Gabriele,
http://dev.mysql.com/doc/mysql/en/Innodb_deadlocks.html " SET AUTOCOMMIT=0; LOCK TABLES t1 WRITE, t2 READ, ...; [do something with tables t1 and t2 here]; COMMIT; UNLOCK TABLES; "
"
In applications using AUTOCOMMIT=1 and MySQL's LOCK TABLES command, InnoDB's internal table locks that were added in 4.0.20 can cause deadlocks. Starting from 4.0.22, you can set innodb_table_locks=0 in `my.cnf' to fall back to the old behavior and remove the problem.
"
http://dev.mysql.com/doc/mysql/en/News-4.0.22.html
"
InnoDB: New mysqld option --innodb-table-locks and session variable innodb_table_locks (on by default). In applications using AUTOCOMMIT=1 and MySQL's LOCK TABLES command, InnoDB's internal table locks that were added in 4.0.20 can cause deadlocks. You can set innodb_table_locks=0 in `my.cnf' to remove that problem. See section 15.17 Restrictions on InnoDB Tables. (Bug #3299, Bug #5998)
"
Starting from 4.0.20, the correct way to use LOCK TABLES is to set AUTOCOMMIT=0. Then you can release the MySQL table lock with LOCK TABLES, and the InnoDB table lock with COMMIT at the same time.
You can also fall back to the old behavior by setting the session variable innodb_table_locks to 0.
We changed the table locking behavior in 4.0.20 because the old behavior was even more illogical: you could LOCK a table even though some other user had row locks on it. What we did not foresee was that this would easily generate deadlocks if users had AUTOCOMMIT=1.
In transactional databases all locks are released at a COMMIT. It does not make sense to run at AUTOCOMMIT=1, if you want to set table locks. MySQL's LOCK TABLES is unusual: the lock survives over transaction commits. That is what causes the deadlock problems. In a future version of MySQL we will introduce
LOCK TABLES TRANSACTIONAL ...
that no longer takes a MySQL table lock at all, only an InnoDB table lock. This new command will behave like in a similar table lock command in normal transactional databases, and the old LOCK TABLES will become deprecated for pure InnoDB transactions.
We will update the MySQL manual to include the above text. LOCK TABLES has caused a lot of confusion lately.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
----- Original Message ----- From: ""Mr. Gabriele Somthing"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Tuesday, November 23, 2004 3:32 AM
Subject: "Lock wait timeout exceeded" Errors
I believe I have read or skimmed all of the posts to this group with "Lock", "wait" and "timeout" in their body over the past year (172) with no answers in sight (if I missed a post, please let me know).
I am unable to figure out what is causing this problem that is 100% reproducible on a 4.0.20-standard server. It works as follows:
1) Thread A locks InnoDB table Foo for writing SUCCESSFULLY. 2) Thread B locks InnoDB table Foo for writing, but has to wait for Thread A. 3) Thread A performs a query (I used "SELECT MAX(id) FROM foo;" for testing). 4) Thread A terminates the query with a "Lock wait timeout exceeded" error. 5) Thread B goes on its merry way.
NOTES:
1) NO OTHER THREADS are locking any of the tables in question or even have access to the databases in question
2) The THREAD WITH THE LOCK is the one that HAS THE ERROR
3) No other sequence of events seems to cause the problem
4) The lock on "foo" is not release until after Thread A either dies, disconnects or issues a LOCK/UNLOCK command
5) During the time that Thread A is performing the query, Thread B has a state of "Sending data" while Thread B is "Locked".
This only happens on the ISP's MySQL server (4.0.20-standard), not on our development server (4.1.7-standard). We do not have control over the system variables (except for runtime variables, of course). It only happens with InnoDB tables, not with MyISAM tables. I have included a perl script that causes the bug 100% of the time below. I can reproduce the bug using two instances of the mysql command-line utility and it manifests using PHP as well, so it's not a driver issue.
The innodb_lock_wait_timeout is set to the default 50 seconds, but this should NEVER be reached, as a query like "SELECT MAX(id) FROM foo;" should take less than 0.005 seconds.
Questions: Why is the query slowed when the process that is making it is the one that HAS the lock? And why does it happen only when another thread is trying to get a lock?
What can I do to get this working properly? I'm at my wit's end.
Since this is a large commercial ISP, I am unable to get them to upgrade MySQL because we have encoutered a bug.
Help, comments, suggestions, ridicule (accompanied by a remedy) actively solicited.
Thank you!
-Gabriele
--- START ENVIRONMENT INFO --- MySQL Server Version: 4.0.20-standard OS: Linux (Kernel 2.4.21-15.0.4.ELsmp) --- END ENVIRONMENT INFO ---
Perl Script that can reliably reproduce error: --- START SCRIPT --- #!/usr/bin/perl -w use DBI; use POSIX ":sys_wait_h"; use Time::HiRes qw( gettimeofday usleep tv_interval ); use strict; $|=1; # Catch dead forks. sub REAPER { my $c; while (($c=waitpid(-1,WNOHANG))>0){ print "Proc $c died with \$? = '$?'\n"; } $SIG{CHLD}=\&REAPER; } $SIG{CHLD}=\&REAPER; my($u,$p) = (@ARGV);
my $proc=0; my $step=0;
my $dbh=DBI->connect("DBI:mysql:database=test;host=localhost", $u,$p, { PrintError=>1,PrintWarn=>1 });
db_do("DROP TABLE IF EXISTS foo;"); db_do("CREATE TABLE foo ( id int ) TYPE=InnoDB ;");
my $child_pid = fork(); die "Can't fork!" unless defined $child_pid;
$dbh=DBI->connect("DBI:mysql:database=test;host=localhost", $u,$p, { PrintError=>1,PrintWarn=>1 });
$step=0;
if($child_pid) { $proc=1; db_do("LOCK TABLES foo WRITE;"); usleep 5_000_000; db_do("SELECT MAX(id) FROM foo;"); usleep 5_000_000; db_do("UNLOCK TABLES;"); } else { $proc=2; usleep 1_500_000; db_do("LOCK TABLES foo WRITE;"); db_do("UNLOCK TABLES;"); } sub db_do { my $sql = shift; $step++; my $t0=[gettimeofday]; $dbh->do($sql); print "Process $proc, step $step: finish: ", $sql, sprintf("%7.4f secs\n", tv_interval($t0)); }
1; __END__ --- END SCRIPT ---
Script Output on 4.0.20 server: --- START OUTPUT --- Process 0, step 1: finish: DROP TABLE IF EXISTS foo; 0.1641 secs Process 0, step 2: finish: CREATE TABLE foo ( id int ) TYPE=InnoDB ; 0.0655 secs Process 1, step 1: finish: LOCK TABLES foo WRITE; 0.0010 secs DBD::mysql::db do failed: Lock wait timeout exceeded; Try restarting transaction at /home/divineme/test_locks line 53. Process 1, step 2: finish: SELECT MAX(id) FROM foo;51.6475 secs Process 1, step 3: finish: UNLOCK TABLES; 0.0003 secs Process 2, step 1: finish: LOCK TABLES foo WRITE;60.1304 secs Process 2, step 2: finish: UNLOCK TABLES; 0.0032 secs --- END OUTPUT ---
Script Ouput on 4.1.7 server: --- START OUTPUT --- Process 0, step 1: finish: DROP TABLE IF EXISTS foo; 0.0063 secs Process 0, step 2: finish: CREATE TABLE foo ( id int ) TYPE=InnoDB ; 0.0247 secs Process 1, step 1: finish: LOCK TABLES foo WRITE; 0.0003 secs Process 1, step 2: finish: SELECT MAX(id) FROM foo; 0.0003 secs Process 1, step 3: finish: UNLOCK TABLES; 0.0002 secs Process 2, step 1: finish: LOCK TABLES foo WRITE; 8.5087 secs Process 2, step 2: finish: UNLOCK TABLES; 0.0014 secs --- END OUTPUT ---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
