4

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

2 Answers 2

7

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.

5
  • 1
    You are terribly wrong. Write lock makes all table access restricted to session holding he lock. If you hold write lock for a long time - all applications using the table will hang until lock is released. Also there is no need to get a write lock for flushing. Data in table do not change during flush 'cause of read lock.
    – DukeLion
    May 31, 2012 at 11:28
  • That's what you get for answering on a phone and not double-checking your work. I'll correct it.
    – Ladadadada
    May 31, 2012 at 11:40
  • @DukeLion I think hitler was "terribly wrong", misreading the docs is probably just "a bit wrong", or even if you were harsh, "completely wrong" but to the degree of causing or likely to cause terror; sinister or shocking or extremely, as in a crime, would be a little strong.
    – Tom
    May 31, 2012 at 12:26
  • Well may be I've exagerrated a bit :) If you are using locks, such misunderstandings leads to very obfuscated bugs.
    – DukeLion
    Jun 1, 2012 at 3:59
  • 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 ......
    – arn
    Jun 2, 2012 at 5:18
4

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .