How to resolve Database Deadlock on Jira?

[Nguyễn Thành Hải]

Hello,

I have some problems about the configuration Isolation level for Jira database. My Jira version is JIRA v7.10.1 and database is MSSQL 2012, I configed mode READ_COMMITTED_SNAPSHOT is ON recording by recommend Instance health checks. But there are many problems of data deadlock like attachment. Please help me to resolve this!!

Many thanks!

https://confluence.atlassian.com/jirakb/health-check-database-isolation-834225506.html?_ga=2.181695520.643115483.1561897842-508274146.1561897842

Instance health checks after configuration DB:

[Nguyễn Thành Hải · Accepted solution]

I created new database with isolation level of READ_COMMITTED_SNAPSHOT is ON and Jira is working well now. I have checked the data integrity and it's okie.

Thanks @Nic Brough -Adaptavist-

[Nic Brough -Adaptavist- · Accepted solution]

What is your connection string in the dbconfig.xml file?

[Nguyễn Thành Hải]

dear Nic:

mssql jdbc:sqlserver://x.x.x.x;databaseName=JIRA_DB;autoCommit=false

[Nic Brough -Adaptavist-]

Ok, what SQL driver are you using?  Should be able to see it in the system information page.

[Nguyễn Thành Hải]

[Nic Brough -Adaptavist-]

Ok, well, that's a fully supported version of Jira + Database + driver + connection string.

I think the next step is to find that error in the main log file and look at the errors that are occurring immediately before and after it.

[Nguyễn Thành Hải]

Yes, i already read the log file. There are a lot errors about deleting and updating OS_CURRENTSTEP table. So the deadlock has occurred, like the screenshot above.

[Nic Brough -Adaptavist-]

So those errors could be leading to the deadlock, not the other way around.  You should examine them to see where they are coming from.

[Nguyễn Thành Hải]

I see the total of rows in OS_CURRENTSTEP table and JIRAISSUE are not equal. I don't know that is problem or not? I think those wrong data was occurred because the application executed with mode of database read uncommit before.

[Nic Brough -Adaptavist-]

Yes, that will cause you problems.  You have a corrupted database now.

I would try restarting Jira and running the integrity check.  That may be able to repair the tables, but if not, then I suspect all you can do is export the issues and import them into a working Jira.

[Nguyễn Thành Hải]

Thanks Nic, I'm also resolving like your recommend. Because, i don't know if another table has wrong data or not, so i can't fix all tables with wrong data.