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

问题描述

我将有多个客户将数据输入数据库，我必须确保事务不会混合.

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

<块引用>

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

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

所以我登录了一个客户端并输入了以下语句:

开始交易；SELECT * FROM productMacAddress WHERE status='free' limit 8 FOR UPDATE;

<块引用>

在此暂停第二个客户端条目....

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

在另一个客户端，我输入:

开始交易；SELECT * FROM productMacAddress WHERE status='free' limit 4 FOR UPDATE;更新 productMacAddress SET status='testing2' WHERE status='free' LIMIT 4;犯罪;

但在第一个客户端完全完成之前，我无法从表中 SELECT 任何内容.为什么会这样?文档说明它应该逐行锁定，特别是因为我 LIMIT 8.

提前谢谢你.

解决方案

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

<块引用>

对于锁定读取(SELECT with FOR UPDATE 或 LOCK IN SHARE MODE)，UPDATE 和 DELETE 语句，锁定取决于是否语句使用具有唯一搜索条件的唯一索引，或范围类型的搜索条件.对于具有唯一搜索的唯一索引条件，InnoDB 只锁定找到的索引记录，而不是间隙在它之前.对于其他搜索条件，InnoDB 锁定索引范围扫描，使用间隙锁或下一个键(间隙加索引记录)锁阻止其他会话插入到范围覆盖的间隙中.

换句话说:您可以尝试在 SELECT 的 WHERE 条件中使用主键吗?因此，例如，而不是:

开始交易；SELECT * FROM productMacAddress WHERE status='free' limit 8 FOR UPDATE;

试试:

开始交易；SELECT * FROM productMacAddress WHERE id=10 FOR UPDATE;

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

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.

解决方案

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.

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