If a client session is currently holding a read lock (to allow concurrent reads while already preventing writes), and then acquires a write lock on the same table(s), does MySQL guarantee that the lock is continuous?

The Manual states:

If a session issues a LOCK TABLES statement to acquire a lock while already holding locks, its existing locks are released implicitly before the new locks are granted.

And as far as I can see, it states nothing explicitly about "upgrading" a lock from read to write. As in

mysql> LOCK TABLE `awesome` READ;
mysql> SELECT ... FROM `awesome` ...
mysql> LOCK TABLE `awesome` WRITE;  # lock continuous, or dropped+reacquired?
mysql> INSERT INTO `awesome` ...

If I'm being paranoid about the verbiage in the documentation, I have to assume that there will be a brief moment between the two lock states where my session temporarily holds no lock at all, during which a concurrent session could potentially modify the table, bringing it into a state inconsistent with what I've seen in the SELECT phase.

I cannot rely on exceptions thrown by duplicate key violations, as there is no useful unique key (and it would probably quadruple the size of the table if MySQL even supported it). For the same reason, transactions wouldn't really help me, but I cannot use them anyways, as the table in questions is using the MyISAM engine for performance reasons. Locking really seems like my best bet, only the documentation seems a little conspicuous on the transitioning details.

And insights on how MySQL internally handles transitioning from read lock to write lock if the set of tables is the same?

The big-wooden-hammer-method would of course be to issue a single write lock up front. But that would force all selects to wait, obviously. OTOH, as I understand it, an infinite number of sessions can read-lock the same table concurrently (implicitly or explicitly) with no implications on select performance at all. The only thing the read lock does is preventing updates from hitting the table, which is exactly what I need in the first stage.

  • Why would a primary key "quadruple" the size of the table?
    – user1822
    May 19, 2014 at 11:46
  • It's not a primary key. PK is autoincrement. Secondary key is an integer hash. The thing that's missing is a hypothetical unique index on a smalltext column, and I'd expect that to be huge no matter the storage engine, but particularly for InnoDB. This is an implementation detail of one solution to the age-old "how do I get auto-increment ids of all this stuff I've inserted in the last two days" problem. Typical table size is ~520M rows, ~75GB (though that's already converted to a zlib compressed TokuDB table, which is done after the table contents are final -- it's faster that way).
    – Rolf NB
    May 20, 2014 at 11:37

1 Answer 1


Indeed, the part of the manual you quoted does just what it says:

-- Connection #1
LOCK TABLE mytable READ; -- lock is acquired

-- Connection #2
LOCK TABLE mytable WRITE; -- execution is suspended, lock is NOT acquired

-- Connection #1
LOCK TABLE mytable WRITE; -- execution is suspended
-- READ lock was released, allowing Connection #2 to acquire the WRITE lock
-- Connection #2 resumes

Solution: do switch to InnoDB and use transactions. Performance of the InnoDB engine improved a lot in the recent versions.

-- Connection #1
SELECT * FROM mytable LOCK IN SHARE MODE; -- lock is acquired

-- Connection #2
SELECT * FROM mytable FOR UPDATE; -- execution is suspended, lock is NOT acquired

-- Connection #1
-- deadlock is detected, Connection #2 is rolled back
  • That's ... a nice and simple test. Thanks! Never occured to me that I could find out this way. InnoDB is definitely not an option. I've tried and retried many times. TokuDB isn't either btw. Building the tables in MyISAM format and then switching engines in post is still several times faster with our data volumes.
    – Rolf NB
    May 20, 2014 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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