2 Replies Latest reply on Jul 19, 2006 4:15 AM by J Jasper

    Deadlock detected when using JBOSSMQ with Sybase Anywhere

    J Jasper Newbie

      Using 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