List:General Discussion« Previous MessageNext Message »
From:Rithish Saralaya Date:March 20 2006 8:05am
Subject:deadlock - further information
View as plain text  
Hello.

We have a web application for our intranet, which uses MySQL for db
management. The application has a way of logging query errors that are
generated in the system.

We get a mysql error recorded as - "Deadlock found when trying to get lock;
try restarting transaction" when one of the files tries to create a
temporary table. The SQL query for it is as below -
CREATE TEMPORARY TABLE TBL_B AS SELECT TA.FLD_MSG_ID FROM TBL_A TA WHERE
{... conditions for selection ...}

I looked through the MySQL lists, but I could not get info as to why a
deadlock is created in this scenario. I also do not know how to replicate
the scenario, as this error is not logged always. Is there a particular case
when this happens?

I looked through
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html and it says
"SELECT ... FROM is a consistent read, reading a snapshot of the database
and setting no locks ..."  So why is a lock being taken in the first place?

I am at a loss. Can someone shed light on this, or point me to some
resource?

Environment -
OS : RHEL 3
DB : MySQL 4.1.11 using INNoDB.

Regards,
Rithish.

Thread
deadlock - further informationRithish Saralaya20 Mar
Re: deadlock - further informationHeikki Tuuri20 Mar