… the ALTER in your test case is waiting on InnoDB row locks by setting innodb_lock_wait_timeout to 1 and then issuing the ALTER… update; — session 2 set innodb_lock_wait_timeout=1; alter table t1 engine=innodb; And the alter will timeout after approximately a second with the following error: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
Post: MySQL 5.6 Compatible Percona Toolkit 2.2 Released
March 25, 2013 By 2 Comments
… its tries and wait time between tries for all operations are configurable. Also, we removed –lock-wait-timeout which set innodb_lock_wait_timeout because that now conflicts, or is at least confused with, lock_wait_timeout (introduced in MySQL 5.5) for metadata locks…
Post: MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server
February 28, 2013 By 5 Comments
… discovered something about lock_wait_timeout that shocked me. From the MySQL 5.5 docs for lock_wait_timeout: This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… | Waiting for table metadata lock | ALTER TABLE test_2 DROP COLUMN c | | 7 | msandbox | localhost | test | Query | 195 | Waiting for table metadata lock | select… with unintended consequences. A workaround here would be to set lock_wait_timeout variable to a very low value and then execute the… immediately (however due to a different reason): session2 > set session lock_wait_timeout=1;CREATE TABLE t1(i int(11) not null auto…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction transaction2 > INSERT INTO t VALUES(29); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction transaction2 > INSERT INTO t VALUES(23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction…
Post: ACTIVE with Locks – Now thats a problem !
June 2, 2011 By 2 Comments
… indicate the problem waiting to happen, this is when you have long enough ACTIVE transactions which have some locks, like in this… other query will try to lock the same rows it will have to wait… possibly until innodb_lock_wait_timeout elapses and when it completes… they are recycled it might be good idea to set wait_timeout to some reasonable value, say 60 seconds. In this case…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
….commanual licenseblogpercona.commanual local_infileblogpercona.commanual local_infileblogpercona.commanual lock_wait_timeoutblogpercona.commanual locked_in_memoryblogpercona.commanual logblogpercona.commanual log_binblogpercona.commanual log…_zoneblogpercona.commanual table_definition_cacheblogpercona.commanual table_lock_wait_timeoutblogpercona.commanual Table_locks_immediateblogpercona.commanual Table_locks_waitedblogpercona.commanual table_open_cacheblogpercona.commanual table…
Post: Test Drive of Solid
September 22, 2006 By 5 Comments
… expect deadlock here – but not – just waiting… In 30 sec Session1: ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction So as we… or giving error to the user you’re forced to wait. Setting lock wait timeout to some small value is not optimal solution ether… transactions to be terminated even though they just had normal lock wait not a deadlock. Test 2: SELECT FOR UPDATE: mysql> begin…
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
… (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:38:03>insert into t values(23); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction root@localhost:test 14:44:37>insert into t values(31); ERROR 1205 (HY000): Lock wait timeout exceeded…
Comment: MySQL 5.5 lock_wait_timeout: patience is a virtue, and a locked server
March 1, 2013 By 5 Comments
… a 1-hour lock wait timeout can be a bug. lock_wait_timeout affects must affect all locks – in particular, it affects GRL and MyISAM locks. The fact that InnoDB doesn’t obey the setting of lock_wait_timeout is a bug in InnnoDB, which is a world in itself, not in lock_wait_timeout. It’s both…
