Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers

up vote 0 down vote accepted

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

share|improve this answer
add comment

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.

share|improve this answer
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 '12 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 '12 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 H May 31 '12 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 '12 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 '12 at 5:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.