SELECT FOR UPDATE holding entire table in MySQL rather than row by row

Question

I will have multiple clients entering data into a database and I must ensure that the transactions do not get intermingled.

I read in the documentation that START TRANSACTION and SELECT ... FOR UPDATE locks each row that it reads:

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

See https://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

So I logged in one client and typed these statements:

START TRANSACTION;
SELECT * FROM productMacAddress WHERE status='free' limit 8 FOR UPDATE;

Pause here for second client entry....

UPDATE productMacAddress SET status='testing1' WHERE status='free' LIMIT 8;
COMMIT;

And in another client, I type:

START TRANSACTION;
SELECT * FROM productMacAddress WHERE status='free' limit 4 FOR UPDATE;
UPDATE productMacAddress SET status='testing2' WHERE status='free' LIMIT 4;
COMMIT;

But I am not able to SELECT anything from the table until the first client is completely done. Why is this happening? The documentation states it should lock row by row, especially since I LIMIT 8.

Thank you in advance.

Answer 1

The default isolation level for InnoDB tables is repeatable read. When this isolation level is active we get the following behavior (quote from: https://dev.mysql.com/doc/refman/5.5/en/set-transaction.html):

For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key (gap plus index-record) locks to block insertions by other sessions into the gaps covered by the range.

In other words: could you try using the primary key in the WHERE condition of the SELECT? So for instance instead of:

START TRANSACTION;
SELECT * FROM productMacAddress WHERE status='free' limit 8 FOR UPDATE;

Try:

START TRANSACTION;
SELECT * FROM productMacAddress WHERE id=10 FOR UPDATE;

in case id is the primary key. Any other column with a unique index on it would work too. When using non-unique columns in your WHERE clause InnoDB will lock a range of rows.