… all the locks required in the beginning to prevent dead locks? So you would get a lock wait instead of a dead lock. No, that… that mean? Does the tool clean up (i.e., remove table / triggers?) About cleanup.. What about manual aborting a run. will… breaks reliably on the __tmp_x table why ? The first question sounds like a potential MySQL bug. Are you sure this is…
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
…): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:38:03>insert into t values(23); ERROR 1205 (HY000): Lock wait timeout… thread id 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1216, 1 row lock(s) MySQL thread id 31929…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…is no MYISAM like table lock priority problem with pending WRITE query blocks any READ queries to execute on the table. mysql> show …| 681 | Waiting for table flush | flush tables with read lock | 0 | 0 | 1 | | 10291 | root | localhost | dumptest | Query | 74 | Waiting for global read lock | insert …
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…1205 (HY000): Lock wait timeout exceeded; try restarting transaction transaction2 > INSERT INTO t VALUES(29); ERROR 1205 (HY000): Lock wait timeout …MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx id 72C lock mode IX RECORD LOCKS…
Comment: Innodb Performance Optimization Basics
March 28, 2012 By 70 Comments
… facing major issues with slow query output. The tables are in InnoDB with MySQL 5 and RHEL 5. Its a 32 bit… innodb_lock_wait_timeout = 50 A count command with approx 3 million records is taking too long and all my transactions are locked. select count(1),field1 from Table where REQUEST_TIME >= ’2012-03-26′ group…
Comment: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
Even killing FLUSH TABLES WITH READ LOCK won’t resolve the deadlock as of MySQL 5.5 – other queries will still be stuck in ‘Waiting for table flush’ even after the FLUSH TABLES WITH READ LOCK goes away. 
http://bugs.mysql.com/bug.php?id=44884
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…the amazing MySQL manual, especially the option and variable reference table. But …table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: How to debug long-running transactions in MySQL
March 8, 2011 By 6 Comments
… data, then other transactions could block and fail with a lock wait timeout. The problem is, it can be very difficult to… blocking others. There are some INFORMATION_SCHEMA tables that make this simple. Just query the tables, and if something is blocked, find… free)”>&2 exit 1 fi host=$(mysql -ss -e ‘SELECT p.HOST FROM information_schema.innodb_lock_waits w INNER JOIN information_schema…
Post: Table locks in SHOW INNODB STATUS
June 8, 2010 By 1 Comment
… 2 lock struct(s), heap size 368 MySQL thread id 8, query id 164 localhost root TABLE LOCK table `test/t1` trx id 0 4872 lock… be locked. Such locks are called intention lock, hence abbreviation IX = Intention eXclusive. Intention locks do not work the same way as table locks – Intention exclusive lock… worry intention table locks you may observe in SHOW INNODB STATUS output, they almost never would be cause of your lock waits or deadlocks…
Post: How to Monitor MySQL with Percona's Nagios Plugins
February 22, 2012 By 8 Comments
… is failed or overly full. Too many deadlocks. Deadlocks (and lock wait timeouts, covered in another plugin) are frequent causes of serious…’t owned by MySQL, you won’t have any problems until you try to create or drop a table, or the database…-up of connections all trying to open or close tables can indicate LOCK_open contention. Replication is delayed or stopped. Our plugins…
