I have big application where 4 nodes are using quartz to schedule jobs.
Very frequently I am getting mails from our DB team that
'SELECT * FROM QRTZ_LOCKS WHERE LOCK_NAME='TRIGGER_ACCESS' FOR UPDATE'
is blocking for 15-20 minutes. Sometimes hours. And I see that my jobs are also stuck waiting for lock.
We are using Quartz 1.8.3 which is pretty old version. Here is Quartz setting I am using
org.quartz.scheduler.instanceName = DefaultQuartzScheduler
org.quartz.scheduler.instanceId = AUTO
org.quartz.scheduler.rmi.export = false
org.quartz.scheduler.rmi.proxy = false
org.quartz.scheduler.wrapJobExecutionInUserTransaction = false
org.quartz.threadPool.class = org.quartz.simpl.SimpleThreadPool
org.quartz.threadPool.threadCount = 25
org.quartz.threadPool.threadPriority = 5
org.quartz.threadPool.threadsInheritContextClassLoaderOfInitializingThread = true
org.quartz.jobStore.misfireThreshold = 60000
org.quartz.jobStore.class=org.quartz.impl.jdbcjobstore.JobStoreCMT
org.quartz.jobStore.driverDelegateClass=com.xyx.abc.common.scheduler.impl.CDAJDBCDelegate
org.quartz.jobStore.useProperties=false
org.quartz.jobStore.tablePrefix=QRTZ_
org.quartz.jobStore.isClustered=true
org.quartz.jobStore.clusterCheckinInterval = 20000
# these 3 are required by customSchedulerFactory class
org.quartz.dataSource.myDS.connectionProvider.class=com.xyz.abc.common.scheduler.impl.CustomPoolingConnectionProvider
org.quartz.jobStore.dataSource=myDS
org.quartz.jobStore.nonManagedTXDataSource=myDS
I tried enabling debug logs for Quartz. But didn't get much from it.
Has anyone faced similar issue? How to make sure that 'Select for update' query is executing fast?