2 Replies Latest reply on Oct 26, 2005 7:53 AM by deas0815

    JBoss MQ deadlocks using JBoss 4.0.2 + MySQL 4.1.14 InnoDB

    deas0815 Newbie

      Hallo,

      im experiencing Database deadlocks using JBoss 4.0.2 and MySQL 4.1.14
      using InnoDB tables (as I considered JMS TX-safety important).

      For the JMS-service, I deployed
      docs/examples/jms/mysql-jdbc2-service.xml as shipped with
      the jboss 4.0.2 distribution.

      JBoss chokes

      Caused by: org.jboss.mq.SpyXAException: - nested throwable: (org.jboss.mq.SpyTransactionRolledBackException: Transaction was rolled back.; - nested throwable: (org.jboss.mq.SpyJMSException: Could not remove message: 56 msg=55 soft STORED PERSISTENT queue=QUEUE.testQueue priority=4 lateClone=false hashCode=9710658; - nested throwable: (java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction)))
      at org.jboss.mq.SpyXAResource.commit(SpyXAResource.java:87)
      at org.jboss.tm.TransactionImpl$Resource.commit(TransactionImpl.java:2142)
      at org.jboss.tm.TransactionImpl.commitResources(TransactionImpl.java:1675)
      at org.jboss.tm.TransactionImpl.commit(TransactionImpl.java:312)

      while mysql has a point of view like this:

      ------------------------
      LATEST DETECTED DEADLOCK
      ------------------------
      051019 20:02:32
      *** (1) TRANSACTION:
      TRANSACTION 0 91476, ACTIVE 0 sec, process no 6297, OS thread id 9415600 fetching rows
      mysql tables in use 1, locked 1
      LOCK WAIT 5 lock struct(s), heap size 320, undo log entries 2
      MySQL thread id 11, query id 7272 localhost.localdomain 127.0.0.1 jboss_jms updating
      DELETE FROM JMS_TRANSACTIONS WHERE TXID = 33
      *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 20 page no 3 n bits 160 index `GEN_CLUST_INDEX` of table `jboss_jms/JMS_TRANSACTIONS` trx id 0 91476 lock_mode X waiting
      Record lock, heap no 61 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0
      0: len 6; hex 000000001120; asc ;; 1: len 6; hex 000000016553; asc eS;; 2: len 7; hex 800000002d0084; asc - ;; 3: len 4; hex 80000022; asc ";;

      *** (2) TRANSACTION:
      TRANSACTION 0 91475, ACTIVE 0 sec, process no 6297, OS thread id 8838064 starting index read, thread declared inside InnoDB 500
      mysql tables in use 1, locked 1
      4 lock struct(s), heap size 320, undo log entries 1
      MySQL thread id 9, query id 7275 localhost.localdomain 127.0.0.1 jboss_jms Updating
      UPDATE JMS_MESSAGES SET TXID=34, TXOP='D' WHERE MESSAGEID=29 AND DESTINATION='QUEUE.testQueue'
      *** (2) HOLDS THE LOCK(S):
      RECORD LOCKS space id 20 page no 3 n bits 160 index `GEN_CLUST_INDEX` of table `jboss_jms/JMS_TRANSACTIONS` trx id 0 91475 lock_mode X locks rec but not gap
      Record lock, heap no 61 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0
      0: len 6; hex 000000001120; asc ;; 1: len 6; hex 000000016553; asc eS;; 2: len 7; hex 800000002d0084; asc - ;; 3: len 4; hex 80000022; asc ";;

      *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
      RECORD LOCKS space id 17 page no 3 n bits 128 index `PRIMARY` of table `jboss_jms/JMS_MESSAGES` trx id 0 91475 lock_mode X locks rec but not gap waiting
      Record lock, heap no 49 PHYSICAL RECORD: n_fields 7; 1-byte offs FALSE; info bits 0
      0: len 4; hex 8000001d; asc ;; 1: len 15; hex 51554555452e746573745175657565; asc QUEUE.testQueue;; 2: len 6; hex 000000016458; asc dX;; 3: len 7; hex 800000002d0084; asc - ;; 4: SQL NULL, size 4 ; 5: len 3; hex 412020; asc A ;; 6: len 30; hex aced000577a8010200097465737451756575650000000200000000000000; asc w testQueue ;...(truncated);

      *** WE ROLL BACK TRANSACTION (2)

      To be honest, I have not yet tracked all SQL issued by the conflicting
      database sessions and i don't quite understand each and every detail
      about the locks.

      Nevertheless, I would greatly appreciate any help/suggestion here.

      Thanks