From: Brady Brown (bradyflatirontek.com)
Date: Tue Jul 05 2005 - 15:47:37 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]