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
• Status: Closed
• Category: MySQL Server: InnoDB storage engine
• Severity: S4 (Feature request)
• Version: 5.0.54a
• OS: Any
• Assigned to: Marko Mäkelä
• CPU Architecture: Any

[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] Valeriy Kravchuk

Thank you for a reasonable feature request.

[30 May 2008 14:50] MySQL Verification Team

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:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-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:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:39] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 7:06] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-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:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-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:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-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:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[2 Aug 2011 2:22] MySQL Verification Team

See http://bugs.mysql.com/bug.php?id=20885.