I am having a database logic which implies:

• doing a select in a table to get the most recent row ( that has a date column B_DATE inside of it )
• doing a logic depending if the current day != B_DATE.day resulting in a dayId
• insert a new row with the computed dayId

Because I am having 2 schedulers running in parallel I am trying to avoid the issue of inserting two rows with the same dayId.

Using a distributed lock library like shedlock is not an option as I really need to scale my app.

I went ahead and used the lock table feature in postgres inside a transaction, at the start of my code:

LOCK TABLE MY_TABLE IN EXCLUSIVE MODE


@Transactional
public void executeLogic() {
    myTableRepository.lockTable();
    //the rest of the logic
}

What I have noticed is that, the blocking is working, but the second scheduler doesn’t see the row inserted by the first scheduler.

Am I missing something?