Database deadlock on Microsoft SQL Server
Problem
Confluence freezes and stops responding, logs contain a very specific message:
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)
A variant of this error message:
2010-08-09 11:48:35,837 ERROR [http-8081-1] [[Standalone].[localhost].[/].[file-server]] log Servlet.service() for servlet file-server threw exception
org.springframework.dao.ConcurrencyFailureException: Hibernate operation: could not update: [bucket.user.propertyset.BucketPropertySetItem#bucket.user.propertyset.BucketPropertySetItem@2377212]; SQL [];
Transaction (Process ID 51) 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 51) 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 51) 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:368)
...
It's also possible for this to happen without putting anything into the logs at all.
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.
If the symptom reflects the variant above, Tracking Plugin might have been installed in Confluence.
Diagnosis
You can enable a trace flag on SQL Server that will log deadlocked queries with enough verbosity to confirm if there is an issue with the type of lock that is being used. This MSDN blog outlines the process and gives examples of the output this additional level of trace logging provides. It also provides an explanation for interpreting the output, which may help your troubleshooting process. If you raise a support issue, please include the SQL Server logs in addition to your support zip/Confluence logs.
Resolution
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;
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>';
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]
If the Tracking Plugin is installed, try disabling the plugin. See CONF-14096 and this discussion for further information.