Spring shedlock table update row commit getting failed when the backend DB (Mysql InnoDB cluster) is running in multi-primary mode

[VenkateshJhipster]

A spring boot microservice deployed in OCP and makes connection to the backend database using mysql-router image. This service contains a scheduler class (using @scheduled annotation) to do certain workflows based on a cron expression. This microservice is running with multiple nodes.

In order to stop the scheduler class getting executed from all the nodes, SHEDLOCK implementation was done. This setup was working perfectly when the backend database (Mysql Inno DB cluster) is running in Single-primary mode.

Recently, we have changed the database mode from single-primary to multi-primary mode, Similarly mysql-router configurations were also changed from first available mode to round robin mode. Post this implementation, when the SHEDLOCK library is trying to update the lock timings in SHEDLOCK table, the jdbc execution gets failed with below error. Kindly help for solving this issue.

Exception details:

ERROR Unexpected exception
org.springframework.transaction.TransactionSystemException: JDBC commit failed
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.translateException(DataSourceTransactionManager.java:435) ~[spring-jdbc-6.0.9.jar!/:6.0.9]
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:336) ~[spring-jdbc-6.0.9.jar!/:6.0.9]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743) ~[spring-tx-6.0.9.jar!/:6.0.9]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711) ~[spring-tx-6.0.9.jar!/:6.0.9]
    at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:152) ~[spring-tx-6.0.9.jar!/:6.0.9]
    at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.execute(JdbcTemplateStorageAccessor.java:119) ~[shedlock-provider-jdbc-template-5.3.0.jar!/:?]
    at net.javacrumbs.shedlock.provider.jdbctemplate.JdbcTemplateStorageAccessor.updateRecord(JdbcTemplateStorageAccessor.java:82) ~[shedlock-provider-jdbc-template-5.3.0.jar!/:?]
    at net.javacrumbs.shedlock.support.StorageBasedLockProvider.doLock(StorageBasedLockProvider.java:90) ~[shedlock-core-5.3.0.jar!/:?]
    at net.javacrumbs.shedlock.support.StorageBasedLockProvider.lock(StorageBasedLockProvider.java:62) ~[shedlock-core-5.3.0.jar!/:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343) ~[spring-aop-6.0.9.jar!/:6.0.9]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:216) ~[spring-aop-6.0.9.jar!/:6.0.9]
    at jdk.proxy2.$Proxy99.lock(Unknown Source) ~[?:?]
    at net.javacrumbs.shedlock.core.DefaultLockingTaskExecutor.executeWithLock(DefaultLockingTaskExecutor.java:60) ~[shedlock-core-5.3.0.jar!/:?]
    at net.javacrumbs.shedlock.spring.aop.MethodProxyScheduledLockAdvisor$LockingInterceptor.invoke(MethodProxyScheduledLockAdvisor.java:83) ~[shedlock-spring-5.3.0.jar!/:?]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184) ~[spring-aop-6.0.9.jar!/:6.0.9]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750) ~[spring-aop-6.0.9.jar!/:6.0.9]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702) ~[spring-aop-6.0.9.jar!/:6.0.9]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
    at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
    at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
    at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84) ~[spring-context-6.0.9.jar!/:6.0.9]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-6.0.9.jar!/:6.0.9]
    at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:96) ~[spring-context-6.0.9.jar!/:6.0.9]
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) ~[?:?]
    at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
    at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[?:?]
    at java.lang.Thread.run(Thread.java:840) ~[?:?]
Caused by: java.sql.SQLException: Got error 149 - 'Lock deadlock; Retry transaction' during COMMIT
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.28.jar!/:8.0.28]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.28.jar!/:8.0.28]
    at com.mysql.cj.jdbc.ConnectionImpl.commit(ConnectionImpl.java:807) ~[mysql-connector-java-8.0.28.jar!/:8.0.28]
    at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:377) ~[HikariCP-5.0.1.jar!/:?]
    at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java) ~[HikariCP-5.0.1.jar!/:?]
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:333) ~[spring-jdbc-6.0.9.jar!/:6.0.9]
    ... 33 more

[lukas-krecan]

Hi, thanks for the report. ShedLock is actually catching the exception and is just logging it. Does it log it every time or just sometimes?

In general, this will be hard to solve. I do not have any experience with MySQL in multi-primary mode, I do not know what its transaction properties are. Can you please share your lock provider config? Do you use .usingDbTime() setting?

[VenkateshJhipster]

Hi @lukas-krecan Thanks for responding to the issue. Please find my comments below:

Does it log it every time or just sometimes?
Lets assume that if the scheduler runs for every 2 mins, then it was unable to acquire a lock for few occurrences of its run intermittently.
Eg:
10:00 --> Locked
10:02 --> Locked
10:04 --> Unable to lock due to exception. Scheduler execution itself failed. (Can refer to the same exception)
10:06 --> Locked

Can you please share your lock provider config?
@bean
public LockProvider lockProvider(DataSource dataSource) {
return new JdbcTemplateLockProvider(dataSource);
}

Do you use .usingDbTime() setting?
We don't use this.

[lukas-krecan]

Can you please try usingDbTime, it may help

[VenkateshJhipster]

Hi @lukas-krecan ,
I have set this property in lock provider config. Upon deploying the changes, the outcome is still the same. Issue persists.

[lukas-krecan]

Hi, can you please try JdbcStorageAccessor? It uses transactions in a different way so it may work better.

[lukas-krecan]

Would this be helpful to you?

It allows you to configure the lock provider in a way that throws the unexpected exception. You can then wrap the lock provider and retry the execution in case of MySql conflict.