SELECT FOR UPDATE在MySQL中保存整个表，而不是逐行 [英] SELECT FOR UPDATE holding entire table in MySQL rather than row by row

问题描述

我将有多个客户端将数据输入数据库，并且我必须确保交易不会混合在一起.

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

我在文档中读到START TRANSACTION和SELECT ... FOR UPDATE锁定它读取的每一行:

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

SELECT ... FOR UPDATE读取最新的可用数据，并在读取的每一行上设置排他锁.因此，它设置了与行中搜索的SQL UPDATE相同的锁.

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.

请参见 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;

但是在第一个客户端完全完成之前，我无法从表中进行任何操作.为什么会这样呢?文档指出应该逐行锁定，尤其是由于我LIMIT 8.

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.

谢谢.

推荐答案

InnoDB表的默认隔离级别是可重复读取的.当此隔离级别处于活动状态时，我们将得到以下行为(引用来自: https://dev.mysql.com/doc/refman/5.5/en/set-transaction.html ):

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 UPDATE或LOCK IN SHARE MODE进行选择)， UPDATE和DELETE语句的锁定取决于是否 语句使用具有唯一搜索条件的唯一索引，或者 范围类型搜索条件.对于具有唯一搜索的唯一索引 条件，InnoDB仅锁定找到的索引记录，而不锁定间隙 在它之前.对于其他搜索条件，InnoDB锁定索引范围 使用间隙锁或next-key(间隙加索引记录)锁扫描到 阻止其他会话插入该范围所覆盖的空白.

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.

换句话说:您可以在SELECT的WHERE条件下尝试使用主键吗?因此，例如，代替:

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;

尝试:

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

如果id是主键.任何其他具有唯一索引的列也可以使用.在WHERE子句中使用非唯一列时，InnoDB将锁定一定范围的行.

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.

这篇关于SELECT FOR UPDATE在MySQL中保存整个表，而不是逐行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！