MySQL Bugs: #56764: if clause causes unnecessary locking

Bug #56764	if clause causes unnecessary locking
Submitted:	14 Sep 2010 7:42	Modified:	16 Sep 2010 7:56
Reporter:	Michael Skulsky	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.0.91, 5.1.39, 5.1.50, 5.5.7	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Retrieving a row inside an if test clause causes unnecessary locking.

How to repeat:
PREPARATION:

mysql> CREATE TABLE `ttt` (
    ->   `id` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into ttt select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> delimiter $
create procedure test_lock1() 
begin
    if (select id from ttt where id=1) is not null  then
        select 1;
    end if;
end;
$
create procedure test_lock2() 
begin
   declare lock_check int;
   
   select id into lock_check from ttt where id=1;

   if lock_check is not null then
        select 1;
   end if;
end;
$

mysql> delimiter ;
mysql>

WINDOW 1:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from ttt where id=1 for update;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

mysql>

WINDOW 2:

mysql> call test_lock2();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call test_lock1();

The call HANGS until the lock in window 1 is released. Same result can be obtained using exists() -- and even worse, any call to exists() from inside a stored procedure takes an unnecessary lock.

Looks like a duplicate of bug #46947. So, please, check if the same problem happens with 5.1.50.

Bug #46947 is a 5.1 bug (introduced in 5.1.33). This behavior is reproducible also on 5.0. Besides, there is no subquery here, but a stored procedure is required.

You are right, that older bug is different. Verified with 5.1.50:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.50-community MySQL Community Server (GPL)

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> call test_lock2();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.09 sec)

Query OK, 0 rows affected (0.09 sec)

mysql> call test_lock1();
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

Take a savepoint before the 'if' statement, and rollback to the savepoint after the condition is evaluated that would release the row lock.
I don't see how this can be fixed in the foreseeable future.

Can you provide exact conditions, which lead to these locks, and the type of locks being held? I have thousands lines of code in stored procedures, and now I fear any of these lines may be acquiring some weird lock.

Please verify against 5.5.
In 5.5 the locks should be taken only in strong isolation modes.

Same with 5.5.7:

openxs@ubuntu:/home2/openxs/dbs/5.5$ bin/mysql --no-defaults -uroot --socket=/tmp/vk.sock 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 2
Server version: 5.5.7-m3-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> call test_lock2();
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> call test_lock1();
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

Default isolation level was used.