7 Replies Latest reply on Nov 15, 2007 6:59 AM by Jeremy Stone

    Poss solution frequent deadlocks on SQLServer

    Jeremy Stone Newbie

      When using SQLServer for the persistence back-end for JBoss Messaging we have been experiencing a large number of deadlocks when consumer thread transactions are committed:

      2007-11-12 10:26:29,088 431718 WARN [org.jboss.messaging.core.impl.JDBCSupport] (Thread-42:) SQLException caught, SQLState 40001 code:1205- assuming deadlock detected, try:1
      com.microsoft.sqlserver.jdbc.SQLServerException: Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
       at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(Unknown Source)
       at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(Unknown Source)
       at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(Unknown Source)
       at org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeUpdate(CachedPreparedStatement.java:95)
       at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:251)
       at org.jboss.messaging.core.impl.JDBCPersistenceManager$1HandleBeforeCommit2PCRunner.doTransaction(JDBCPersistenceManager.java:1447)
       at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner2.execute(JDBCSupport.java:453)
       at org.jboss.messaging.core.impl.JDBCSupport$JDBCTxRunner2.executeWithRetry(JDBCSupport.java:491)
       at org.jboss.messaging.core.impl.JDBCPersistenceManager.handleBeforeCommit2PC(JDBCPersistenceManager.java:1472)
       at org.jboss.messaging.core.impl.JDBCPersistenceManager$TransactionCallback.beforeCommit(JDBCPersistenceManager.java:2435)
       at org.jboss.messaging.core.impl.tx.Transaction.commit(Transaction.java:208)
       at org.jboss.jms.server.endpoint.ServerConnectionEndpoint.sendTransaction(ServerConnectionEndpoint.java:471)
       at org.jboss.jms.server.endpoint.advised.ConnectionAdvised.org$jboss$jms$server$endpoint$advised$ConnectionAdvised$sendTransaction$aop(ConnectionAdvised.java:101)
       at org.jboss.jms.server.endpoint.advised.ConnectionAdvised$sendTransaction_N3268650789275322226.invokeNext(ConnectionAdvised$sendTransaction_N3268650789275322226.java)
       at org.jboss.jms.server.container.SecurityAspect.handleSendTransaction(SecurityAspect.java:195)
       at sun.reflect.GeneratedMethodAccessor341.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:585)
       at org.jboss.aop.advice.PerInstanceAdvice.invoke(PerInstanceAdvice.java:121)
       at org.jboss.jms.server.endpoint.advised.ConnectionAdvised$sendTransaction_N3268650789275322226.invokeNext(ConnectionAdvised$sendTransaction_N3268650789275322226.java)
       at org.jboss.jms.server.container.ServerLogInterceptor.invoke(ServerLogInterceptor.java:105)
       at org.jboss.jms.server.endpoint.advised.ConnectionAdvised$sendTransaction_N3268650789275322226.invokeNext(ConnectionAdvised$sendTransaction_N3268650789275322226.java)
       at org.jboss.jms.server.endpoint.advised.ConnectionAdvised.sendTransaction(ConnectionAdvised.java)
       at org.jboss.jms.wireformat.ConnectionSendTransactionRequest.serverInvoke(ConnectionSendTransactionRequest.java:82)
       at org.jboss.jms.server.remoting.JMSServerInvocationHandler.invoke(JMSServerInvocationHandler.java:143)
       at org.jboss.remoting.ServerInvoker.invoke(ServerInvoker.java:769)
       at org.jboss.remoting.transport.local.LocalClientInvoker.invoke(LocalClientInvoker.java:101)
       at org.jboss.remoting.Client.invoke(Client.java:1634)
       at org.jboss.remoting.Client.invoke(Client.java:548)
       at org.jboss.remoting.Client.invoke(Client.java:536)
       at org.jboss.jms.client.delegate.DelegateSupport.doInvoke(DelegateSupport.java:187)
       at org.jboss.jms.client.delegate.DelegateSupport.doInvoke(DelegateSupport.java:158)
       at org.jboss.jms.client.delegate.ClientConnectionDelegate.org$jboss$jms$client$delegate$ClientConnectionDelegate$sendTransaction$aop(ClientConnectionDelegate.java:221)
       at org.jboss.jms.client.delegate.ClientConnectionDelegate$sendTransaction_N3268650789275322226.invokeNext(ClientConnectionDelegate$sendTransaction_N3268650789275322226.java)
       at org.jboss.jms.client.container.FailoverValveInterceptor.invoke(FailoverValveInterceptor.java:92)
       at org.jboss.aop.advice.PerInstanceInterceptor.invoke(PerInstanceInterceptor.java:105)
       at org.jboss.jms.client.delegate.ClientConnectionDelegate$sendTransaction_N3268650789275322226.invokeNext(ClientConnectionDelegate$sendTransaction_N3268650789275322226.java)
       at org.jboss.jms.client.container.ClosedInterceptor.invoke(ClosedInterceptor.java:170)
       at org.jboss.aop.advice.PerInstanceInterceptor.invoke(PerInstanceInterceptor.java:105)
       at org.jboss.jms.client.delegate.ClientConnectionDelegate$sendTransaction_N3268650789275322226.invokeNext(ClientConnectionDelegate$sendTransaction_N3268650789275322226.java)
       at org.jboss.jms.client.delegate.ClientConnectionDelegate.sendTransaction(ClientConnectionDelegate.java)
       at org.jboss.jms.tx.ResourceManager.sendTransactionXA(ResourceManager.java:637)
       at org.jboss.jms.tx.ResourceManager.commit(ResourceManager.java:393)
       at org.jboss.jms.tx.MessagingXAResource.commit(MessagingXAResource.java:238)
       at org.jboss.resource.connectionmanager.xa.JcaXAResourceWrapper.commit(JcaXAResourceWrapper.java:53)
       at com.arjuna.ats.internal.jta.resources.arjunacore.XAResourceRecord.topLevelCommit(XAResourceRecord.java:487)
       at com.arjuna.ats.arjuna.coordinator.BasicAction.doCommit(BasicAction.java:3107)
       at com.arjuna.ats.arjuna.coordinator.BasicAction.doCommit(BasicAction.java:3022)
       at com.arjuna.ats.arjuna.coordinator.BasicAction.phase2Commit(BasicAction.java:2126)
       at com.arjuna.ats.arjuna.coordinator.BasicAction.End(BasicAction.java:1819)
       at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(TwoPhaseCoordinator.java:88)
       at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:177)
       at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1382)
       at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:135)
       at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:87)
       at org.jboss.tm.usertx.client.ServerVMClientUserTransaction.commit(ServerVMClientUserTransaction.java:140)
       at org.springframework.transaction.jta.JtaTransactionManager.doCommit(JtaTransactionManager.java:842)
       at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:662)
       at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:632)
       at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:314)
       at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:116)
       at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
       at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
       at $Proxy82.onMessage(Unknown Source)
       at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:510)
       at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:445)
       at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:414)
       at org.springframework.jms.listener.AbstractMessageListenerContainer.executeListener(AbstractMessageListenerContainer.java:386)
       at org.springframework.jms.listener.SimpleMessageListenerContainer$2.onMessage(SimpleMessageListenerContainer.java:204)
       at org.jboss.resource.adapter.jms.JmsMessageListener.onMessage(JmsMessageListener.java:56)
       at org.jboss.jms.client.container.ClientConsumer.callOnMessage(ClientConsumer.java:157)
       at org.jboss.jms.client.container.ClientConsumer$ListenerRunner.run(ClientConsumer.java:965)
       at EDU.oswego.cs.dl.util.concurrent.QueuedExecutor$RunLoop.run(QueuedExecutor.java:89)
       at java.lang.Thread.run(Thread.java:595)
      


      Our application uses the Spring SimpleMessageListenerContainer to consume messages from a queue and process them in some way. Processing often involves database updates as well as writing other messages to the same and other queues for other types of processing.

      Analysis of the deadlock with the SQL Server Profiler indicated contention between various threads using the COMMIT_MESSAGE_REF1 query
      (UPDATE JBM_MSG_REF SET STATE='C', TRANSACTION_ID = NULL WHERE TRANSACTION_ID=? AND STATE='+').

      Looking at the indexes defined on the JBM_MSG_REF table we noticed that the STATE column is not included. Adding the STATE column after the TRANSACTION_ID column fixed the deadlock problem for us. I.e. the mssql-persistence-service.xml CREATE_IDX_MESSAGE_REF_TX line now reads...

      CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID, STATE)
      


      Not sure yet if this will have any undesired side effects or whether the same change should be applied for other database persistence serices.

      Any thoughts?

        • 1. Re: Poss solution frequent deadlocks on SQLServer
          Tim Fox Master

          Thanks Jeremy for that. :)

          I'll run some tests here and see if we can incorporate your fix.

          BTW Regarding your usage of Spring, I don't know it applies in your case, but have you seen http://wiki.jboss.org/wiki/Wiki.jsp?page=JBMSpringJMSTemplateNotes ?

          • 2. Re: Poss solution frequent deadlocks on SQLServer
            Jeremy Stone Newbie

            No problem - hope it helps.

            I have seen the JBMSpringJMSTemplateNotes wiki and we are only sending messages using the JCA factory so I think that's ok.

            However we also use Spring to receive messages. (We were originally using their DefaultMessageListenerContainer but turned out to be SLOW -- as I notice you've mentioned on previous occassions). So we started using their SimpleMessageListenerContainer which registers a MessageListener on a MessageConsumer. However the problem with this is that messages seem to be getting acknowleged in spite of failure (setRollbackOnly called) and no redelivery is occurring.

            Looking at the JBoss Messaging source, SessionAspect.handlePostDeliver() calls isXAAndConsideredNonTransacted() which returns true because the SessionState's treatAsNonTransactedWhenNotEnlisted is set to true.

            Is the solution to somehow to set treatAsNonTransactedWhenNotEnlisted to false? What does an MDB container do here?

            • 3. Re: Poss solution frequent deadlocks on SQLServer
              Tim Fox Master

               

              "JeremyStone" wrote:
              However the problem with this is that messages seem to be getting acknowleged in spite of failure (setRollbackOnly called) and no redelivery is occurring.

              Looking at the JBoss Messaging source, SessionAspect.handlePostDeliver() calls isXAAndConsideredNonTransacted() which returns true because the SessionState's treatAsNonTransactedWhenNotEnlisted is set to true.

              Is the solution to somehow to set treatAsNonTransactedWhenNotEnlisted to false? What does an MDB container do here?


              Yes, this is probably because Spring isn't enlisting the session in the JTA transaction until after it has received the message from JBM using a message listener.

              Either that or there is a small period of time between Spring delisting the session from the JTA tx before commit and enlisting it in the next one, when a message can be received but the session is not enlisted. The session is therefore not transacted at that point so acts as auto_ack and acks immediately.


              • 5. Re: Poss solution frequent deadlocks on SQLServer
                Jeremy Stone Newbie

                Regarding Spring - that does appear to be what's happening. Trying alternative approach with the DefaultMessageListenerContainer (again) but without setting a transactionManager so that it will cache all JMS resources.

                Thanks

                • 6. Re: Poss solution frequent deadlocks on SQLServer
                  Tim Fox Master

                   

                  "Jeremy Stone" wrote:


                  CREATE_IDX_MESSAGE_REF_TX=CREATE INDEX JBM_MSG_REF_TX ON JBM_MSG_REF (TRANSACTION_ID, STATE)
                  


                  Not sure yet if this will have any undesired side effects or whether the same change should be applied for other database persistence serices.

                  Any thoughts?


                  Instead of adding the state column to the JBM_MSG_REF_TX index, does it also solve your problem by adding a new index solely on the state column.

                  e.g. CREATE INDEX TEST_INDEX_STATE ON JBM_MSG_REF(STATE). ?

                  • 7. Re: Poss solution frequent deadlocks on SQLServer
                    Jeremy Stone Newbie

                    No. Unfortunately, we still get the deadlock exception from SQLServer with TEST_INDEX_STATE as described and only the TRANSACTION_ID in the JBM_MSG_REF_TX index.