何时使用SELECT ... FOR UPDATE？

Question

何时使用SELECT ... FOR UPDATE？

mysqlsqlsql-servertransactionsselect-for-update

170

请帮我理解使用 SELECT ... FOR UPDATE 的用例。

问题1：以下是否是使用 SELECT ... FOR UPDATE 的好例子？

给定:

房间 [id] 标签 [id, name] 房间标签 [room_id, tag_id]

房间 id 和标签 id 是外键

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

最初: 房间包含 [id = 1] 标签包含 [id = 1, name = 'cats'] 房间标签包含 [room_id = 1, tag_id = 1]

线程1: SELECT id FROM rooms; 返回 [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事务隔离级别，而不是使用带有SELECT ... FOR UPDATE的READ_COMMITTED？

需要提供可移植的答案（非特定于某个数据库）。如果不可能，请解释原因。

- Gili

2

你使用哪个关系型数据库管理系统？ - Quassnoi

4

如问题底部所提到的，我正在寻找一个便携式（非特定于数据库）的解决方案。 - Gili

2

“REPEATABLE_READ”和“READ_COMMITTED”选项是否是可移植的选项？我所得到的唯一结果是针对MSSQL服务器的。 - Billy ONeal

4

请注意，隔离模式保证您不会看到它们不允许的怪癖，但不保证您能看到它们允许的怪癖。这意味着设置例如“读取已提交”模式并不能定义您是否实际上能看到由另一个事务提交的记录：它仅确保您永远不会看到未提交的记录。 - Quassnoi

5

对于 rooms 执行的 select ... for update 语句仍然允许删除 room_tags，因为它们是独立的表格。您的意思是询问 for update 子句是否会防止从 rooms 删除内容？ - Chris Saxon

显示剩余3条评论

3个回答

49

简短回答：

Q1：是。

Q2：使用哪一个都无所谓。

长篇回答：

select ... for update会选择某些行并将它们锁定，就像这些行已经被当前事务更新了一样（或者就像标识符更新已经执行了）。这允许您在当前事务中再次更新它们，然后提交，而另一个事务则无法以任何方式修改这些行。

从另一个角度来看，就好像原子地执行了以下两个语句：

select * from my_table where my_condition;

update my_table set my_column = my_column where my_condition;

由于被 my_condition 影响的行已被锁定，因此任何其他事务都无法以任何方式修改它们，因此，在这里，事务隔离级别没有任何影响。

还要注意，事务隔离级别与锁定无关：设置不同的隔离级别不能让您绕过锁定并在不同的事务中更新由您的事务锁定的行。

事务隔离级别确保数据在事务进行时处于一致状态（在不同级别上有所区别）。

- Colin 't Hart

1

我认为“什么事务隔离级别确保[...]完成事务后数据一致性。”不正确地暗示了隔离级别不会影响事务期间发生的情况。我建议修改这个部分，提供更多关于它们如何影响您在事务期间看到（或看不到）的内容的细节。 - Gili

1

我发现你的帖子比Quassnoi更好地回答了我的具体问题，但我很感激他提供的所有参考资料。我将接受最好地结合两者（具体答案在上方，支持参考资料在下方）的答案。 - Gili

锁定和隔离是相互交织的复杂问题。那么有没有相关书籍可以获取这方面的知识呢？ - Chao

3

什么是SELECT FOR UPDATE？

SELECT FOR UPDATE 是一个在事务工作负载环境下非常有用的SQL命令。它允许您“锁定” SELECT 查询返回的行，直到包含该查询的整个事务被提交为止。试图访问这些行的其他事务将被放置在基于时间的队列中等待，并在第一个事务完成后按照时间顺序执行。

BEGIN;
SELECT * FROM kv WHERE k = 1 FOR UPDATE;
UPDATE kv SET v = v + 5 WHERE k = 1;
COMMIT

- Mahdi Abedi

那么，当执行更新操作时，SELECT语句有什么好处呢？毕竟更新操作会锁定行。 - Morey

那么，当执行更新操作时，SELECT语句有什么好处呢？毕竟更新操作会锁定行。 - undefined

@Morey 这个答案非常简洁地解释了这个问题。基本上，你在完成操作之前阻止其他会话读取该行。这意味着在选择该行后，没有其他查询可以阻止你更新该行，这有时可能导致死锁的发生。 - Steen Schütt

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Quassnoi · Accepted Answer

实现房间和标签之间一致性、确保在删除后不返回已删除的房间唯一可行方式是使用 SELECT FOR UPDATE 锁定它们。然而，在某些系统中，锁定是并发控制的副作用，因此您可以在不显式指定 FOR UPDATE 的情况下实现相同的结果。

解决这个问题，线程1应该 SELECT id FROM rooms FOR UPDATE，从而防止线程2从rooms表中删除数据，直到线程1完成。这取决于数据库系统正在使用的并发控制方式。在MySQL的MyISAM（和其他一些旧系统）中，锁定整个表查询期间; 在SQL Server中，SELECT查询对其所检查的记录/页面/表放置共享锁，而DML查询放置更新锁。在使用MVCC的数据库（如Oracle、PostgreSQL、InnoDB的MySQL），DML查询会创建记录的一个副本，并且通常读者不会阻塞写入者。在这些情况下，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。

请注意，隔离模式标准规定您不会在查询中看到某些怪异行为，但未定义如何（使用锁定还是MVCC或其他方式）。

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