MySQL: FLUSH TABLES WITH READ LOCK

[hieuhtr]

What is FLUSH TABLES WITH READ LOCK in MySQL?

Command:

mysql> FLUSH TABLES WITH READ LOCK;

What exactly does command do?

1. Set the global read lock - after this step, insert/update/delete/replace/alter statements cannot run
2. Close open tables - this step will block until all statements started previously have stopped
3. Set a flag to block commits

It means:

• Closes all open tables
• Locks all tables for all databases with a global read lock
• MySQL is in READ only mode, cannot WRITE anything, except one case (see below)
• insert/update/delete/replace/alter statements cannot run

Important:

• The session that holds the lock can read the table (but not write it)
• It prevents other sessions from modifying tables during periods
• If you don't keep this session open to keep Locking
  • Other sessions can write into tables
  • If you run FLUSH TABLES WITH READ LOCK for preparing mysqldump, you must keep this session, and run mysqldump in another session.
  • Because If you don't keep, the binlog and the position of binlog may be changed during this session

Note:

• FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into the log tables

How to unlock and keep MySQL back to normal?

• Use UNLOCK TABLES to release the lock

[zhjwpku]

hi, I have one question,

FLUSH TABLES WITH READ LOCK does not prevent the server from inserting rows into the log tables

what are the log tables? binary log?

[chandankumar4]

Hi I'm trying to take a snapshot so for this i run the command FLUSH TABLES WITH READ LOCK before taking snapshot but the issue is if i close the session where i run the flush command and start a new session then i'm able to write data in mysql DB, Is there any way like, after closing the session the flush command will persist and write is blocked until we run unlock tables cc @hieuhtr