[jboss-user] [JBoss Messaging] - Poss solution frequent deadlocks on SQLServer
Jeremy Stone
do-not-reply at jboss.com
Mon Nov 12 05:47:49 EST 2007
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?
View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4103599#4103599
Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4103599
More information about the jboss-user
mailing list