Does MySQL Guarantee Atomic Upgrade from Read Lock to Write Lock?

lockingmyisamMySQL

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` ...
mysql> UNLOCK TABLES;

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.

Best Answer

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
BEGIN;
SELECT * FROM mytable LOCK IN SHARE MODE; -- lock is acquired

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

-- Connection #1
SELECT * FROM mytable FOR UPDATE;
-- deadlock is detected, Connection #2 is rolled back
Related Question