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 above configuration is correct then 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: 

  <property name="hibernate.c3p0.max_size">...</property>

  Please increase the value and restart your Bamboo instance.