何时使用SELECT…FOR UPDATE？

暨弘毅

2023-03-14

问题内容：

请帮助我了解后面的用例SELECT ... FOR UPDATE。

问题1 ：以下是何时SELECT ... FOR UPDATE应使用的好例子吗？

鉴于：

该应用程序希望列出所有房间及其标签，但是需要区分没有标签的房间和已删除的房间。如果不使用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.tag_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？

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

问题答案：

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

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

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

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

MyISAM在MySQL（和其他几个老系统）则锁定整个表进行查询的时间。
在中SQL Server，SELECT查询将共享锁放置在他们检查过的记录/页面/表上，而DML查询则将更新锁放置（随后将其升级为互斥或降级为共享锁）。互斥锁与共享锁不兼容，因此SELECT或DELETE查询将一直锁定，直到提交另一个会话为止。
在数据库在使用MVCC（如Oracle，PostgreSQL，MySQL用InnoDB），一个DML查询创建记录的副本（以一种或另一种方式），一般读者不会阻止作家，反之亦然。对于这些数据库，a SELECT FOR UPDATE会派上用场：它将锁定一个SELECT或一个DELETE查询，直到提交另一个会话为止，就像这样SQL Server做一样。

当一个人应该使用REPEATABLE_READ的事务隔离与READ_COMMITTED用SELECT ... FOR UPDATE？

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

在Oracle及早期PostgreSQL版本中，REPEATABLE READ实际上是的同义词SERIALIZABLE。基本上，这意味着事务在启动后看不到更改。因此，在此设置中，最后一个Thread 1查询将返回会议室，就好像它从未被删除过（可能是您想要的，也可能不是您想要的）。如果您不想在删除房间后显示房间，则应使用SELECT FOR UPDATE
在InnoDB，REPEATABLE READ并且SERIALIZABLE是不同的东西：读者SERIALIZABLE模式设置上，他们评估记录next-key锁定，有效防止并发DML他们。因此，您不需要SELECT FOR UPDATE处于可序列化模式，而是需要在REPEATABLE READ或中使用它们READ COMMITED。

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

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