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
Triage: Needs Triage: D2 (Serious)

[4 Feb 2014 9:02] Sadao Hiratsuka
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.
[4 Feb 2014 9:03] Sadao Hiratsuka
test program to reproduce problem

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

[4 Feb 2014 20:38] Sveta Smirnova
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.
[6 Feb 2014 0:50] Sadao Hiratsuka
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 ?
[6 Feb 2014 14:05] Ståle Deraas
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...
[6 Feb 2014 17:01] Sadao Hiratsuka
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.