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.