Quartz scheduler with percona db cluster giving deadlock errors

Question

Hi We are trying to configure quartz scheduler in a clustered mode with percona multinode (galera master-master) cluster. Tocmat gets started, and works, but seeing below exception frequently in log.

Any pointers ?

Tomcat 7.0.54 Quartz - 2.1.7 Percona XtraDB cluster 5.5

[ERROR] 11 6 02:26:00.059 午前 MyClusteredScheduler_QuartzSchedulerThread [org.quartz.core.ErrorLogger]
An error occurred while releasing trigger 'DEFAULT.SqlBackgroundServiceTrigger'

org.quartz.JobPersistenceException: Couldn't commit jdbc connection. Deadlock found when trying to get lock; try restarting transaction [See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction]
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.commitConnection(JobStoreSupport.java:3661)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3799)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3760)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.releaseAcquiredTrigger(JobStoreSupport.java:2845)
        at org.quartz.core.QuartzSchedulerThread.releaseIfScheduleChangedSignificantly(QuartzSchedulerThread.java:465)
        at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:306)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:389)
        at com.mysql.jdbc.Util.getInstance(Util.java:372)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3835)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3771)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2531)
        at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1612)
        at com.mysql.jdbc.MultiHostMySQLConnection.commit(MultiHostMySQLConnection.java:111)
        at sun.reflect.GeneratedMethodAccessor44.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at com.mysql.jdbc.LoadBalancingConnectionProxy.invokeMore(LoadBalancingConnectionProxy.java:458)
        at com.mysql.jdbc.MultiHostConnectionProxy.invoke(MultiHostConnectionProxy.java:394)
        at com.sun.proxy.$Proxy4.commit(Unknown Source)
        at org.apache.tomcat.dbcp.dbcp.DelegatingConnection.commit(DelegatingConnection.java:334)
        at org.apache.tomcat.dbcp.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.commit(PoolingDataSource.java:211)
        at sun.reflect.GeneratedMethodAccessor44.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)
        at org.quartz.impl.jdbcjobstore.AttributeRestoringConnectionInvocationHandler.invoke(AttributeRestoringConnectionInvocationHandler.java:73)
        at com.sun.proxy.$Proxy30.commit(Unknown Source)
        at org.quartz.impl.jdbcjobstore.JobStoreSupport.commitConnection(JobStoreSupport.java:3659)
        ... 5 more

Here is our current quartz.properties file configuration,

# Configure Main Scheduler Properties
org.quartz.scheduler.instanceName=MyClusteredScheduler
org.quartz.scheduler.instanceId=AUTO

# Configure ThreadPool
org.quartz.threadPool.class=org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount=60
org.quartz.threadPool.threadPriority=5

# Configure JobStore
org.quartz.jobStore.misfireThreshold = 60000

org.quartz.jobStore.class = org.quartz.impl.jdbcjobstore.JobStoreTX
org.quartz.jobStore.driverDelegateClass = org.quartz.impl.jdbcjobstore.StdJDBCDelegate
org.quartz.jobStore.useProperties = false
org.quartz.jobStore.dataSource = myDS
org.quartz.jobStore.tablePrefix = QRTZ_
org.quartz.plugin.jobInitializer.class =org.quartz.plugins.xml.XMLSchedulingDataProcessorPlugin
org.quartz.plugin.jobInitializer.fileNames = quartz-config.xml
org.quartz.plugin.jobInitializer.failOnFileNotFound = true
org.quartz.jobStore.isClustered = true
org.quartz.jobStore.clusterCheckinInterval = 20000

# Configure Datasources
org.quartz.dataSource.myDS.driver = com.mysql.jdbc.Driver
org.quartz.dataSource.myDS.jndiURL=java:/comp/env/jdbc/quartzJOB

Answer 1

I don't know anything about Quartz but when PXC gives you a deadlock error, its usually because you started a transaction on node1 and node2, attempting to modify near or the same row, node2 committed first then node1. node1 would have returned a deadlock error indicating a local certification failure.

Go download this toolkit and have it up and running on each node before starting quartz. If you see numbers in the 'bfa' or 'lcf' columns, then you know this is happening.

https://github.com/jayjanssen/myq_gadgets

./myq_status wsrep

It is not possible to write to the same row on multiple PXC nodes and generally advised AGAINST simultaneously writing to the same table on multiple nodes.

Answer 2

The standard lock handler of quartz is using

SELECT ... FOR UPDATE

but this works not as expected in a galera cluster.

Use the following quartz configuration to use the update row semaphore:

org.quartz.jobStore.lockHandler.class = org.quartz.impl.jdbcjobstore.UpdateLockRowSemaphore