JBoss MQ deadlocks using JBoss 4.0.2 + MySQL 4.1.14 InnoDB
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
