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

Multiple error messages in logs after enabling snapshot isolation level in MSSQL database

    • Icon: Bug Bug
    • Resolution: Support Request
    • Icon: Medium Medium
    • None
    • 2.9.1
    • None

      To prevent multiple deadlocks on our database (MSSQL Server) we switch isolation level to snapshot isolation. And now we are getting following multiple messages in a log-file:

      2008-09-10 09:48:10,221 ERROR [TP-Processor16] [atlassian.xwork.interceptors.XWorkTransactionInterceptor] onThrowable Invoking rollback for transaction on action '/pages/viewpage.action (ViewPageAction.execute())' due to throwable: org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not update: [bucket.user.propertyset.BucketPropertySetItem#bucket.user.propertyset.BucketPropertySetItem@ccf6bf53]; 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 'Confluence_2_9' 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.
      – url: /wiki/display/Connectivity/Home+of+Connectivity+product | userName: evstidm
      org.springframework.jdbc.BadSqlGrammarException: Hibernate operation: could not update: [bucket.user.propertyset.BucketPropertySetItem#bucket.user.propertyset.BucketPropertySetItem@ccf6bf53]; 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 'Confluence_2_9' 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 'Confluence_2_9' 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.
      at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:368)
      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)

            [CONFSERVER-13005] Multiple error messages in logs after enabling snapshot isolation level in MSSQL database

            We also have this issue

             

            Joel Enriquez added a comment - We also have this issue  

            Anatoli added a comment -

            Hi Stuart,

            We have not found a solution yet. We will update confluence documentation once we found a solution.

            Anatoli.

            Anatoli added a comment - Hi Stuart, We have not found a solution yet. We will update confluence documentation once we found a solution. Anatoli.

            We are preparing to switch to SQL Server 2005 using Confluence 2.8.1 and are seeing this error as well.

            I have set the snapshot isolation level to 4096 in confluence as referenced here http://www.hibernate.org/382.html#A6 which http://confluence.atlassian.com/display/DOC/Known+Issues+For+SQL+Server;jsessionid=2C5557BD9F487F28A69770DA5DEE24E5\ refers to and ran the following commands on my database:

            ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
            ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON

            Was a solution found?

            Stuart Gilberd added a comment - We are preparing to switch to SQL Server 2005 using Confluence 2.8.1 and are seeing this error as well. I have set the snapshot isolation level to 4096 in confluence as referenced here http://www.hibernate.org/382.html#A6 which http://confluence.atlassian.com/display/DOC/Known+Issues+For+SQL+Server;jsessionid=2C5557BD9F487F28A69770DA5DEE24E5\ refers to and ran the following commands on my database: ALTER DATABASE <database name> SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT ALTER DATABASE <database name> SET ALLOW_SNAPSHOT_ISOLATION ON Was a solution found?

            Hi Dzmitry,

            My initial impression is that you might not have configured your database correctly. Could you please raise a support request to help diagnose this problem?
            We can reopen this issue if we have confirmed a real bug.

            Cheers,
            Andrew Lynch

            Andrew Lynch (Inactive) added a comment - Hi Dzmitry, My initial impression is that you might not have configured your database correctly. Could you please raise a support request to help diagnose this problem? We can reopen this issue if we have confirmed a real bug. Cheers, Andrew Lynch

              Unassigned Unassigned
              4b9af5e8891c DzmitryZ
              Affected customers:
              0 This affects my team
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: