Uploaded image for project: 'Confluence Data Center'
  1. Confluence Data Center
  2. CONFSERVER-21909

Deadlocks in Oracle 11G due to missing indexes

XMLWordPrintable

      Bug Description

      This bug takes place only while using Oracle 11G. The problem takes place when some users edit a page and save their pages nearly the same time. The following stacktrace will be logged in atlassian-confluence.log:

      ORA-00060: Deadlock detected while waiting for resource
- url: /pages/doeditpage.action | page: 31522840 | userName: test | referer: https://test/pages/editpage.action?pageId=31522840 | action: doeditpage

Could not synchronize database state with session
- url: /pages/doeditpage.action | page: 31522840 | userName: test | referer: https://test/pages/editpage.action?pageId=31522840 | action: doeditpage

Action /pages/doeditpage.action (EditPageAction.doEdit()) is already completed and can not be committed again.
- url: /pages/doeditpage.action | userName: test | referer: https://test/pages/editpage.action?pageId=31522840

Could not execute action
- url: /pages/doeditpage.action | userName: test | referer: https://test/pages/editpage.action?pageId=31522840
org.springframework.transaction.IllegalTransactionStateException: Transaction is already completed - do not call commit or rollback more than once per transaction
at org.springframework.transaction.support.AbstractPlatformTransactionManager.rollback(AbstractPlatformTransactionManager.java:725)
at com.atlassian.xwork.interceptors.TransactionalInvocation.commitOrRollbackTransaction(TransactionalInvocation.java:93)
at com.atlassian.xwork.interceptors.TransactionalInvocation.invokeInTransaction(TransactionalInvocation.java:63)
at com.atlassian.xwork.interceptors.XWorkTransactionInterceptor.intercept(XWorkTransactionInterceptor.java:56)
at com.opensymphony.xwork.DefaultActionInvocation.invoke(DefaultActionInvocation.java:165)
at com.atlassian.confluence.xwork.SetupIncompleteInterceptor.intercept(SetupIncompleteInterceptor.java:40)
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.atlassian.confluence.servlet.ConfluenceServletDispatcher.serviceAction(ConfluenceServletDispatcher.java:53)
at com.opensymphony.webwork.dispatcher.ServletDispatcher.service(ServletDispatcher.java:199)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.security.websudo.MessagesDecoratorFilter.doFilter(MessagesDecoratorFilter.java:49)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.opensymphony.module.sitemesh.filter.PageFilter.parsePage(PageFilter.java:118)
at com.atlassian.confluence.util.profiling.ProfilingPageFilter.parsePage(ProfilingPageFilter.java:84)
at com.opensymphony.module.sitemesh.filter.PageFilter.doFilter(PageFilter.java:54)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.jmx.JmxFilter.doFilter(JmxFilter.java:106)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.core.filters.ServletContextThreadLocalFilter.doFilter(ServletContextThreadLocalFilter.java:19)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.util.LoggingContextFilter.doFilter(LoggingContextFilter.java:47)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.web.filter.ThreadLocalCacheFilter.doFilter(ThreadLocalCacheFilter.java:22)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.util.UserThreadLocalFilter.doFilter(UserThreadLocalFilter.java:44)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.seraph.filter.SecurityFilter.doFilter(SecurityFilter.java:213)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.security.auth.trustedapps.filter.TrustedApplicationsFilter.doFilter(TrustedApplicationsFilter.java:98)
at com.atlassian.confluence.util.AbstractBootstrapHotSwappingFilter.doFilter(AbstractBootstrapHotSwappingFilter.java:30)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.seraph.filter.BaseLoginFilter.doFilter(BaseLoginFilter.java:142)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
at com.atlassian.oauth.serviceprovider.internal.servlet.OAuthFilter.doFilter(OAuthFilter.java:69)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.util.ClusterHeaderFilter.doFilter(ClusterHeaderFilter.java:34)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.johnson.filters.AbstractJohnsonFilter.doFilter(AbstractJohnsonFilter.java:72)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
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:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.util.ConfluenceErrorFilter.doFilter(ConfluenceErrorFilter.java:22)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.util.profiling.filters.ProfilingFilter.doFilter(ProfilingFilter.java:99)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.core.datetime.RequestTimeThreadLocalFilter.doFilter(RequestTimeThreadLocalFilter.java:35)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.util.RequestCacheThreadLocalFilter.doFilter(RequestCacheThreadLocalFilter.java:25)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.gzipfilter.GzipFilter.doFilterInternal(GzipFilter.java:81)
at com.atlassian.gzipfilter.GzipFilter.doFilter(GzipFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.core.filters.cache.AbstractCachingFilter.doFilter(AbstractCachingFilter.java:33)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:46)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter$1.doFilter(DelegatingPluginFilter.java:66)
at com.atlassian.confluence.extra.webdav.servlet.filter.ReverseProxyFilter.doFilter(ReverseProxyFilter.java:427)
at com.atlassian.confluence.extra.webdav.servlet.filter.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:34)
at com.atlassian.plugin.servlet.filter.DelegatingPluginFilter.doFilter(DelegatingPluginFilter.java:74)
at com.atlassian.plugin.servlet.filter.IteratingFilterChain.doFilter(IteratingFilterChain.java:42)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:77)
at com.atlassian.plugin.servlet.filter.ServletFilterModuleContainerFilter.doFilter(ServletFilterModuleContainerFilter.java:63)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.web.filter.validateparam.RequestParamValidationFilter.doFilter(RequestParamValidationFilter.java:58)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.plugin.servlet.filter.ActionContextCleanUp.doFilter(ActionContextCleanUp.java:71)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.core.filters.encoding.AbstractEncodingFilter.doFilter(AbstractEncodingFilter.java:41)
at com.atlassian.core.filters.AbstractHttpFilter.doFilter(AbstractHttpFilter.java:31)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.core.filters.HeaderSanitisingFilter.doFilter(HeaderSanitisingFilter.java:44)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at com.atlassian.confluence.servlet.FourOhFourErrorLoggingFilter.doFilter(FourOhFourErrorLoggingFilter.java:65)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:558)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11NioProcessor.process(Http11NioProcessor.java:883)
at org.apache.coyote.http11.Http11NioProtocol$Http11ConnectionHandler.process(Http11NioProtocol.java:721)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:2258)
at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
at java.lang.Thread.run(Thread.java:619)
      Root cause

      The following columns have foreign key constraints against them but no indexes on the columns themselves.

      CONFANCESTORS.ANCESTORID                     
EXTERNAL_MEMBERS.EXTENTITYID                    
LOCAL_MEMBERS.USERID                         
OS_USER_GROUP.GROUP_ID                       
TRUSTEDAPPRESTRICTION.TRUSTEDAPPID

      For example, the CONTENT table has a foreign key constraint on its ANCESTORID column against CONFANCESTORS.ANCESTORID but there is no index on that ANCESTORID column in the table CONFANCESTORS. This can lead to performance problems, and has been shown for some customers to lead to a deadlock problem in Oracle 11G, which is more deadlock sensitive than 10G.

      Here's an explanation of the situation. If two people save a (different) page and these pages have been saved as draft before, Confluence will be doing two database operations for each user:

      1. Deleting the draft
      2. Updating the original page
        As there is no index on column ANCESTORID in table CONFANCESTORS (but the table is referenced via a foreign key constraint), Oracle has to lock the whole table CONFANCESTORS to perform the delete operation in the table CONTENT. This may lead to deadlock situation if the other user want to update and delete at the same time.
      Workaround
      1. Execute http://www.ixora.com.au/scripts/sql/missing_fk_indexes.sql to obtain columns which are used as foreign keys but don't have indexes on them.
      2. Create indexes for all tables/columns returned by it in confluence schema.

      That should eliminate table-level locks.

        1. with-index.jpg
          with-index.jpg
          354 kB
        2. without-index.jpg
          without-index.jpg
          373 kB

          Form Name

            [CONFSERVER-21909] Deadlocks in Oracle 11G due to missing indexes

            Jens Kasperek (Bosch GmbH) (Inactive) added a comment -

            We have the same issue when users try to move page trees on our Confluence 5.5.6 instance. Because there is also no UI hint how far the move progress is, users start multiple page moves at once.

            Therefore, we really demand a fix or at least some UI improvements that inform users about current progress !

            Jens Kasperek (Bosch GmbH) (Inactive) added a comment - We have the same issue when users try to move page trees on our Confluence 5.5.6 instance. Because there is also no UI hint how far the move progress is, users start multiple page moves at once. Therefore, we really demand a fix or at least some UI improvements that inform users about current progress !

            Petro Semeniuk (Inactive) added a comment -

            dana.jansen@twcable.com this is not specific to Oracle. We have another issue for deadlocks during page move: https://jira.atlassian.com/browse/CONF-35040

            Petro Semeniuk (Inactive) added a comment - dana.jansen@twcable.com this is not specific to Oracle. We have another issue for deadlocks during page move: https://jira.atlassian.com/browse/CONF-35040

            Dana Jansen added a comment -

            I was moving a very large number of pages from one space to another when this deadlock occurred. It has not happened again (of course I have not moved a large number of pages between spaces again)

            Dana Jansen added a comment - I was moving a very large number of pages from one space to another when this deadlock occurred. It has not happened again (of course I have not moved a large number of pages between spaces again)

            Dana Jansen added a comment -

            This is the SQL that is being executed causing the deadlock
            update CONTENT set CHILD_POSITION=:1 , PARENTID=:2 , SPACEID=:3 , TITLE=:4 , VERSION=:5 , CREATOR=:6 , CREATIONDATE=:7 , LASTMODIFIER=:8 , LASTMODDATE=:9 , VERSIONCOMMENT=:10 , PREVVER=:11 , CONTENT_STATUS=:12 where CONTENTID=:13

            I'm trying to get the oracle log information from the DBAs

            Dana Jansen added a comment - This is the SQL that is being executed causing the deadlock update CONTENT set CHILD_POSITION=:1 , PARENTID=:2 , SPACEID=:3 , TITLE=:4 , VERSION=:5 , CREATOR=:6 , CREATIONDATE=:7 , LASTMODIFIER=:8 , LASTMODDATE=:9 , VERSIONCOMMENT=:10 , PREVVER=:11 , CONTENT_STATUS=:12 where CONTENTID=:13 I'm trying to get the oracle log information from the DBAs

            Olli Nevalainen added a comment -

            Hi dana.jansen@twcable.com,

            I'm not aware of any other steps to resolve this specific issue. Would you be able to raise a support issue so we can have a closer look at what is going wrong in your instance?

            Olli Nevalainen added a comment - Hi dana.jansen@twcable.com , I'm not aware of any other steps to resolve this specific issue. Would you be able to raise a support issue so we can have a closer look at what is going wrong in your instance?

            Dana Jansen added a comment -

            The missing indexes were added to the database and we are still seeing deadlocks.
            is there anything else we need to look at in order to resolve this issue?

            Dana Jansen added a comment - The missing indexes were added to the database and we are still seeing deadlocks. is there anything else we need to look at in order to resolve this issue?

            Thomas Strempel added a comment -

            We have the same issue in CF 5.2.5

            Thomas Strempel added a comment - We have the same issue in CF 5.2.5

            Sergey Svishchev added a comment - - edited

            Steve, thank you for that. When CONF-30110 is going to have the same treatment?

            Perhaps Atlassian will also review everything performance/enterprise-related and tag it with a recognizable label or epic link, so that customers know what to expect.

            Sergey Svishchev added a comment - - edited Steve, thank you for that. When CONF-30110 is going to have the same treatment? Perhaps Atlassian will also review everything performance/enterprise-related and tag it with a recognizable label or epic link, so that customers know what to expect.

            Steve Haffenden (Inactive) added a comment -

            Hi Everyone.

            Thanks for being so patient with this issue. Three years is far too long a time to wait for a fix. This issue is on our backlog and will be addressed in the very near future. As soon as we have any further updates for you we'll let you know.

            Regards
            Steve Haffenden
            Confluence Bugmaster
            Atlassian

            Steve Haffenden (Inactive) added a comment - Hi Everyone. Thanks for being so patient with this issue. Three years is far too long a time to wait for a fix. This issue is on our backlog and will be addressed in the very near future. As soon as we have any further updates for you we'll let you know. Regards Steve Haffenden Confluence Bugmaster Atlassian

            Sergey Svishchev added a comment -

            It's probably time to write Atlassian's Enterprise Advocate and let them know of this bug and its lack of progress.

            Sergey Svishchev added a comment - It's probably time to write Atlassian's Enterprise Advocate and let them know of this bug and its lack of progress.

              onevalainen Olli Nevalainen
              sjayaraman Sashidaran Jayaraman [Atlassian]
              Affected customers:
              25 This affects my team
              Watchers:
              46 Start watching this issue

                Created:
                Updated:
                Resolved: