Unable to delete projects in Jira with SQLException: 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
Summary
The Jira admin is unable to delete projects which is failing with the following error in the <jira-home>/log/atlassian-jira.log:
1
2
3
4
5
6
/secure/project/DeleteProject.jspa [c.a.jira.project.DefaultProjectManager] Exception removing issues
com.querydsl.core.QueryException: Caught SQLException for delete from customfieldvalue
where customfieldvalue.issue = ?
at com.querydsl.sql.DefaultSQLExceptionTranslator.translate(DefaultSQLExceptionTranslator.java:50)
...
Caused by: java.sql.SQLException: Lock wait timeout exceeded; try restarting transactionDiagnosis
This type of error typically occurs when a transaction is waiting on row(s) of data to update which is already locked by another transaction.
Make sure the database tables are using InnoDB storage engine and READ-COMMITTED transaction isolation level.
Increase the database server innodb_lock_wait_timeout variable to 500.
Restart the MySQL database service and the Jira instance for the configuration to take place.
Follow the resolution in the KB NullPointerException when Deleting a Custom Field.
Run these queries below to double check the configuration:
1
2
3
show variables like '%wait_timeout%';
show variables like '%tx_isolation%';
SELECT @@GLOBAL.tx_isolation, @@tx_isolation;Cause
If everything above is configured properly, the issue could be caused by incompatible 3rd-party listeners and custom fields. See JRASERVER-70522 - Updating custom field value during issue deletion can cause stuck threads for more information.
Solution
Enter Safe Mode and attempt to delete the projects.
If these steps don't resolve the issue, there may be another root cause – please reach out to Atlassian Support with a Support Zip from your instance.
Was this helpful?