Failure obtaining db row lock: Lock wait timeout exceeded; try restarting transaction

[ariawanzero]

I'm using quartz on my java project with version 2.2.3 and getting This Error

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: Lock wait timeout exceeded; try restarting transaction

  | at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:157)
  | at org.quartz.impl.jdbcjobstore.DBSemaphore.obtainLock(DBSemaphore.java:113)
  | at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3796)
  | at org.quartz.impl.jdbcjobstore.JobStoreSupport.triggersFired(JobStoreSupport.java:2916)
  | at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:336)
  | Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
  | at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  | at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
  | at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  | at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
  | at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
  | at com.mysql.jdbc.Util.getInstance(Util.java:408)
  | at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:951)
  | at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
  | at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
  | at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
  | at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
  | at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2487)
  | at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
  | at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1966)
  | at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
  | at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
  | at org.quartz.impl.jdbcjobstore.StdRowLockSemaphore.executeSQL(StdRowLockSemaphore.java:96)
  | ... 4 common frames omitted

Anyone have solution for this?

[zemian]

Can you share your quartz.properties and is there any special DB configuration you used?

[ariawanzero]

Sorry for late response @zemian because the problem appear again

private Properties getQuartzProperties() {
Properties props = new Properties();
props.put("org.quartz.scheduler.instanceName", "ClusteredScheduler");
props.put("org.quartz.scheduler.instanceId", "AUTO");
// props.put("org.quartz.jobStore.misfireThreshold", "1000");
props.put("org.quartz.jobStore.class", "com.btpn.common.util.quartz.CustomJobStore");
props.put("org.quartz.jobStore.driverDelegateClass", "org.quartz.impl.jdbcjobstore.StdJDBCDelegate");
props.put("org.quartz.jobStore.tablePrefix", "QRTZ_");
props.put("org.quartz.jobStore.isClustered", "true");
props.put("org.quartz.threadPool.class", "org.quartz.simpl.SimpleThreadPool");
props.put("org.quartz.threadPool.threadCount", "25");
props.put("org.quartz.threadPool.threadPriority", "5");
props.put("org.quartz.scheduler.skipUpdateCheck", "true");
return props;
}

I'm using mysql 5.7 with standard configuration and it problem appear again do you have any solution

[zemian]

Hi @ariawanzero

This means your DB is not able to acquire the proper lock using the QRTZ_LOCKS table.
Can you try to enable DEBUG level on in your scheduler and check the output by org.quartz.impl.jdbcjobstore.StdRowLockSemaphore class logger? This would tell you which lock
name and how many times it retried and failed.

Also tell us the workload of your quartz scheduler (how many jobs do you have running, how long does each job run on avg etc.) when this problem occur.

[zemian]

I also noticed you have a custom jobStore class com.btpn.common.util.quartz.CustomJobStore. We won't able to debug what's in there. Please use a one quartz provided (eg: org.quartz.impl.jdbcjobstore.JobStoreTX) and reproduce the problem.

[zemian]

It seems like this failure error will occur only when the load is too heavy and the DB is not able to acquire the DB lock within the time allowed to handle the work. I have added a PR #363 as improvement that allow you to configure the maxRetry and retryTimeout (in millis) parameters to avoid this error message. To use it, you need to get latest code and add the following to quartz configuration:

org.quartz.jobStore.lockHandler.class=org.quartz.impl.jdbcjobstore.StdRowLockSemaphore
org.quartz.jobStore.lockHandler.maxRetry=7
org.quartz.jobStore.lockHandler.retryPeriod=3000