MySQL - Lock wait timeout exceeded - try restarting transaction
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Symptoms
MySQL database is being used, and the following appears in Bamboo logs:
1
2
3
4
5
6
7
8
9
10
11
12
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 moreCause
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:
1 2 3show 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.xmland find the following tag:1<property name="hibernate.c3p0.max_size">...</property>Please increase the value and restart your Bamboo instance.
Was this helpful?