org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update

[romanmorenko]

We run two instance of service. In trace of one: sql.util.PSQLException ERROR: could not serialize access due to concurrent update

1. shedlock version 4.14.0
2. Jdbc-template shedlock provider
3. Config:

   @Bean
   fun lockProvider(dataSource: DataSource): LockProvider {
       return JdbcTemplateLockProvider(
               JdbcTemplateLockProvider.Configuration.builder()
                       .withJdbcTemplate(JdbcTemplate(dataSource))
                       .usingDbTime()
                       .build()
       )
   }

4. Trace:

   
   org.springframework.dao.CannotSerializeTransactionException:
   PreparedStatementCallback; SQL [INSERT INTO shedlock(name, lock_until, locked_at, locked_by) VALUES(?, timezone('utc', CURRENT_TIMESTAMP) + cast(? as interval), timezone('utc', CURRENT_TIMESTAMP), ?) ON CONFLICT (name) DO UPDA

TE SET lock_until = timezone('utc', CURRENT_TIMESTAMP) + cast(? as interval), locked_at = timezone('utc', CURRENT_TIMESTAMP), locked_by = ? WHERE shedlock.name = ? AND shedlock.lock_until <= timezone('utc', CURRENT_TIMESTAMP)]; ERROR: could not serialize access due to concurrent update; nested exception is org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:883) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:321) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:326) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.lambda$insertRecord$0(JdbcTemplateStorageAccessor.java:65) ~[shedlock-provider-jdbc-template-4.14.0.jar!/:na] at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140) ~[spring-tx-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.insertRecord(JdbcTemplateStorageAccessor.java:63) ~[shedlock-provider-jdbc-template-4.14.0.jar!/:na] at net.javacrumbs.shedlock.support.StorageBasedLockProvider.doLock(StorageBasedLockProvider.java:81) ~[shedlock-core-4.14.0.jar!/:na] at net.javacrumbs.shedlock.support.StorageBasedLockProvider.lock(StorageBasedLockProvider.java:65) ~[shedlock-core-4.14.0.jar!/:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344) ~[spring-aop-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:205) ~[spring-aop-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at com.sun.proxy.$Proxy95.lock(Unknown Source) ~[na:na] at net.javacrumbs.shedlock.core.DefaultLockingTaskExecutor.executeWithLock(DefaultLockingTaskExecutor.java:63) ~[shedlock-core-4.14.0.jar!/:na] at net.javacrumbs.shedlock.spring.aop.MethodProxyScheduledLockAdvisor$LockingInterceptor.invoke(MethodProxyScheduledLockAdvisor.java:85) ~[shedlock-spring-4.14.0.jar!/:na] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) ~[spring-aop-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691) ~[spring-aop-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at ru.vtb.msa.car.deal.orchestrator.service.SchedulerService$$EnhancerBySpringCGLIB$$a395913b.restart() ~[classes!/:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na] at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na] at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na] at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na] at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84) ~[spring-context-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93) ~[spring-context-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at ru.vtb.msa.mdclogging.concurrent.MdcScheduledExecutorService$decorate$2.run(MdcScheduledExecutorService.kt:60) ~[mdc-logging-core-0.1.13.jar!/:0.1.13] at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) ~[na:na] at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[na:na] at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) ~[na:na] at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na] at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na] at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na] Caused by: org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2532) ~[postgresql-42.2.14.jar!/:42.2.14] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2267) ~[postgresql-42.2.14.jar!/:42.2.14] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:312) ~[postgresql-42.2.14.jar!/:42.2.14] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:448) ~[postgresql-42.2.14.jar!/:42.2.14] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:369) ~[postgresql-42.2.14.jar!/:42.2.14] at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:153) ~[postgresql-42.2.14.jar!/:42.2.14] at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:119) ~[postgresql-42.2.14.jar!/:42.2.14] at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61) ~[HikariCP-3.4.5.jar!/:na] at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java) ~[HikariCP-3.4.5.jar!/:na] at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617) ~[spring-jdbc-5.2.7.RELEASE.jar!/:5.2.7.RELEASE]

**Expected behavior**
No errors in stacktrace

**Actual behavior**
Error in stacktrace:
org.postgresql.util.PSQLException: ERROR: could not serialize access due to concurrent update

[lukas-krecan]

Thanks for reporting, will take a look at it.

[romanmorenko]

Thank you, when you plan to release 4.18.1?

[lukas-krecan]

Over the weekend

[lukas-krecan]

Released as 4.19.0