Jira MySQL Usage Fails With Error - java.sql.BatchUpdateException Lock wait timeout exceeded
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
There are multiple forms in which this error can surface:
Jira becomes unresponsive or crashes.
Jira Crowd integration fails
Jira fails to start up
The exceptions in the logs are also varied.
Example 1
The following appears in the atlassian-jira.log file:
1
Caused by: org.codehaus.xfire.fault.XFireFault: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [update cwd_token set directory_id=?, entity_name=?, random_number=?, identifier_hash=?, random_hash=?, created_date=?, last_accessed_date=? where id=?]; SQL state [41000]; error code [1205]; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
ℹ️ SQL state 41000 error code 1205 is returned from Crowd.
OR
1
2
3
4
5
6
7
java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2815)Cause
The MySQL database configuration related to transaction isolation is incorrect.
Resolution
Modify MySQL startup options in the configuration file my.cnf (often named my.ini on Windows), so the transaction level is set to transaction-isolation = READ-COMMITTED. (Refer to MySQL Option Files for detailed instructions on editing my.cnf and my.ini.)
1
2
[mysqld]
transaction-isolation = READ-COMMITTEDIf the same behavior happens even though the transaction-isolation level is set correctly, contact your DBA to check for more details on MySQL. There are other situations (e.g. database is too busy, storage is full) that can also trigger the same behavior.
Notes:
On Windows, the my.cnf file is often named my.ini. Windows can handle both file names.
The above configuration will prevent errors when you import directory information into Crowd. See CWD-1505.
Was this helpful?