-
Bug
-
Resolution: Fixed
-
Medium
-
None
-
None
Getting deadlocks when Confluence is using SQL Server 2005 for its database. See attached files for more information
This usually occurs when concurrent page create/edits occur. The following is a sample stack trace of such an error. In this case, two concurrent page creates deadlock when a draft is being deleted (this occurs in the same transaction, after the new page has been saved).
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) at org.springframework.orm.hibernate.HibernateTransactionManager.doCommit(HibernateTransactionManager.java:510) at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:662) at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:632) at com.atlassian.xwork.interceptors.XWorkTransactionInterceptor$1.beforeResult(XWorkTransactionInterceptor.java:88) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:181) at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.opensymphony.xwork.interceptor.DefaultWorkflowInterceptor.intercept(DefaultWorkflowInterceptor.java:55) at com.atlassian.confluence.core.ConfluenceWorkflowInterceptor.intercept(ConfluenceWorkflowInterceptor.java:35) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.security.interceptors.CaptchaInterceptor.intercept(CaptchaInterceptor.java:46) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.util.LoggingContextInterceptor.intercept(LoggingContextInterceptor.java:48) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.core.CancellingInterceptor.intercept(CancellingInterceptor.java:23) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.security.actions.PermissionCheckInterceptor.intercept(PermissionCheckInterceptor.java:54) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.pages.actions.CommentAwareInterceptor.intercept(CommentAwareInterceptor.java:43) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.pages.actions.PageAwareInterceptor.intercept(PageAwareInterceptor.java:120) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.spaces.actions.SpaceAwareInterceptor.intercept(SpaceAwareInterceptor.java:67) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.core.actions.LastModifiedInterceptor.intercept(LastModifiedInterceptor.java:39) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.confluence.core.ConfluenceAutowireInterceptor.intercept(ConfluenceAutowireInterceptor.java:25) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.atlassian.xwork.interceptors.XWorkTransactionInterceptor.intercept(XWorkTransactionInterceptor.java:97) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.opensymphony.xwork.interceptor.AroundInterceptor.intercept(AroundInterceptor.java:35) at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165) at com.opensymphony.xwork.DefaultActionProxy.execute(DefaultActionProxy.java:115) at com.opensymphony.webwork.dispatcher.ServletDispatcher.serviceAction(ServletDispatcher.java:229) at com.opensymphony.webwork.dispatcher.ServletDispatcher.service(ServletDispatcher.java:199) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118) at com.atlassian.confluence.util.profiling.ProfilingPageFilter.parsePage(ProfilingPageFilter.java:153) at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:54) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.confluence.jmx.JmxFilter.doFilter(JmxFilter.java:109) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.core.filters.ServletContextThreadLocalFilter.doFilter(ServletContextThreadLocalFilter.java:21) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.confluence.util.LoggingContextFilter.doFilter(LoggingContextFilter.java:49) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.confluence.util.UserThreadLocalFilter.doFilter(UserThreadLocalFilter.java:44) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.seraph.filter.SecurityFilter.doFilter(SecurityFilter.java:192) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.seraph.filter.TrustedApplicationsFilter.doFilter(TrustedApplicationsFilter.java:120) at com.atlassian.confluence.util.AbstractBootstrapHotSwappingFilter.doFilter(AbstractBootstrapHotSwappingFilter.java:28) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.seraph.filter.BaseLoginFilter.doFilter(BaseLoginFilter.java:125) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.confluence.util.ClusterHeaderFilter.doFilter(ClusterHeaderFilter.java:35) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.johnson.filters.AbstractJohnsonFilter.doFilter(AbstractJohnsonFilter.java:72) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.springframework.orm.hibernate.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:170) at com.atlassian.spring.filter.FlushingSpringSessionInViewFilter.doFilterInternal(FlushingSpringSessionInViewFilter.java:29) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:75) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.util.profiling.filters.ProfilingFilter.doFilter(ProfilingFilter.java:142) at com.atlassian.core.filters.ProfilingAndErrorFilter.doFilter(ProfilingAndErrorFilter.java:27) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.confluence.core.datetime.RequestTimeThreadLocalFilter.doFilter(RequestTimeThreadLocalFilter.java:34) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.confluence.util.RequestCacheThreadLocalFilter.doFilter(RequestCacheThreadLocalFilter.java:25) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.gzipfilter.GzipFilter.doFilterInternal(GzipFilter.java:94) at com.atlassian.gzipfilter.GzipFilter.doFilter(GzipFilter.java:64) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at com.atlassian.core.filters.AbstractEncodingFilter.doFilter(AbstractEncodingFilter.java:33) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870) at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665) at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528) at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685) at java.lang.Thread.run(Thread.java:613)
- is related to
-
-
- Closed
-
[CONFSERVER-13668] Deadlocks in MS SQL Server
chua hock huat
added a comment - Hi Andrew,
We have managed to run the SQL script on our confluence database.
We will monitor the situation whether the deadlock still happen...
Thx and regards
chua hock huat
added a comment - Hi Andrew,
We have managed to run the SQL script on our confluence database.
We will monitor the situation whether the deadlock still happen...
Thx and regards
This issue is caused by a missing index on one of the columns with a foreign key constraint.
From 2.10 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 (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
Wasu Koysiripong [Atlassian]
added a comment - This is kinda weird. According to the file, dedlok.xdl, two processes have exclusive locks on the same object and they both try to acquire shared locks on that object. 
Wasu Koysiripong [Atlassian]
added a comment - This is kinda weird. According to the file, dedlok.xdl, two processes have exclusive locks on the same object and they both try to acquire shared locks on that object. The deadlocks occur with isolation levels: read committed (the default) and read committed using row versioning.
We originally recommended snapshot isolation to customers who encountered deadlocks with the default read committed isolation level, however that results in other problems:
2008-10-31 15:33:20,938 ERROR [resin-tcp-connection-*:8081-67] [opensymphony.webwork.dispatcher.ServletDispatcher] serviceAction Could not execute action -- url: /pages/viewpage.action | userName: anonymous org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not update: [bucket.user.propertyset.BucketPropertySetItem#bucket.user.propertyset.BucketPropertySetItem@cf06bf4a]; bad SQL grammar []; nested exception is java.sql.SQLException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.OS_PROPERTYENTRY' directly or indirectly in database 'Community02' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement. Caused by: java.sql.SQLException: Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.OS_PROPERTYENTRY' directly or indirectly in database 'Community02' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.
Just an update on this issue: It seems that the solution to add indexes does not work for deadlocks against the os_propertyentry table. A
separate issuewill be logged for this deadlock and support will be looking into it.