Hello.
Have you been at: http://dev.mysql.com/doc/mysql/en/innodb-locks-set.html In my opinion, the deadlock could appear in your case, according to that page. Because both REPLACE and INSERT could put next-key locks. And DELETE generally set record locks on every index record that is scanned in the processing of the SQL query. Brady Brown <[EMAIL PROTECTED]> wrote: > 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 > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
