deadlock - further information
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. Thanks. However, whatever is written in http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html on innodb_locks_unsafe_for_binlog was enough to scare me to not use it in my my.cnf :o) Also I believe this variable is available as of MySQL 4.1.4, whereas ours in 4.1.11. Upgradation is not a viable option for us right now. 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. No problemos.. :o) 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 Regards, Rithish.
Re: deadlock - further information
Rithish Saralaya wrote: snip 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. Thanks. However, whatever is written in http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html on innodb_locks_unsafe_for_binlog was enough to scare me to not use it in my my.cnf :o) Also I believe this variable is available as of MySQL 4.1.4, whereas ours in 4.1.11. Upgradation is not a viable option for us right now. As 11 4, 4.1.11 is newer than 4.1.4, so this variable is available to you. 4.1.11 is almost a year old. Have you read the list of bugs fixed since then? http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: deadlock - further information
As 11 4, 4.1.11 is newer than 4.1.4, so this variable is available to you. Gawwddd... I really made an ass of myself that time... :o) Thanks for pointing it out though. 4.1.11 is almost a year old. Have you read the list of bugs fixed since then? http://dev.mysql.com/doc/refman/4.1/en/news-4-1-x.html Thanks for this too. However, db upgradation is not a viable option for us right now. However, we are sure to do that in the future. Regards, Rithish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deadlock - further information
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.
Re: deadlock - further information
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]