SQL试图通过在非索引列上使用条件执行Delete语句时锁定整个

SQL试图通过在非索引列上使用条件执行Delete语句时锁定整个

本文介绍了MySQL试图通过在非索引列上使用条件执行Delete语句时锁定整个表来防止什么现象的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

使用MySQL隔离级别的可重复读取".

Using the MySQL isolation level of Repeatable Read.

给表test包含未索引列quantity:

id    |     quantity
--------------------
1     |      10
2     |      20
3     |      30

Tx1执行1st,请注意,它尚未提交,这意味着尚未获得所有获得的锁.

Tx1 executes 1st, note it is not committed yet, meaning that all the acquired locks are not released yet.

Tx1:

START TRANSACTION;
DELETE FROM test WHERE quantity=10;

现在执行Tx2

Tx2:

START TRANSACTION;
INSERT INTO test(quantity) VALUES (40);
COMMIT;

对于Tx2,我得到以下结果:

For Tx2 I get the following result:

Lock wait timeout exceeded; try restarting transaction

我了解到,由于未对quantity列进行索引,因此delete语句会进行全表扫描,锁定所有行(与where条件是否匹配无关),并且还会应用间隙在聚集索引中的最后一个索引记录的前后都锁定,导致表被完全阻塞,因此tx2的insert语句无法获取要插入的行的锁定.

I understand that, as the quantity column is not indexed, the delete statement does a full table scan, locks all the rows( doesn't matter the where condition matches or not) and also applies gap locks before every and after the last index record in a Clustered Index resulting in a fully blocked table hence the insert statement from tx2 cannot acquire a lock for a row to be inserted.

从MySQL 手册 (对于可重复读取"隔离级别):

From MySQL manual(for Repeatable Read isolation level):

对于其他搜索条件,InnoDB使用间隙锁或next-key锁来锁定扫描的索引范围,以阻止其他会话插入该范围所覆盖的间隙(在我的情况下使用).

For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range (this is used in my case).

考虑到在任何给定的隔离级别中应用锁定是为了防止phenomenas我有点困惑,在这种情况下阻塞整个表的原因是什么,我的意思是哪种phenomena在这种情况下可以阻止整个表被阻止?

Taking into account that the locking in any given isolation level is applied for preventing the phenomenas I'm a little bit confused what is the reason to block the whole table in this case, I mean what kind of phenomena is prevented with blocking the whole table in this case ?

推荐答案

默认情况下,InnoDB在可重复读取隔离级别,这意味着您可以获得元组和范围的可重复读取.

By default, InnoDB uses consistent snapshots in Repeatable Read isolation level, meaning that you get repeatable reads for both tuples and ranges.

即使SQL标准指出幻影读取也会被Serializable阻止,并且可重复读也许不能阻止它,如果较低的隔离级别提供的保证比标准所考虑的要好,这也不是问题.

Even if the SQL standard says that Phantom Reads are prevented by Serializable, and that Repeatable Read might not prevent it, it is not a problem if a lower isolation level provides better guarantees than what the standard has it mind.

有关间隙锁定如何工作的更多详细信息,请查看这篇文章.

For more details about how gap locking works, check out this article.

这篇关于MySQL试图通过在非索引列上使用条件执行Delete语句时锁定整个表来防止什么现象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-11 21:30