How to imply the WRITE lock on all tables in MySql?

How to imply the WRITE lock on all tables in MySql ? I tried the following but it gives error.

FLUSH TABLES mytest.mytable1 WITH  LOCK;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'WITH  LOCK' at line 1

What are you trying to do here? Locks in MySQL can be a little confusing if you make assumptions about what you think they do.

  • A READ lock allows the session with the lock to read and other sessions to read but not write.
  • A WRITE lock allows the session with the lock to read and write and other sessions no access.

The FLUSH TABLES syntax doesn't allow you to specify a list of tables as well as acquiring a lock. FLUSH TABLES WITH READ LOCK locks all tables. FLUSH TABLES <tables> will flush those tables. There is some implicit locking involved in flushing tables as new writes cannot go to a table while it is being flushed but this implicit lock is released as soon as the flush completes.

If you want to lock specific tables with a write lock, LOCK TABLES <table> WRITE is what you want. You can supply a comma-delimited list of tables here. If you want to lock all tables you will need to supply the entire list.


I would like to conclude in simple statement- FLUSH TABLES WITH READ LOCK; will allow all session to READ operation and will not allow to do any data modification for any session. The LOCK TABLE tblname WRITE; Will lock the table and it will allow to do read or data modification for the issued session but will not for other sessions. Thanks ......