SHOW INNODB STATUS indicates these two queries are deadlocking:
(1) REPLACE INTO TMP_pixel_xfer SELECT * FROM user_question q INNER JOIN user_session s USING(user_session_id) WHERE user_question_id BETWEEN '27853011' AND '27891923' ORDER BY s.user_id
(2) DELETE t from TMP_user_client_report t LEFT JOIN user_question u USING(user_session_id,question_id) WHERE u.user_id IS NULL
I execute 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED' before executing each respective query.
Note that the two queries both join to user_question, but neither query changes any data in user_question. Only data in their respective TMP_ tables is modified. But apparently there are row-locks set on user_question anyway. Why is this? And shouldn't each query be using it's own fresh copy of user_question since the isolation level is set to READ COMMITTED beforehand? Is there another strategy I can use to avoid the deadlock? Any insight is greatly appreciated.
One last note that may or may not be relevant. I began to experience this deadlock only after I upgraded from mysql 4.0 to 4.1.
Thanks, Brady -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
