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

[Tim Fox (JIRA)]

    [ http://jira.jboss.com/jira/browse/JBMESSAGING-1226?page=comments#action_12397768 ] 
            
Tim Fox commented on JBMESSAGING-1226:
--------------------------------------

MySQL is prone to deadlock - often there is not much you can do about it.

We should investigate though

> 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
>             Fix For: 1.4.0.SP3.CP01, 1.4.1.beta2
>
>
> 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