MySQL Bugs: #32005: no lock wait timeout if a table is locked by "Lock tables ... "

Bug #32005	no lock wait timeout if a table is locked by "Lock tables ... "
Submitted:	31 Oct 2007 20:09	Modified:	2 Nov 2007 14:17
Reporter:	Armin Haaf	Email Updates:
Status:	Open	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S4 (Feature request)
Version:	5.0.45	OS:	Linux
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
if a table is locked by "Lock tables ..." (e.g. by mysqldump) innodb did not timeout another transaction waiting for a write lock. 

How to repeat:
1.transaction:  LOCK TABLES tablexyz;
2.transaction:  UPDATE TABLE tablexyz SET mycolumn=1;

2. transaction block endless till 1.transaction unlocks the table

Suggested fix:
don't use lock tables for innodb tables

This is a feature request for the MySQL server. Table locks are primarily handled by MySQL, not InnoDB. There is no lock wait timeout in MySQL's table locks.

If this is not a bug, then you have to update your documentation:

http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html

-- from http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html
innodb_lock_wait_timeout

The timeout in seconds an InnoDB transaction may wait for a lock before being rolled back. InnoDB automatically detects transaction deadlocks in its own lock table and rolls back the transaction. InnoDB notices locks set using the LOCK TABLES statement. The default is 50 seconds. 
--

Armin,

you are right. The docs are unclear.

Paul,

please change the paragraph to:

"
The timeout in seconds an InnoDB transaction may wait for a lock before being rolled back.
InnoDB automatically detects transaction deadlocks inside its own lock table and rolls back
the transaction. A lock wait for a MySQL table lock does not happen inside InnoDB, and this timeout does not apply to that wait. The default is
50 seconds. 
"

Best regards,

Heikki

ok this comment is clearer. 

I changed this issue into a feature request. But I would like to point out that this is an important feature request for the usage of innodb in a production environment (we had a serious incident because of this missing feature)

I have updated the documentation per Heikki's wording.

Resetting the report to Server: InnoDB category because the report is a feature request that applies to InnoDB.