[jboss-jira] [JBoss JIRA] Created: (JBMESSAGING-1226) Deadlock occurs when deleting messages when using MySQL for persistence.

Tue Jan 29 17:13:59 EST 2008

Deadlock occurs when deleting messages when using MySQL for persistence.
------------------------------------------------------------------------

                 Key: JBMESSAGING-1226
                 URL: http://jira.jboss.com/jira/browse/JBMESSAGING-1226
             Project: JBoss Messaging
          Issue Type: Bug
    Affects Versions: 1.4.0.SP3
            Reporter: Phillip Thurmond
         Assigned To: Tim Fox
            Priority: Minor

This could possibly be a JDBC bug, but I thought it might be related to the way JBM is executing the SQL statements.

I noticed this error when I upgraded the mysql jdbc driver from 3 to 5.  The datasource remains the same.  I never saw the deadlock with the older jdbc driver, but it could possibly affect other DB's as well.  Please note that the transaction DOES succeed after retry, however, these are occurring very often according to the logs.

Innotop (mysql monitoring tool) output for the deadlock:

Deadlock Transactions __________________________________________________________________________________________
CXN        ID   Timestring           User  Host    Victim  Time   Undo  LStrcts  Query Text
cluster08  355  2008-01-29 17:02:09  jms   perf01  No      00:00     3        4  DELETE FROM JBM_MSG_REF WHERE MESSAGE_ID=3032 AND CHANNEL_ID=82 AND STATE='C'
cluster08  364  2008-01-29 17:02:09  jms   perf01  Yes     00:00     0        3  DELETE FROM JBM_MSG WHERE MESSAGE_ID = 3032 AND NOT EXISTS (SELECT * FROM JBM_MSG_REF WHERE JBM_MSG_REF.MESSAGE_ID = 3032)

You can see that it looks like the DELETE_MESSAGE query was executed before the DELETE_MESSAGE_REF query finished.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://jira.jboss.com/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira