MySQL试图通过在非索引列上使用条件执行Delete语句时锁定整个表来防止什么现象 [英] What Phenomena does MySQL try to prevent by locking the whole table upon executing Delete statement with the condition on a non-indexed column
问题描述
使用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仅锁定找到的索引记录，而不锁定其前的空白.
For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.
对于其他搜索条件，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 ?
推荐答案
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语句时锁定整个表来防止什么现象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
