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 ......