Deadlock errors in logs when running Jira server with SQL Server database
Symptoms
The following appears in the atlassian-jira.log:
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 Server
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:
SET IMPLICIT_TRANSACTIONS ON;
Explanation about the above command here
Permanent Resolution:
First, please ensure the database is set to READ_COMMITTED_SNAPSHOT ON as per Connecting JIRA to SQL Server.
To verify the changes, use this query which should result in '1':
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:
<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.