Bug #54200 Deadlock between FLUSH TABLES WITH READ LOCK and DML in 5.5.4
Submitted: 3 Jun 2010 12:13 Modified: 24 Jun 2010 11:08
Reporter: Alexey Stroganov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.4 OS:Any
Assigned to: CPU Architecture:Any

[3 Jun 2010 12:13] Alexey Stroganov
Description:
Execution of FLUSH TABLES WITH READ LOCK statement while server is executing DML statement cause deadlock.

I've observed this issue while running sysbench OLTP_RW workload with 4 threads and execute FLUSH TABLES WITH READ LOCK stmt in mysql client.

+-----+------+-----------------+--------+---------+------+---------------------------------+----------------------------------+
| Id  | User | Host            | db     | Command | Time | State                           | Info                             |
+-----+------+-----------------+--------+---------+------+---------------------------------+----------------------------------+
| 14  | root | localhost:47929 | sbtest | Execute | 801  | Waiting for table               | SELECT c from sbtest where id=?  |
| 15  | root | localhost:47930 | sbtest | Execute | 801  | Waiting for table               | SELECT c from sbtest where id=?  |
| 16  | root | localhost:47931 | sbtest | Execute | 801  | Waiting for table               | SELECT c from sbtest where id=?  |
| 17  | root | localhost:47932 | sbtest | Execute | 801  | Waiting for release of readlock | UPDATE sbtest set c=? where id=? |
| 113 | root | localhost       | mysql  | Query   | 801  | Flushing tables                 | FLUSH TABLES WITH READ LOCK      |
| 286 | root | localhost       |        | Query   | 0    |                                 | show processlist                 |
+-----+------+-----------------+--------+---------+------+---------------------------------+----------------------------------+

Please note that this issue is 5.5.4 specific only. All works as expected for 5.5.3 and mysql-trunk. In 5.5.4 some work was done to relax LOCK_open mutex that in result may cause such side effect.

Related issue - BUG#46673.

How to repeat:
Run sysbench/OLTP_RW workload and execute FLUSH TABLES WITH READ LOCK stmt  in parallel against 5.5.4 binary.
[24 Jun 2010 9:19] Konstantin Osipov
We did not push the offending patch into 5.5.5.
Could you please re-verify the bug?
We believe the bug is not present in 5.5.5.
[24 Jun 2010 11:08] Alexey Stroganov
I've just rechecked issue with the latest trunk and trunk-runtime and didn't observe deadlocks. So we can assume that this issue was resolved during development cycle of 5.5.5.