JIRA MySQL Usage Fails With Error - java.sql.BatchUpdateException Lock wait timeout exceeded
Symptoms
There are multiple forms in which this error can surface:
- JIRA becomes unresponsive or crashes.
- JIRA Crowd integration fails
The exceptions in the logs are also varied.
Example 1
The following appears in the atlassian-jira.log file:
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
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.)
[mysqld]
transaction-isolation = READ-COMMITTEDNotes:
- 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.
Last modified on Mar 30, 2016
Powered by Confluence and Scroll Viewport.