MySQL Bugs: #29678: Table lock wait timeout in all case

Bug #29678	Table lock wait timeout in all case
Submitted:	10 Jul 2007 6:12	Modified:	2 Feb 2013 14:23
Reporter:	Yuan WANG	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S4 (Feature request)
Version:	5.0.44	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	lock

Description:
Although MySQL has the option table_lock_wait_timeout, it works only for connections with open cursors, which is not the common case. In common case, if someone lock the table for a long time (e.g. doing ALTER TABLE of a large table), other sessions will be blocked infinitely if they want to modified the same table. In production system, this means ALTER TABLE will block the whole application eventually, which is unacceptable.

So MySQL should make table_lock_wait_timeout works for all case.

How to repeat:
Connection 1:
mysql> lock table t read;
Connection 2:
mysql> insert into t values(1);

Connection 2 will be blocked infinitely until connection 1 release the lock.

Suggested fix:
In wait_for_lock(), always use pthread_cond_timedwait instead of pthread_cond_wait.

This seems to be implemented by looking at the code for wait_for_lock (which calls my_cond_timedwait which in turn calls the pthread_cond_timedwait), however, the default value for lock_wait_timeout is 31536000 , so it will wait for a very long time (a year) before timing out.

lock_wait_timeout solves this.

Connection 1:
---------------
mysql> create table t1(a int)engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> lock table t1 read;
Query OK, 0 rows affected (0.00 sec)

Connection2:
--------------
mysql> set lock_wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_lock_wait_timeo...
http://dev.mysql.com/doc/refman/5.5/en/metadata-locking.html