为什么谓词锁不能通过显式锁定查询语法获取

Question

大多数关系型数据库管理系统允许在选择的行上获取共享或独占锁。例如，PostgreSQL有这样一种语法：

SELECT * 
FROM post 
WHERE id=10 
FOR SHARE;

使用FOR SHARE，即使在READ_COMMITTED隔离级别下，我们也可以获取共享锁，并且可以防止非重读现象，而不必实际使用REPEATABLE_READ事务隔离。

但是为了防止幽灵读取，SERIALIZABLE是唯一的方法。为什么没有明确的锁定语法来获得谓词锁呢？

据我所知，在Oracle、SQL Server、MySQL或PostgreSQL中都没有看到这样的结构。

- Vlad Mihalcea

---

FOR SHARE 获取读锁，而 FOR UPDATE 则获取写锁。当使用 SERIALIZABLE 时，数据库会在检索到的所有行上获取 SHARED 锁（以防止模糊读取），还会获取范围/谓词锁（以防止幻读）。 - Vlad Mihalcea

为什么你想在每个查询中强制执行事务隔离级别，而不是只使用事务隔离级别呢？在Oracle中，您可以始终使用闪回查询到SCN来避免幻读（如果将事务隔离级别设置为可序列化，则Oracle在幕后执行此操作）。这不需要任何类型的锁定或阻止其他会话。但它不会具有相同的行为，即对于非可串行修改抛出ORA-08177：无法为此事务序列化访问错误。 - Justin Cave

鉴于您列出的每个数据库可能会有不同的答案，我预计这个问题要么是“过于宽泛”，要么是“主观意见为主”。 - Justin Cave

隔离级别是在连接级别设置的，从该特定连接开始的所有事务都将继承它。我考虑了更精细的控制方式，在较低的隔离级别（READ_COMMITTED）下使用显式锁定来防止一些异常情况。 - Vlad Mihalcea

它可能会被标记为不符合SO规则，但我认为原因是普遍的，与冲突解决检测机制有关。 - Vlad Mihalcea

Answer 1

在PostgreSQL中，Serializable隔离级别基于所谓的Serializable Snapshot Isolation，它不是用于实际锁定，而是用于监视可能创建序列化异常的条件的谓词锁。这种机制仅适用于Serializable级别；无法在较低级别使用谓词锁。

但是，为了防止幻读，在PostgreSQL中实际上只需要Repeatable Read隔离级别（尽管SQL标准对隔离级别的规定可能不同）。有关详细信息，请参见文档。

至于Oracle，它根本没有谓词锁。其Serializable隔离级别使用快照隔离（与PostgreSQL中的Repeatable Read相同），可防止幻读，但允许其他序列化异常。

我没有关于SQL Server和MySQL的信息。

- Egor Rogov

---

我进行了点踩，因为这并没有真正回答问题。问题是为什么PostgreSQL没有通过SQL语法提供对谓词锁的显式支持。例如，调用select count(*) from cars where color = 'blue' for update可以创建一种“谓词锁”，它会阻止尝试添加、删除和更新返回集合中的行（这类似于SERIALIZABLE模式中谓词锁的作用）。 - mljrg

它已经为具体行执行了这个操作，但是它不会在“任何匹配color ='blue'的东西上创建锁，即使在初始查询时它不存在或不可见。”那将是方便的，但是其他DBMS的经验表明它容易死锁并且难以实现良好的性能，因此在实践中，Pg提供了SERIALIZABLE隔离作为替代方案。您可以使用咨询锁定或使用哨兵行、唯一约束和行锁定来模拟谓词锁的应用程序级逻辑。 - Craig Ringer

Answer 2

标准未指定谓词锁，也未规定必须使用谓词锁来实现SERIALIZABLE。它只指定了SERIALIZABLE必须防止的异常情况......而大多数DBMS实际上并没有完全遵守这一点。

在PostgreSQL的情况下，没有显式的谓词锁定语法，因为没有谓词锁。PostgreSQL对于SERIALIZABLE使用更像乐观锁的东西，其中它跟踪事务间的依赖关系，并在检测到循环依赖关系时中止。这不符合锁的语义，并且明确执行可能没有太大用处。

- Craig Ringer

---

在弱于SERIALIZABLE的隔离级别中，它会非常有用。请参见我在Egor答案中添加的评论。 - mljrg