Deadlock detected when using JBOSSMQ with Sybase Anywhere
demesne1 Jun 16, 2006 9:23 AMUsing Jboss 4.0.3 SP1 with Sybase anywhere version 9.0.2 (3302) it throws a deadlock exception when using JMS.
The configuration is as follows:
1) In the Sybase datasource file the transaction-isolation is set to TRANSACTION_READ_COMMITTED
2) Set the Sybase isolation_level to 1
3) Created additional indexes on the JMS_MESSAGES table. (No effect)
4) The LOCK DATAROWS option is not available in Sybase anywhere. Is there a similar configuration paramater?
5) Remove the UNION in the SELECT_MAX_TX sql command. (No effect)
Any assistance on this would be greatly appreciated.
sybasedb-jdbc2-service.xml
BLOB_TYPE=BYTES_BLOB
INSERT_TX = INSERT INTO JMS_TRANSACTIONS (TXID) values(?)
INSERT_MESSAGE = INSERT INTO JMS_MESSAGES (MESSAGEID, DESTINATION, MESSAGEBLOB, TXID, TXOP) VALUES(?,?,?,?,?)
SELECT_ALL_UNCOMMITED_TXS = SELECT TXID FROM JMS_TRANSACTIONS
SELECT_MAX_TX = SELECT MAX(TXID) FROM JMS_TRANSACTIONS UNION SELECT MAX(TXID) FROM JMS_MESSAGES
DELETE_ALL_TX = DELETE FROM JMS_TRANSACTIONS
SELECT_MESSAGES_IN_DEST = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE DESTINATION=?
SELECT_MESSAGE = SELECT MESSAGEID, MESSAGEBLOB FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
MARK_MESSAGE = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE MESSAGEID=? AND DESTINATION=?
UPDATE_MESSAGE = UPDATE JMS_MESSAGES SET MESSAGEBLOB=? WHERE MESSAGEID=? AND DESTINATION=?
UPDATE_MARKED_MESSAGES = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=?
UPDATE_MARKED_MESSAGES_WITH_TX = UPDATE JMS_MESSAGES SET TXID=?, TXOP=? WHERE TXOP=? AND TXID=?
DELETE_MARKED_MESSAGES_WITH_TX = DELETE FROM JMS_MESSAGES WHERE TXOP=? AND JMS_MESSAGES.TXID IN (SELECT TXID FROM JMS_TRANSACTIONS)
DELETE_TX = DELETE FROM JMS_TRANSACTIONS WHERE TXID = ?
DELETE_MARKED_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXID=? AND TXOP=?
DELETE_TEMPORARY_MESSAGES = DELETE FROM JMS_MESSAGES WHERE TXOP='T'
DELETE_MESSAGE = DELETE FROM JMS_MESSAGES WHERE MESSAGEID=? AND DESTINATION=?
CREATE_MESSAGE_TABLE = CREATE TABLE JMS_MESSAGES ( MESSAGEID INTEGER NOT NULL, \
DESTINATION VARCHAR(255) NOT NULL, TXID INTEGER NULL, TXOP CHAR(1), \
MESSAGEBLOB IMAGE, PRIMARY KEY (MESSAGEID, DESTINATION) )
CREATE_IDX_MESSAGE_TXOP_TXID = CREATE INDEX JMS_MESSAGES_TXOP_TXID ON JMS_MESSAGES (TXOP, TXID)
CREATE_IDX_MESSAGE_DESTINATION = CREATE INDEX JMS_MESSAGES_DESTINATION ON JMS_MESSAGES (DESTINATION)
CREATE_TX_TABLE = CREATE TABLE JMS_TRANSACTIONS ( TXID INTEGER, PRIMARY KEY (TXID) )
CREATE_TABLES_ON_STARTUP = TRUE
org.jboss.mq.SpyJMSException: Could not remove message: 2188 msg=800 hard STORED PERSISTENT queue=QUEUE.B priority=4 lateClone=false hashCode=8938306; - nested throwable: (com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -306: Deadlock detected)
at org.jboss.mq.pm.jdbc2.PersistenceManager.remove(PersistenceManager.java:1136)
at org.jboss.mq.server.BasicQueue.acknowledge(BasicQueue.java:564)
at org.jboss.mq.server.JMSQueue.acknowledge(JMSQueue.java:156)
at org.jboss.mq.server.ClientConsumer.acknowledge(ClientConsumer.java:328)
at org.jboss.mq.server.JMSDestinationManager.acknowledge(JMSDestinationManager.java:519)
at org.jboss.mq.server.JMSDestinationManager.transact(JMSDestinationManager.java:464)
at org.jboss.mq.server.JMSServerInterceptorSupport.transact(JMSServerInterceptorSupport.java:186)
at org.jboss.mq.security.ServerSecurityInterceptor.transact(ServerSecurityInterceptor.java:182)
at org.jboss.mq.server.TracingInterceptor.transact(TracingInterceptor.java:412)
at org.jboss.mq.server.JMSServerInvoker.transact(JMSServerInvoker.java:186)
at org.jboss.mq.il.uil2.ServerSocketManagerHandler.handleMsg(ServerSocketManagerHandler.java:172)
at org.jboss.mq.il.uil2.SocketManager$ReadTask.handleMsg(SocketManager.java:369)
at org.jboss.mq.il.uil2.msgs.BaseMsg.run(BaseMsg.java:377)
at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:748)
at java.lang.Thread.run(Thread.java:595)
Caused by: com.sybase.jdbc2.jdbc.SybSQLException: ASA Error -306: Deadlock detected
at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2538)
at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1922)
at com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:201)
at com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:182)
at com.sybase.jdbc2.jdbc.SybStatement.updateLoop(SybStatement.java:1611)
at com.sybase.jdbc2.jdbc.SybStatement.executeUpdate(SybStatement.java:1594)
at com.sybase.jdbc2.jdbc.SybPreparedStatement.executeUpdate(SybPreparedStatement.java:89)
at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:227)
at org.jboss.mq.pm.jdbc2.PersistenceManager.remove(PersistenceManager.java:1124)
... 14 more