How to resolve Database Deadlock on Jira?

[Luca Lo Casto]

Good afternoon,

since June we have encountered an error that slows down our work.

Often, when we update a ticket, we get the error:
"Deadlock errors in logs when running Jira server with SQL Server database"

Often it is enough to repeat the action to make it successful, other times it is necessary to reload the ticket page and then repeat the action.

It happens practically every day on many agents, we can't understand what it comes from.

The tests we have done are the following:

1. CLONE THE DB (since the first one was used by another application too, Decysion - xxxxxxxxx, with no success.

2. Tried the solution here with no success (https://confluence.atlassian.com/jirakb/deadlock-errors-in-logs-when-running-jira-server-with-sql-server-database-283640022.html? utm_medium = scheduledLogScan & utm_source = STP), Screenshot attached

3. Upgrade Jira to the latest version

4. Upgrade all plugin



Complete JVM Input Arguments: 

JVM Input Arguments

-Djava.awt.headless=true -Datlassian.standalone=JIRA -Dorg.apache.jasper.runtime.BodyContentImpl.LIMIT_BUFFER=true -Dmail.mime.decodeparameters=true -Dorg.dom4j.factory=com.atlassian.core.xml.InterningDocumentFactory -XX:-OmitStackTraceInFastThrow -XX:+ExplicitGCInvokesConcurrent -XX:+PrintGCDetails -XX:+PrintGCDateStamps -XX:+PrintGCTimeStamps -XX:+PrintGCCause -XX:+UseGCLogFileRotation -XX:NumberOfGCLogFiles=5 -XX:GCLogFileSize=20M -Xloggc:C:\Program Files\Atlassian\JIRA\logs\atlassian-jira-gc-%t.log -XX:InitialCodeCacheSize=32m -XX:ReservedCodeCacheSize=512m -Dcatalina.home=C:\Program Files\Atlassian\JIRA -Dcatalina.base=C:\Program Files\Atlassian\JIRA -Dignore.endorsed.dirs=C:\Program Files\Atlassian\JIRA\endorsed -Djava.io.tmpdir=C:\Program Files\Atlassian\JIRA\temp -Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Djava.util.logging.config.file=C:\Program Files\Atlassian\JIRA\conf\logging.properties -Dfile.encoding=utf-8 -Dsun.jnu.encoding=UTF-8 -Djira.downgrade.allowed=true -XX:+UseG1GC exit abort -Xms12288m -Xmx12288m

 I've found those in the LOGS:

Line 3011: Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:UPDATE jiraschema.jiraissue SET UPDATED=? WHERE ID=? (Transaction (Process ID 88) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.)

Line 3869: Caused by: org.ofbiz.core.entity.GenericDataSourceException: Generic Entity Exception occurred in deleteAll (SQL Exception while executing the following:DELETE FROM jiraschema.customfieldvalue WHERE ID=? (Transaction (Process ID 57) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.))

                Line 5899: Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:UPDATE jiraschema.OS_CURRENTSTEP SET START_DATE=? WHERE ID=? (Transaction (Process ID 90) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.) 

                Line 8356:     Caused by: org.ofbiz.core.entity.GenericDataSourceException: SQL Exception while executing the following:UPDATE jiraschema.jiraissue SET ASSIGNEE=?, UPDATED=? WHERE ID=? (Transaction (Process ID 90) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.)

SELECT [ID] FROM [JIRASD].[jiraschema].[OS_CURRENTSTEP] | ROWS: 86398

SELECT [ID] FROM [JIRASD].[jiraschema].[JIRAISSUE] | ROWS: 79765

SELECT [ID] FROM [JIRASD].[jiraschema].[customfieldvalue] | ROWS: 1.979.273

OS_CURRENTSTEP / JIRA ISSUE have not the same rows, could this be the causes of the problem?

How could i solve?

Thanks,
Regards.

Luca

[Nic Brough -Adaptavist-]

The problem here is that you seem to be allowing more than one application to access the Jira database (it could even be two Jiras if you've mistakenly tried to cluster Jira without data center).  The error you're getting is down to one of them deadlocking records that the other is trying to use.

You need to set up your Jira with its own database that nothing else is trying to read or write (it's fine to have other databases on the same database server, but you should never share the Jira database with other applications)

[Luca Lo Casto]

Hello Nic,

we also thought the problem was that, but we cloned the DB and isoleted them to each application and the problem was not solved.

Is there a way to understand if we've two parallel instances of Jira?

Do you think the problem could be in the DB datas? Cloning the database wasn't resolutive, maybe the cloned DB was corrupted so we had the same problem even in the "new" one?

Thank you very much.

[Nic Brough -Adaptavist-]

You can't have two instances of Jira using the same database off-the-shelf, the first active one will "lock" the database, and the second one will refuse to start because it can see the first one is active.  You can, of course, hack that, but you will still find out about it very quickly as your users start complaining about crashes, deadlocks and corrupt data.

Deadlocks are not caused by corrupted data, they happen when you've got applications interfering with each other's access.  Jira has been written in a way that avoids complexity by simply assuming that no other application will be writing to the database when it is trying to use it.  So you get deadlocks when another application does.

[Luca Lo Casto]

Hello Nic, 
Thanks.

The problem is this, we also cloned the database and dedicated its use just to Jira but the problem was not solved.

I found a similar thread to mine and the discussion was about corrupted data (https://community.atlassian.com/t5/Jira-questions/How-to-resolve-Database-Deadlock-on-Jira/qaq-p/1121583) that's why i thought we had the same problem.

Thank you so much!