Deadlock errors in logs when running Jira with SQL Server database
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
The following appears in the atlassian-jira.log:
1
2
3
4
5
6
7
8
9
10
2012-04-16 10:21:47,156 JiraImportTaskExecutionThread-1 ERROR anonymous 603x30243x1 nx2q2h 138.106.178.52 /secure/admin/XmlRestore.jspa [sal.core.lifecycle.DefaultLifecycleManager] Unable to start component: $Proxy1405
com.opensymphony.module.propertyset.PropertyImplementationException: Generic Entity Exception occurred in deleteByAnd (SQL Exception while executing the following:DELETE FROM jiraschema.propertyentry WHERE ENTITY_NAME=? AND ENTITY_ID=? AND PROPERTY_KEY=? (Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.))
at com.opensymphony.module.propertyset.ofbiz.OFBizPropertySet.remove(OFBizPropertySet.java:222)
at com.atlassian.jira.propertyset.JiraCachingPropertySet.remove(JiraCachingPropertySet.java:804)
at com.atlassian.sal.jira.pluginsettings.JiraPluginSettings.putActual(JiraPluginSettings.java:28)
at com.atlassian.sal.core.pluginsettings.AbstractStringPluginSettings.put(AbstractStringPluginSettings.java:88)
at com.atlassian.upm.impl.NamespacedPluginSettings.put(NamespacedPluginSettings.java:34)
at com.atlassian.upm.log.PluginSettingsAuditLogService.saveEntries(PluginSettingsAuditLogService.java:489)
at com.atlassian.upm.log.PluginSettingsAuditLogService.saveEntryAndPurge(PluginSettingsAuditLogService.java:435)
at com.atlassian.upm.log.PluginSettingsAuditLogService.logI18nMessageWithUsername(PluginSettingsAuditLogService.java:112)Cause
Autocommit is ON in SQL Server
Environment
Jira
Resolution
Temporary Resolution:
This suggestion will only work for a session and it is meant for verification of the problem, it will reset autocommit to ON once the session is expired. Set the autocommit to OFF with the following command:
1
SET IMPLICIT_TRANSACTIONS ON;NOTE: Explanation about the above command here
Permanent Resolution:
First, please ensure the database is set toREAD_COMMITTED for the SET TRANSACTION ISOLATION LEVEL as per Connecting JIRA applications to SQL Server 2019
To verify the changes, use this query which should result in '1':
1
2
3
SELECT sd.is_read_committed_snapshot_on
FROM sys.databases AS sd
WHERE sd.[name] = '<database name>';To ensure that the autocommit is to remain OFF, you'll need to modify the connection parameters. Please locate your dbconfig.xml located at your JIRA_HOME directory. Locate the line below and add autoCommit=false as shown:
1
<url>jdbc:jtds:sqlserver://localhost:1433/jiradb;autoCommit=false</url>
ℹ️ In case you are finding difficulties in any of the above steps, please contact Atlassian Support with a detailed description of the problem.
Was this helpful?