Bug #57167 Misleading error for LOCK TABLE t1 IN SHARE MODE NOWAIT

• Submitted: 1 Oct 2010 13:08
• Modified: 22 Apr 2011 13:27
• Reporter: Konstantin Osipov (OCA)
• Status: Won't fix
• Category: MySQL Server: Locking
• Severity: S3 (Non-critical)
• Version: mysql-next-mr-wl3561
• OS: Any
• CPU Architecture: Any

[1 Oct 2010 13:08] Konstantin Osipov

Description:
LOCK TABLE t1 IN SHARE MODE NOWAIT produces a misleading error message.

MySQL does not support NOWAIT clause, but the message suggests something different.

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

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

mysql> lock table t1 in share mode nowait;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> -- there is no lock timeout; the clause is unsupported. See for example:
mysql> lock table t1 in exclusive mode nowait;
ERROR 1112 (42000): Table 't1' uses an extension that doesn't exist in this
MySQL version
mysql> -- (you can observe a correct error message above)
mysql> -- the message can be very misleading when no wait is supposed to
occur:
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> lock table t1 in share mode nowait;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Another way to formulate the problem is to say that the fix for Bug#46664 was incomplete.

How to repeat:
drop table t1;
create table t1 (a int) engine=innodb;
lock table t1 in share mode nowait;
lock table t1 in exclusive mode nowait;
begin;
lock table t1 in exclusive mode;
lock table t1 in share mode nowait;

Suggested fix:
Produce a consistent error message about unsupported syntax, or remove NOWAIT from the parser altogether.

[1 Oct 2010 17:30] Valeriy Kravchuk

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 7
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 t1;
Query OK, 0 rows affected (0.00 sec)

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

mysql> lock table t1 in share mode nowait;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> lock table t1 in exclusive mode nowait;
ERROR 1112 (42000): Table 't1' uses an extension that doesn't exist in this MySQL version
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> lock table t1 in share mode nowait;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

[22 Apr 2011 13:27] Dmitry Lenev

Hello!

I am closing this bug as "Won't fix" since it was reported against feature tree and corresponding WL#3561 was canceled.