MySQL Bugs: #41949: table_lock_wait_timeout can't work cause dead lock

Bug #41949	table_lock_wait_timeout can't work cause dead lock
Submitted:	8 Jan 2009 9:08	Modified:	13 Mar 2009 18:03
Reporter:	li david	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.30, 5.0, 5.1, 6.0 bzr	OS:	Linux (RedHat as 5)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
set table_lock_wait_timeout=20;
in mysql client window as the following:
lock table testmy read;

in another mysql client window as the following :
insert into testmy values(1);
if table_lock_wait_timeout work well, it will terminate in 20s.
in fact ,it keep hang until unlock tables.

How to repeat:
in mysql client window as the following:
lock table testmy read;

in another mysql client window as the following :
insert into testmy values(1);

Why do you think it is a bug? SET is interpreted as SET SESSION by default. Please, check http://dev.mysql.com/doc/refman/5.1/en/set-option.html.

In session A ,do the following:
  mysql> create table testmy(id int);
  Query OK, 0 rows affected (0.02 sec)
  mysql> set global table_lock_wait_timeout=20;
  Query OK, 0 rows affected (0.00 sec)
  mysql> lock table testmy read;
  Query OK, 0 rows affected (0.00 sec)

In another session B , do the following:
  mysql> show global variables like 'table_lock%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| table_lock_wait_timeout | 20    | 
+-------------------------+-------+
1 row in set (0.00 sec)
 mysql> insert into testmy values(1);
 this will hang 

In the third session C.Do the following:
 mysql> show processlist                 
    -> ;
+--------+--------------+---------------------+----------+---------+------------+-----------------------------------------------------------------------+------------------------------+
| Id     | User         | Host                | db       | Command | Time       | State                                                                 | Info                         |
+--------+--------------+---------------------+----------+---------+------------+-----------------------------------------------------------------------+------------------------------+
| 159002 | system user  |                     | NULL     | Connect |      20318 | Waiting for master to send event                                      | NULL                         | 
| 159003 | system user  |                     | NULL     | Connect | 4294967288 | Has read all relay log; waiting for the slave I/O thread to update it | NULL                         | 
| 159998 | passportuser | 10.1.43.93:1824     | passport | Sleep   |        752 |                                                                       | NULL                         | 
| 160005 | root         | localhost           | mysql    | Query   |          0 | NULL                                                                  | show processlist             | 
| 160015 | root         | localhost           | mysql    | Query   |        292 | Locked                                                                | insert into testmy values(1) | 
 
sesion B keep locked after 292s. 
if table_lock_wait_timeout is enable function. sessionB will terminate it after 20s.
But it keep hang until sessionA exit.
So we think that table_lock_wait_timeout can't work as expected.

Thank you for the report.

Verified as described.

We decided internally this is documentation problem: table_lock_wait_timeout does not affect situation described.

which cases will enable it?

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

It has been determined that this variable is currently unused (its action depends on presence of an unimplemented feature). I'll update the documentation to reflect that it's unused.