mysql 逐行更新,SELECT FOR UPDATE在MySQL中保存整个表，而不是逐行

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.

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.

解决方案

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.