4:47 a.m.
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#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...