Shedlock fails to commit transaction with Spring Boot 2.2.6

[klimesf]

Hello,

I have upgraded Spring Boot from 2.2.5 to 2.2.6 and my scheduled tasks stopped working. I believe the transaction manager behavior has changed, causing JdbcTemplateStorageAccessor to fail when trying to execute insertRecord().

1. Which version do you use
4.8.0

2. Which Lock Provider
JDBC (on PostgreSQL)

3. ShedLock configuration

@Configuration
@EnableScheduling
@EnableSchedulerLock(
    mode = EnableSchedulerLock.InterceptMode.PROXY_METHOD,
    defaultLockAtLeastFor = "PT10M",
    defaultLockAtMostFor = "PT10M"
)
public class SchedulingConfiguration
{

    @Bean
    public LockProvider shedlockLockProvider(final DataSource dataSource)
    {
        return new JdbcTemplateLockProvider(dataSource);
    }

}

4. ShedLock logs

20:07:00.084 [scheduling-1   ] ERROR   o.s.s.support.TaskUtils$LoggingErrorHandler:	Unexpected error occurred in scheduled task 
org.springframework.transaction.TransactionSystemException: Could not commit JDBC transaction; nested exception is org.postgresql.util.PSQLException: The database returned ROLLBACK, so the transaction cannot be committed. Transaction failure cause is <<ERROR: duplicate key value violates unique constraint "shedlock_pkey"
  Detail: Key (name)=(uniqueNotificationName) already exists.>>
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:335)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:152)
	at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.insertRecord(JdbcTemplateStorageAccessor.java:63)
	at net.javacrumbs.shedlock.support.StorageBasedLockProvider.doLock(StorageBasedLockProvider.java:77)
	at net.javacrumbs.shedlock.support.StorageBasedLockProvider.lock(StorageBasedLockProvider.java:61)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205)
	at com.sun.proxy.$Proxy113.lock(Unknown Source)
	at net.javacrumbs.shedlock.core.DefaultLockingTaskExecutor.executeWithLock(DefaultLockingTaskExecutor.java:50)
	at net.javacrumbs.shedlock.spring.aop.MethodProxyScheduledLockAdvisor$LockingInterceptor.invoke(MethodProxyScheduledLockAdvisor.java:67)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
	at com.example.ExampleScheduler$$EnhancerBySpringCGLIB$$126463b3.sendProductGroupNotifications(<generated>)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
	at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
	at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93)
	at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: The database returned ROLLBACK, so the transaction cannot be committed. Transaction failure cause is <<ERROR: duplicate key value violates unique constraint "shedlock_pkey"
  Detail: Key (name)=(uniqueNotificationName) already exists.>>
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2209)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:331)
	at org.postgresql.jdbc.PgConnection.executeTransactionCommand(PgConnection.java:833)
	at org.postgresql.jdbc.PgConnection.commit(PgConnection.java:855)
	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:366)
	at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java)
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:332)
	... 32 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "shedlock_pkey"
  Detail: Key (name)=(uniqueNotificationName) already exists.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2578)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2313)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:331)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:159)
	at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:125)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917)
	at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.lambda$insertRecord$1(JdbcTemplateStorageAccessor.java:65)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	... 29 common frames omitted

Expected behavior
Transaction commit should be okay, even though primary key constraint was violated, and the shedlock cache should be populated – causing shedlock to not try to insert the key again.

Actual behavior
Transaction commit failed, causing the shedlock cache to not populate, and failing to insert the row continually.

[lukas-krecan]

Seems to be duplicate of #207

[lukas-krecan]

Are you sure, you are using 4.8.0?

[klimesf]

I am so terribly sorry, there was a problem with cache in my project. I indeed wasn't using 4.8.0.
After upgrading to 4.8.0, the issue is fixed.

Thank you for your super quick response!

[lukas-krecan]

No problem