Rithish,
- Original Message -
From: Rithish Saralaya [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, March 20, 2006 10:02 AM
Subject: deadlock - further information
--=_NextPart_000_0059_01C64C23.16088020
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit
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?
in 4.1.xx, you can use the my.cnf option innodb_locks_unsafe_for_binlog to
make InnoDB to use a consistent read in the SELECT tables in CREATE ...
SELECT. Read the caveats about the my.cnf option, though.
This is explained at:
http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html
I noticed that this behavior is broken in 5.0. I filed the bug report
http://bugs.mysql.com/bug.php?id=18350 about this. Thank you for bringing
this up.
Environment -
OS : RHEL 3
DB : MySQL 4.1.11 using INNoDB.
Regards,
Rithish.
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]