MySQL Bugs: #71580: FLUSH TABLES WITH READ LOCK hangs when many clients run SELECT FOR UPDATE twice

Bug #71580	FLUSH TABLES WITH READ LOCK hangs when many clients run SELECT FOR UPDATE twice
Submitted:	4 Feb 2014 9:02	Modified:	6 Feb 2014 17:01
Reporter:	Sadao Hiratsuka	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.6.16, 5.5.36	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	flush tables with read lock, select for update

Description:
FLUSH TABLES WITH READ LOCK hangs when
there are 2+ threads executing the following transaction.

BEGIN;
SELECT * FROM t WHERE id = 1 FOR UPDATE;
SELECT * FROM t WHERE id = 2 FOR UPDATE;
ROLLBACK;

How to repeat:
mysql> GRANT ALL PRIVILEGES ON test.* TO test@localhost identified by 'test';
mysql> CREATE DATABASE test;
mysql> USE test
mysql> CREATE TABLE t (id INT PRIMARY KEY, data VARCHAR(10));
mysql> INSERT INTO t (id, data) VALUES (1, 'aaaaa');
mysql> INSERT INTO t (id, data) VALUES (2, 'bbbbb');

$ export CLASSPATH=/PATH/TO/Connector-J:.
$ javac ConcurrentSelectForUpdate.java
$ java ConcurrentSelectForUpdate

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SHOW PROCESSLIST;
+------+------+-----------------+------+---------+------+------------------------------+-----------------------------------------+
| Id   | User | Host            | db   | Command | Time | State                        | Info                                    |
+------+------+-----------------+------+---------+------+------------------------------+-----------------------------------------+
| 7429 | root | localhost       | test | Query   |    0 | init                         | SHOW PROCESSLIST                        |
| 7438 | test | localhost:49140 | test | Query   |   20 | statistics                   | SELECT * FROM t WHERE id = 1 FOR UPDATE |
| 7439 | test | localhost:49141 | test | Query   |   20 | statistics                   | SELECT * FROM t WHERE id = 1 FOR UPDATE |
| 7440 | test | localhost:49142 | test | Query   |   20 | statistics                   | SELECT * FROM t WHERE id = 1 FOR UPDATE |
| 7441 | test | localhost:49143 | test | Query   |   20 | statistics                   | SELECT * FROM t WHERE id = 1 FOR UPDATE |
| 7442 | test | localhost:49144 | test | Query   |   20 | Waiting for global read lock | SELECT * FROM t WHERE id = 2 FOR UPDATE |
| 7443 | test | localhost:49145 | test | Query   |   20 | statistics                   | SELECT * FROM t WHERE id = 1 FOR UPDATE |
| 7444 | test | localhost:49146 | test | Query   |   20 | statistics                   | SELECT * FROM t WHERE id = 1 FOR UPDATE |
| 7445 | test | localhost:49147 | test | Query   |   20 | statistics                   | SELECT * FROM t WHERE id = 1 FOR UPDATE |
| 7446 | root | localhost       | NULL | Query   |   20 | Waiting for global read lock | FLUSH TABLES WITH READ LOCK             |
+------+------+-----------------+------+---------+------+------------------------------+-----------------------------------------+

I think 'Waiting for global read lock' is usual behavior, but 'statistics' is not.
FLUSH TABLES WITH READ LOCK does not hang if there is only 1 thread executing SELECT FOR UPDATE.
FLUSH TABLES WITH READ LOCK does not hang If there is only 1 SELECT FOR UPDATE in the transacftion.

Suggested fix:
Sorry, I have no idea.

test program to reproduce problem

Attachment: ConcurrentSelectForUpdate.java (application/octet-stream, text), 1.83 KiB.

Thank you for the report.

Verified as described. Actually SELECT queries fail after innodb_lock_wait_timeout ends, then FLUSH TABLES succeeds, but it is still strange why queries can not be finished in turn.

Here is a minimized test case.

session1> BEGIN;
session1> SELECT * FROM t WHERE id = 1 FOR UPDATE;

session2> BEGIN;
session2> SELECT * FROM t WHERE id = 1 FOR UPDATE; -- session 2 waits for session 1

session3> FLUSH TABLES WITH READ LOCK; -- session 3 waits for session 2

session1> SELECT * FROM t WHERE id = 2 FOR UPDATE -- session 1 waits for session 3

It seems deadlock.
Is it a bug ? or an expected behavior ?

Posted by developer:
 
This can be resolved through the lock timeout in InnoDB/SQL-layer: http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_lock_wait_time...

I think session 1 and session 2 should not fail by timeout.

When session 3 takes a global read lock normally,
other sessions wait until the lock is released, and do not fail by timeout.

In this 3-way deadlock situation,
if innodb_lock_wait_timeout is set to large value, session 1 & 2 wait infinitely.
Or if innodb_lock_wait_timeout is set to small value, session 1 & 2 fail by timeout.