MySQL - Lock wait timeout exceeded - try restarting transaction

Symptoms:-

MySQL database is being used, and the following appears in Bamboo logs:

Caused by: java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2007)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1443)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeBatch(NewProxyPreparedStatement.java:1723)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:128)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2436)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2391)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2259)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at

org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:510)
... 88 more

Cause :-

The problem usually happens when a query is taking too long (maybe because the server is too busy).

Resolution :-

Make sure the database tables are using InnoDB storage engine and READ-COMMITTED transaction isolation level.

If the the above configuration is correct then please try to increase the database server innodb_lock_wait_timeout variable to 500

Restart the Bamboo instance and the MySQL database service for the configuration to take place.

If the steps above don't help, please run these queries below to double-check the configuration:

show variables like '%wait_timeout%';

show variables like '%tx_isolation%';

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

The connection pool could be increased as well. Please open the file %BAMBOO_HOME%/bamboo.cfg.xml and find the following tag: