Misfired tasks in the Job conductor when TAC is in high availability

Symptoms

Jobs misfire randomly with the following error on the logserver:

org.quartz.JobPersistenceException: Couldn't update states of blocked triggers: Lock wait timeout exceeded; try restarting transaction [See nested exception: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction]
at org.quartz.impl.jdbcjobstore.JobStoreSupport.triggerFired(JobStoreSupport.java:2921)
at org.quartz.impl.jdbcjobstore.JobStoreSupport$38.execute(JobStoreSupport.java:2842) 
at org.quartz.impl.jdbcjobstore.JobStoreSupport.executeInNonManagedTXLock(JobStoreSupport.java:3742)
at org.quartz.impl.jdbcjobstore.JobStoreSupport.triggerFired(JobStoreSupport.java:2836)
at org.quartz.core.QuartzSchedulerThread.run(QuartzSchedulerThread.java:339)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction

 

Diagnosis

A connection can't be established from the MySQL DB side.

 

Solution

The solution is to make the following changes on the MySQL DB side (used by TAC):

  1. Change the MySQL DB isolation level by using READ COMMITTED instead of REPEATABLE READ (InnoDB Defaults) in the following commands:

    mysql> SET tx_isolation = 'READ-COMMITTED';
    mysql> SET GLOBAL tx_isolation = 'READ-COMMITTED';
  2. Increase the MySQL max_connections value using the following command:

    mysql>set global max_connections=1500
  3. In the TAC configuration.properties file, set up the following value as:

    # maxStatements:
    hibernate.c3p0.max_statements=0
  4. Restart TAC after the changes.

Version history
Revision #:
4 of 4
Last update:
‎12-08-2017 02:30 PM
Updated by: