Shedlock tries to insert new row in db instead of updating the existing row for the job

Question

So I have a scheduled job, locked with ShedLock. When executing, I get the following error:

SQLIntegrityConstraintViolationException: Duplicate entry 'myJob' for key 'PRIMARY'.

The function has the following description:

@Transactional
@Scheduled(cron = "0 0 1 * * MON")
@SchedulerLock(name = "myJob", lockAtLeastFor = "PT5M")
public void myJob() {//Code is not relevant.}

Do you have any idea what this could be about? It feels like the job is running twice, but why doesn't it update the existing column in the DB, but tries to insert a new one?

Update: The job runs fine, meaning just one instance gets to execute the code inside the function. But the other ones throw that exception.

The bean declaration of my LockProvider is as follows:

    @Bean
public LockProvider lockProvider(DataSource dataSource) {
    return new JdbcTemplateLockProvider(
            JdbcTemplateLockProvider.Configuration.builder()
                    .withJdbcTemplate(new JdbcTemplate(dataSource))
                    .withTableName("shedlock")
                    .usingDbTime()
                    .build()
    );
}

Answer 1

It's important to mention that the exception is handled by the library, it just appears in the DB log.

If you want to get rid of this behavior, enable usingDbTime().

The reason why ShedLock tries to insert a new row after startup is simple - it needs to atomically ensure the lock. For that, it needs to upsert a record and find-out if the upsert was successful. In standard SQL the only way is to try to insert the row first and if the insert fails due to primary key conflict, try to update the row.

When you specify useDbTime() it uses DB engine specific commands to handle the conflict better (for example INSERT ON CONFLICT... in Postgres)