Database Deadlock on Microsoft SQL Server in Confluence
プラットフォームについて: Data Center - この記事は、Data Center プラットフォームのアトラシアン製品に適用されます。
このナレッジベース記事は製品の Data Center バージョン用に作成されています。Data Center 固有ではない機能の Data Center ナレッジベースは、製品のサーバー バージョンでも動作する可能性はありますが、テストは行われていません。サーバー*製品のサポートは 2024 年 2 月 15 日に終了しました。サーバー製品を利用している場合は、アトラシアンのサーバー製品のサポート終了のお知らせページにて移行オプションをご確認ください。
*Fisheye および Crucible は除く
症状
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.
原因
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.
診断
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>';回避策
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]
ソリューション
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