MySQL - Lock wait timeout exceeded - try restarting transaction

Still need help?

The Atlassian Community is here for you.

Ask the community

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.

DescriptionMySQL - Lock wait timeout exceeded - try restarting transaction error appears in Bamboo logs.
Last modified on May 26, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.