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.
