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

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.1.CR1, 1.4.0.SP3.CP02 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.