Database Deadlock on Microsoft SQL Server in Confluence
Platform Notice: Data Center - This article applies to Atlassian products on the Data Center platform.
Note that this knowledge base article was created for the Data Center version of the product. Data Center knowledge base articles 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
In Confluence 2.9 or before, when creating a page, you may encounter deadlocks and get the following error:
2008-11-11 17:01:21,355 ERROR [http-8080-Processor6] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
-- url: /confluence/pages/docreatepage.action | userName: tstcreator3 | action: docreatepage
2008-11-11 17:01:21,355 ERROR [http-8080-Processor6] [sf.hibernate.util.JDBCExceptionReporter] logExceptions Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
-- url: /confluence/pages/docreatepage.action | userName: tstcreator3 | action: docreatepage
2008-11-11 17:01:21,357 ERROR [http-8080-Processor6] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session
-- url: /confluence/pages/docreatepage.action | userName: tstcreator3 | action: docreatepage
2008-11-11 17:01:21,357 ERROR [http-8080-Processor6] [sf.hibernate.impl.SessionImpl] execute Could not synchronize database state with session
-- url: /confluence/pages/docreatepage.action | userName: tstcreator3 | action: docreatepage
2008-11-11 17:01:21,359 ERROR [http-8080-Processor6] [atlassian.xwork.interceptors.XWorkTransactionInterceptor] onThrowable Invoking rollback for transaction on action '/pages/docreatepage.action (CreatePageAction.doAdd())' due to throwable: org.springframework.dao.ConcurrencyFailureException: Hibernate operation: could not delete: [com.atlassian.confluence.pages.Draft#14319839]; SQL []; Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.; nested exception is java.sql.SQLException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
-- url: /confluence/pages/docreatepage.action | userName: tstcreator3
org.springframework.dao.ConcurrencyFailureException: Hibernate operation: could not delete: [com.atlassian.confluence.pages.Draft#14319839]; SQL []; Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.; nested exception is java.sql.SQLException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Caused by: java.sql.SQLException: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:367)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2816)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2254)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:631)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:584)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:546)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:505)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
at net.sf.hibernate.persister.EntityPersister.delete(EntityPersister.java:581)
at net.sf.hibernate.impl.ScheduledDeletion.execute(ScheduledDeletion.java:29)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2449)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2435)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2397)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2261)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
This problem will be more prominent under high load environments when there are more concurrent page creates/edits.
Cause
SQL Server is escalating row locks (in this case deleting a draft from the CONTENT table) to table locks and stopping other transactions from accessing the table.
Diagnosis
Check whether you are using the recommended transaction isolation level of "Read Committed with Row Versioning" by running this SELECT query. If this query returns a '1', you are already using it. If it returns '0', proceed to the Workaround section:
SELECT sd.is_read_committed_snapshot_on
FROM sys.databases AS sd
WHERE sd.[name] = '<database_name>';Workaround
Configure your database to use the isolation level, Read Committed with Row Versioning. You can do this by executing the following query:
ALTER DATABASE <database_name>
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;
You should now run the SELECT query in the Diagnosis section again. It should now return '1'.
More information on SQL Server isolation levels can be found in the Microsoft documentation.
From version 2.10.x onwards, indexes will be automatically created upon performing an upgrade.
For customers using a previous version, please execute the following DDL against your confluence database:
CREATE NONCLUSTERED INDEX [c_pageid_idx] ON [dbo].[CONTENT]
(
[PAGEID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Resolution
Upgrade to Confluence 2.10 or later. If the problem continues, please contact support.
This has been fixed in Confluence 5.6.3 and later. For further details see CONFSERVER-26296 - When using MS SQL Server, Check for 'Read-Committed with Row Versioning' transaction isolation during startup/upgrades