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 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:

    <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 Sep 19, 2019

Was this helpful?

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