MySQL Bugs: #57170: NOWAIT clause can be ignored (expected an error)

Bug #57170	NOWAIT clause can be ignored (expected an error)
Submitted:	1 Oct 2010 13:32	Modified:	4 Apr 2011 19:11
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	mysql-next-mr-wl3561	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If the underlying storage engine does not implement the transactional locking API, NOWAIT clause is ignored.
Otherwise, an error is produced.
This is inconsistent. Since the transactional lock is implicitly converted to a non-transactional lock when an engine does not support the API, a wait can occur, contrary to the explicit user request.

mysql> drop table t1;
Query OK, 0 rows affected (31.32 sec)

mysql> create table t1 (a int) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> lock table t1 in share mode nowait;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1696 | Converted to non-transactional lock on 't1' |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> lock table t1 in exclusive mode nowait;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1696 | Converted to non-transactional lock on 't1' |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t1 engine=innodb;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> lock table t1 in exclusive mode nowait;
ERROR 1112 (42000): Table 't1' uses an extension that doesn't exist in this MySQL version

How to repeat:
drop table if exists t1;
create table t1 (a int) engine=myisam;
lock table t1 in share mode nowait;
show warnings;
lock table t1 in exclusive mode nowait;
show warnings;
alter table t1 engine=innodb;
unlock tables;
lock table t1 in exclusive mode nowait;

Suggested fix:
This bug identifies a design omission in the storage engine API for transactional LOCK TABLE. When a storage engine reports that it doesn't support
the request, it doesn't identify which part of the request -- lock type, timeout, or NOWAIT are unsupported. Thus SQL layer treats them all the same.

If NOWAIT clause can not be handled by the engine, produce a consistent error message, that's the same for all engines, for SHARE and EXCLUSIVE lock request.

Verified on Mac OS X:

macbook-pro:next-mr-wl3561 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.99-m4-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1 (a int) engine=myisam;
Query OK, 0 rows affected (0.07 sec)

mysql> lock table t1 in share mode nowait;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1696 | Converted to non-transactional lock on 't1' |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> lock table t1 in exclusive mode nowait;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1696 | Converted to non-transactional lock on 't1' |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table t1 engine=innodb;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> lock table t1 in exclusive mode nowait;
ERROR 1112 (42000): Table 't1' uses an extension that doesn't exist in this MySQL version

Since was bug was reported against feature tree for WL#3561 "Transactional LOCK TABLES" and this worklog got cancelled I am closing this bug as "Won't fix".