Bug #20885 innodb_lock_wait_timeout at a session level
Submitted: 6 Jul 2006 9:12 Modified: 2 Aug 2011 2:09
Reporter: Sadao Hiratsuka (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S4 (Feature request)
Version:5.0.22 OS:Linux (Linux)
Assigned to: Heikki Tuuri CPU Architecture:Any

[6 Jul 2006 9:12] Sadao Hiratsuka
Description:
Hi,

I want to set innodb_lock_wait_timeout at session level.
Now, when I set innodb_lock_wait_timeout, I get following error.

| scott:scott> SET SESSION innodb_lock_wait_timeout = 1;
| ERROR 1193 (HY000): Unknown system variable 'innodb_lock_wait_timeout'

If InnoDB can set this parameter at session level,
we may consider that InnoDB supports 'SELECT ... FOR UPDATE NOWAIT' statement.
So it is very useful for to migrate from Oracle to MySQL.

Sincerely,

How to repeat:
N/A

Suggested fix:
InnoDB can set innodb_lock_wait_timeout at session level.
[8 Jul 2006 9:10] Valeriy Kravchuk
Thank you for a reasonable feature request.
[25 Oct 2006 20:17] Daniel Liljebaldh
Best solution is 'SELECT ... FOR UPDATE NOWAIT'
[31 Jul 2007 6:05] Zigmund Bulinsh
Yes I also will be verry happy if there was this function like in ORACLE..
Verry verry usefull. Because using GET_LOCK and RELEASE_LOCK is not always verry comfortant..
[11 Dec 2008 14:18] Mattias Jonsson
Seems to be a duplicate of bug#36285, which will be included in the InnoDB plugin.
[21 May 2009 20:06] Larry Parr
This would be very helpfull - either to be able to set the innodb_wait_timeout
to zero - or to be able to use the NOWAIT qualifier like Oracle does.

Our application was originally designed for NOWAIT on a select for update
instruction.  It works well in Oracle - But in MySql every once in a while
we run into issues because it will wait for the record based on the innodb_wait_timeout variable.

Ideally - it would be nice to be able to set this variable to zero (system wide)
If the variable is set to a value other than zero then wait the specified amount of time.  If the SQL Select Statement has a NOWAIT clause at the end - then regardless of the variable value - don't wait.
[2 Aug 2011 2:03] Sadao Hiratsuka
Bug #36285 is the same incident, and is resolved by InnoDB Plugin.
http://bugs.mysql.com/bug.php?id=36285

So please close this Bug #20885.
Thanks.
[2 Aug 2011 2:09] MySQL Verification Team
Thank you for the feedback.