何时使用 SELECT ... FOR UPDATE？

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

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

Given:

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

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

• Initially:
  • 房间包含[id = 1]
  • 标签包含[id = 1, name = 'cats']
  • room_tags 包含[room_id = 1, tag_id = 1]
• Thread 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：[提交交易]
• Thread 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 with SELECT ... FOR UPDATE?

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

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

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

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

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

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

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

• 在使用的数据库中MVCC (like Oracle, PostgreSQL, MySQL with InnoDB), a DML查询创建记录的副本（以一种或另一种方式），并且通常读取器不会阻止写入器，反之亦然。对于这些数据库，SELECT FOR UPDATE会派上用场：它会锁定SELECT or the DELETE查询直到另一个会话提交，就像SQL Server does.

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

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

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

• In InnoDB, REPEATABLE READ and SERIALIZABLE是不同的东西：读者SERIALIZABLE模式在它们评估的记录上设置下一个键锁，有效防止并发DML在他们。所以你不需要SELECT FOR UPDATE在可序列化模式下，但确实需要它们REPEATABLE READ or READ COMMITED.

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

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