MySQL - Lock wait timeout exceeded - try restarting transaction
Platform Notice: Server and Data Center Only. This article only applies to Atlassian products on the server and data center platforms.
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.