REPEATABLE_READ 隔离是否会阻止非范围查询的插入? [英] Does REPEATABLE_READ isolation prevent inserts for non-ranged queries?

问题描述

给定以下 SQL 伪代码:

Given the following SQL pseudo-code:

... open transaction ...
SELECT * FROM users WHERE users.id = 5
... some calculation here ...
INSERT INTO users (id) VALUES (5)
... commit transaction ...

REPEATABLE_READ 隔离是否保证防止并发连接插入Users[id=5] 在查询和插入之间，还是我需要使用 SERIALIZABLE 隔离?

Is REPEATABLE_READ isolation guaranteed to prevent concurrent connections from inserting Users[id=5] between the query and insert, or do I need to use SERIALIZABLE isolation for that?

注意:如果可能，我正在寻找与数据库无关的解决方案.如果不是，请提供来自多个数据库的经验证据，以便我们可以反对某种行业共识.

NOTE: I'm looking for a database agnostic solution if possible. If not, please provide empirical evidence from multiple databases so we can work against some sort of industry consensus.

UPDATE:原始问题包含使用范围锁的错误查询.由于此问题的目的是关注不需要范围锁定的查询，因此我更新了问题.

UPDATE: The original question contained an incorrect query that made use of range-locks. Since the purpose of this question is to focus on queries that do not require range-locks, I have updated the question.

推荐答案

没有.REPEATABLE_READ 隔离级别仅保证您在同一事务中重复 SELECT 时将看到相同的数据，这意味着 (SQL) 实现可以隐藏您执行的事务插入通过其他交易.

No. The REPEATABLE_READ isolation level only guarantees that you will see the same data if you repeat the SELECT within the same transaction, meaning that (SQL) implementations could hide from your transaction insertions performed by other transactions.

唯一能够阻止插入的隔离级别是SERIALIZABLE，因为它保证如果您有多个事务并发操作，结果就像它们以特定顺序顺序运行一样.每种情况下的具体顺序没有指定也没有报告，但可以保证存在一个会产生相同最终结果的顺序.

The only isolation level able to preclude insertions is SERIALIZABLE because it guarantees that if you have multiple transactions operating concurrently, the results will be as if they had run sequentially in certain order. The specific order in each case is not specified nor reported, but it's guaranteed that there exists one that would have produced the same final results.

问题的一个条件是与数据库无关(我假设是高级别的答案)，因此我不会提及不同的隔离级别如何在不同的数据库实现中工作或它们如何转换为锁.

One of the conditions of the question is a database-agnostic (and I assume high-level answer), so I will not mention how the different isolation levels work in different DB implementations or how they translate into locks.

这篇关于REPEATABLE_READ 隔离是否会阻止非范围查询的插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！