何时使用 SELECT ... FOR UPDATE？

Question

请帮助我理解 SELECT ... FOR UPDATE 背后的用例。

问题 1 ：以下是什么时候应该使用 SELECT ... FOR UPDATE 的一个很好的例子吗？

鉴于：

• 房间[id]
• 标签[id，名称]
• room_tags[room_id, tag_id]
  • room_id 和 tag_id 是外键

该应用程序想要列出所有房间及其标签，但需要区分没有标签的房间和已删除的房间。如果不使用 SELECT … FOR UPDATE，可能发生的情况是：

• 最初：
  • 房间包含 [id = 1]
  • 标签包含 [id = 1, name = 'cats']
  • room_tags 包含 [room_id = 1, tag_id = 1]
• 线程1： SELECT id FROM rooms;
  • returns [id = 1]
• 线程2： DELETE FROM room_tags WHERE room_id = 1;
• 线程2： DELETE FROM rooms WHERE id = 1;
• 线程 2：[提交事务]
• 线程1： SELECT tags.name FROM room_tags, tags WHERE room_tags.room_id = 1 AND tags.id = room_tags.tag_id;
  • 返回一个空列表

现在线程 1 认为房间 1 没有标签，但实际上房间已被删除。为了解决这个问题，线程 1 应该 SELECT id FROM rooms FOR UPDATE ，从而防止线程 2 从 rooms 删除，直到线程 1 完成。那是对的吗？

问题 2 ：什么时候应该使用 SERIALIZABLE 事务隔离与 READ_COMMITTED 和 SELECT ... FOR UPDATE ？

答案应该是可移植的（不是特定于数据库的）。如果这不可能，请解释原因。

原文由 Gili 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

实现房间和标签之间的一致性并确保房间在被删除后永远不会返回的唯一可移植方法是使用 SELECT FOR UPDATE 锁定它们。

然而，在某些系统中，锁定是并发控制的副作用，您无需明确指定 FOR UPDATE 即可获得相同的结果。

---

为了解决这个问题，线程 1 应该 SELECT id FROM rooms FOR UPDATE ，从而防止线程 2 从 rooms 删除，直到线程 1 完成。那是对的吗？

这取决于您的数据库系统正在使用的并发控制。

• MyISAM in MySQL （和其他几个旧系统）在查询期间确实锁定了整个表。

• 在 SQL Server , SELECT 查询在他们检查过的记录/页面/表上放置共享锁，而 DML 查询放置更新锁或降级为共享锁）。独占锁与共享锁不兼容，因此 SELECT 或 DELETE 查询将锁定直到另一个会话提交。

• In databases which use MVCC (like Oracle , PostgreSQL , MySQL with InnoDB ), a DML 查询创建记录的副本（以一种或另一种方式），通常读者不会阻止作者，反之亦然。 For these databases, a SELECT FOR UPDATE would come handy: it would lock either SELECT or the DELETE query until another session commits, just as SQL Server 确实。

什么时候应该使用 REPEATABLE_READ 事务隔离与 READ_COMMITTED 和 SELECT ... FOR UPDATE ？

一般 REPEATABLE READ 不禁止虚行（行在另一个事务中出现或消失，而不是被修改）

• 在 Oracle 及更早的 PostgreSQL 版本中， REPEATABLE READ 实际上是 SERIALIZABLE 的同义词。基本上，这意味着事务在开始后看不到所做的更改。因此，在此设置中，最后一个 Thread 1 查询将返回房间，就好像它从未被删除一样（这可能是您想要的，也可能不是您想要的）。如果您不想在房间被删除后显示房间，您应该使用 SELECT FOR UPDATE 锁定行

• In InnoDB , REPEATABLE READ and SERIALIZABLE are different things: readers in SERIALIZABLE mode set next-key locks on the records they evaluate, effectively防止它们同时 DML 。因此，您不需要 SELECT FOR UPDATE 在可序列化模式下，但在 REPEATABLE READ 或 READ COMMITED 中确实需要它们。

请注意，隔离模式的标准确实规定您在查询中看不到某些怪癖，但没有定义如何（使用锁定或 MVCC 或其他方式）。

当我说“您不需要 SELECT FOR UPDATE ”时，我真的应该添加“因为某些数据库引擎实现的副作用”。

原文由 Quassnoi 发布，翻译遵循 CC BY-SA 3.0 许可协议