MySQL table locking: holder reads and writes, other sessions only read?

Question

Is it possible to lock a table such that the holder can read and write, and other sessions can only read?

The documentation seems to suggestion that a read lock allows everyone to only read, and a write lock allows only the holder to read and write and other sessions have no access. Seems like having the holder able to read and write and other sessions only able to read would be a pretty frequently needed behavior -- perhaps the most frequently needed behavior.

Maybe the performance hit in implementing this scenario would be too high?

Answer 1

Take a look at LOCK IN SHARE MODE.

This will let you set non blocking read locks.

But remember, this can lead to deadlocks! Make sure you are okay with processes having out of date information.

Answer 2

You may find that the InnoDB engine does what you need by default: writes do not block reads. You need to be careful with the transaction isolation level so that writes are available when you want them.

Answer 3

There is SELECT ... FOR UPDATE, which will lock the rows for other callers that do SELECT ... FOR UPDATE, but will not lock it for anyone doing just SELECT. UPDATEs will wait for the lock, as well.

This is useful when you want to fetch a value and then push an update back without anyone changing the value and you not noticing. Be careful, adding too much of those will get you into a deadlock.

Answer 4

There are many correct words in existing answers, but no one seems to have given a clear answer. I will try.

As you have already seen in documentation on LOCK TABLES, it can not be used for the purpose, since for the READ lock:

The session that holds the lock can read the table (but not write it).

and for the WRITE lock:

Only the session that holds the lock can access the table. No other session can access it until the lock is released.

That is the effect can hardly be achievable with an arbitrary engine table, but it can be achived with a transactional engine, that is InnoDB.

Let's think about what means that a single session keeps a constant write lock on a table and other tables can read data from the table in terms of transactions. That means that we have an open long living transaction (let it be W transaction) which locks a table for modifications and other transactions (in other sessions) can read data that is already modified, but not yet committed. In terms of isolation levels, that means that we should set up the default isolation level to READ-UNCOMMITTED, so that we would not have to change the isolation level for each new session:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

But our transaction W, should use a stronger isolation level, otherwise we can not apply any locking to our table. READ-COMMITTED is not strong enough, but REPEATABLE-READ is exactly what we want. That is befor starting a W transaction we should set the transaction level for the current session:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Now, how to lock the whole table. Let's create a table:

CREATE TABLE t (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  val VARCHAR(45) NOT NULL,
  PRIMARY KEY (id)
) ENGINE = InnoDB;

LOCK IN SHARE MODE is not what we want:

If any of these rows [that are read] were changed by another transaction that has not yet committed, your query waits until that transaction ends and then uses the latest values.

LOCK FOR UPDATE seems to do what we need:

SELECT ... FOR UPDATE locks the rows and any associated index entries.

Now all we need is to lock the rows. The simplest thing we can to is to lock the primary key. COUNT(*) does a full index scan for InnoDB (since InnoDB does not know that exact row count).

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT COUNT(*) FROM t FOR UPDATE;
INSERT INTO t VALUES (NULL, '');

Now you can open other sessions and try to read the data from the table and try to add or modify the existing data from those sessions.

The problem is though, that you should commit the modifications in W, and as soon as you commit the transaction, the lock is released and all waiting inserts or updates are applied as well, even if you commit it with:

 COMMIT AND CHAIN; SELECT COUNT(*) FROM ti FOR UPDATE;

The moral of the story is that it is much easier to have two MySQL accounts: a) writing account which has INSERT, UPDATE and DELETE GRANT permissions, and b) reading account which has not.