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