Deadlock when running JIRA with SQL Server

Still need help?

The Atlassian Community is here for you.

Ask the community

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

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;

(info) 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>

(info)  In case you are finding difficulties in any of the above steps, please contact Atlassian Support with a detailed description of the problem.

Last modified on Jun 6, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.