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 was 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.
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
