MySQL - FLUSH TABLES WITH READ LOCK

By issuing this command, it is possible to obtain a global lock.

mysql> FLUSH TABLES WITH READ LOCK ;

This command can be maintained consistent data at the time the instruction is executed.


Follow the example.

First, Execute query (flush with read lock).


session1> flush tables with read lock ; Query OK, 0 rows affected (0.00 sec)

And then execute insert query  during flush command executing.


session2>insert into inno_xxx values (1,' xxx') ; -- Waiting... session2 is blocked.

In this situation, execute "show processlist;" on session3.

session3> show full processlist ;
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
| Id   | User | Host      | db      | Command | Time | State                        | Info                                    |
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
| 7220 | root | localhost | baktest | Query   |    2 | Waiting for global read lock | insert into inno_xxx values (1,'xxx') |
| 7224 | root | localhost | NULL    | Query   |    0 | init                         | show full processlist                   |
| 7237 | root | localhost | NULL    | Sleep   |    2 |                              | NULL                                    |
+------+------+-----------+---------+---------+------+------------------------------+-----------------------------------------+
3 rows in set (0.00 sec)

session2 ( thread id 7220 ) state is waiting for global read lock.


The lock is release when executing unlock command on session1.

session1> UNLOCK TABLE ;