Bug #36285 innodb_lock_wait_timeout is not dynamic, not per session
Submitted: 23 Apr 2008 11:46 Modified: 20 Jun 2010 17:24
Reporter: Kristian Koehntopp Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0.54a OS:Any
Assigned to: Marko Mäkelä
Triage: Triaged: D5 (Feature request)

[23 Apr 2008 11:46] Kristian Koehntopp
Description:
The variable innodb_lock_wait_timeout will limit the amount of time a connection spends waiting for a lock (e.g. an X-lock) on a row to be granted.

If the timeout happens, an error

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

which is precisely what is needed. Unfortunately this variable cannot be set dynamically, and not per session, which IS what is needed for many customers.

How to repeat:
Check that it works as advertised:
innodb_lock_wait_timeout = 3

In one connection:
create table t ( id serial, d varchar(20) not null ) engine = innodb;
insert into t (d) values ("eins"), ("zwei"), ("drei");

begin;
update t set d = "two" where id = 2;

In another connection;
begin;
update t set d = "deux" where id = 2; <- hangs for 3 seconds, then
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Try to change it with no server restart:

root on mysql.sock [innodemo]> set global innodb_lock_wait_timeout = 30;
ERROR 1193 (HY000): Unknown system variable 'innodb_lock_wait_timeout'
root on mysql.sock [innodemo]> set session innodb_lock_wait_timeout = 30;
ERROR 1193 (HY000): Unknown system variable 'innodb_lock_wait_timeout'

Suggested fix:
Make "set session innodb_lock_wait_timeout = 30" work.
[24 Apr 2008 15:19] Valerii Kravchuk
Thank you for a reasonable feature request.
[30 May 2008 14:50] Miguel Solorzano
Bug: http://bugs.mysql.com/bug.php?id=37077 has been marked as duplicate of this one.
[3 Oct 2008 10:29] Marko Mäkelä
This bug has been fixed in the upcoming release of the InnoDB plugin 1.0.2 for MySQL 5.1.
[29 May 2009 11:28] Simon Mudd
Does this mean the problem has been resolved in a specific version of 5.1? The bug report says targeted for 6.x so if you're fixing it for 5.1 that's better news. it would also be useful to know WHICH version of 5.1 it's fixed in.

Currently testing 5.1.34 so would be nice to know if this issue is resolved in that version.
[29 May 2009 11:35] Simon Mudd
Seems not:

mysql> select @@version;
+------------------------------------+
| @@version                          |
+------------------------------------+
| 5.1.34-enterprise-gpl-advanced-log | 
+------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT plugin_name,plugin_version,plugin_type FROM INFORMATION_SCHEMA.plugins WHERE plugin_name = 'InnoDB';
+-------------+----------------+----------------+
| plugin_name | plugin_version | plugin_type    |
+-------------+----------------+----------------+
| InnoDB      | 1.0            | STORAGE ENGINE | 
+-------------+----------------+----------------+
1 row in set (0.00 sec)

mysql>
[30 Mar 2010 10:48] Marko Mäkelä
MySQL 5.1 bundles InnoDB Plugin, where this bug has been fixed.
[30 Mar 2010 14:17] Paul Dubois
Reverting assignment change. It's not a docs bug, so the assignment doesn't change just because it goes to Documenting status.
[30 Mar 2010 14:19] Paul Dubois
The first version of InnoDB Plugin included with MySQL 5.1 is 1.0.4 (in MySQL 5.1.38). Since this bug is fixed in InnoDB Plugin 1.0.2, it's fixed for all versions of MySQL 5.1 in which the plugin is included.
[5 May 2010 15:08] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 14:58] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 6:10] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:39] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 7:06] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 23:57] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:16] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:03] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:43] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[2 Aug 2011 2:22] Miguel Solorzano
See http://bugs.mysql.com/bug.php?id=20885.