MySQL 何时使用 SELECT ... FOR UPDATE？

When to use SELECT ... FOR UPDATE?

提问by Gili

Please help me understand the use-case behind SELECT ... FOR UPDATE.

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

Question 1: Is the following a good example of when SELECT ... FOR UPDATEshould be used?

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

Given:

鉴于：

• rooms[id]
• tags[id, name]
• room_tags[room_id, tag_id]
  • room_id and tag_id are foreign keys

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

The application wants to list all rooms and their tags, but needs to differentiate between rooms with no tags versus rooms that have been removed. If SELECT ... FOR UPDATE is not used, what could happen is:

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

• Initially:
  • rooms contains [id = 1]
  • tags contains [id = 1, name = 'cats']
  • room_tags contains [room_id = 1, tag_id = 1]
• Thread 1: SELECT id FROM rooms;
  • returns [id = 1]
• Thread 2: DELETE FROM room_tags WHERE room_id = 1;
• Thread 2: DELETE FROM rooms WHERE id = 1;
• Thread 2: [commits the transaction]
• Thread 1: SELECT tags.name FROM room_tags, tags WHERE room_tags.tag_id = 1 AND tags.id = room_tags.tag_id;
  • returns an empty list

• 最初：
  • 房间包含 [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;
  • 返回一个空列表

Now Thread 1 thinks that room 1 has no tags, but in reality the room has been removed. To solve this problem, Thread 1 should SELECT id FROM rooms FOR UPDATE, thereby preventing Thread 2 from deleting from roomsuntil Thread 1 is done. Is that correct?

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

Question 2: When should one use SERIALIZABLEtransaction isolation versus READ_COMMITTEDwith SELECT ... FOR UPDATE?

问题 2：什么时候应该使用SERIALIZABLE事务隔离而不是READ_COMMITTEDwith SELECT ... FOR UPDATE？

Answers are expected to be portable (not database-specific). If that's not possible, please explain why.

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

采纳答案by Quassnoi

The only portable way to achieve consistency between rooms and tags and making sure rooms are never returned after they had been deleted is locking them with SELECT FOR UPDATE.

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

However in some systems locking is a side effect of concurrency control, and you achieve the same results without specifying FOR UPDATEexplicitly.

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

---

---

To solve this problem, Thread 1 should SELECT id FROM rooms FOR UPDATE, thereby preventing Thread 2 from deleting from roomsuntil Thread 1 is done. Is that correct?

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

This depends on the concurrency control your database system is using.

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

• MyISAMin MySQL(and several other old systems) does lock the whole table for the duration of a query.

• In SQL Server, SELECTqueries place shared locks on the records / pages / tables they have examined, while DMLqueries place update locks (which later get promoted to exclusive or demoted to shared locks). Exclusive locks are incompatible with shared locks, so either SELECTor DELETEquery will lock until another session commits.

• In databases which use MVCC(like Oracle, PostgreSQL, MySQLwith InnoDB), a DMLquery creates a copy of the record (in one or another way) and generally readers do not block writers and vice versa. For these databases, a SELECT FOR UPDATEwould come handy: it would lock either SELECTor the DELETEquery until another session commits, just as SQL Serverdoes.

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

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

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

When should one use REPEATABLE_READtransaction isolation versus READ_COMMITTEDwith SELECT ... FOR UPDATE?

什么时候应该使用REPEATABLE_READ事务隔离而不是READ_COMMITTEDwith SELECT ... FOR UPDATE？

Generally, REPEATABLE READdoes not forbid phantom rows (rows that appeared or disappeared in another transaction, rather than being modified)

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

• In Oracleand earlier PostgreSQLversions, REPEATABLE READis actually a synonym for SERIALIZABLE. Basically, this means that the transaction does not see changes made after it has started. So in this setup, the last Thread 1query will return the room as if it has never been deleted (which may or may not be what you wanted). If you don't want to show the rooms after they have been deleted, you should lock the rows with SELECT FOR UPDATE

• In InnoDB, REPEATABLE READand SERIALIZABLEare different things: readers in SERIALIZABLEmode set next-key locks on the records they evaluate, effectively preventing the concurrent DMLon them. So you don't need a SELECT FOR UPDATEin serializable mode, but do need them in REPEATABLE READor READ COMMITED.

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

• 在InnoDB,REPEATABLE READ和SERIALIZABLE是不同的东西：SERIALIZABLE模式下的读者在他们评估的记录上设置下一个键锁，有效地防止DML它们的并发。因此，您不需要SELECT FOR UPDATEin 可序列化模式，但确实需要它们 inREPEATABLE READ或READ COMMITED。

Note that the standard on isolation modes does prescribe that you don't see certain quirks in your queries but does not define how (with locking or with MVCCor otherwise).

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

When I say "you don't need SELECT FOR UPDATE" I really should have added "because of side effects of certain database engine implementation".

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

回答by Colin 't Hart

Short answers:

简短的答案：

Q1: Yes.

Q1：是的。

Q2: Doesn't matter which you use.

Q2：不管你用哪个。

Long answer:

长答案：

A select ... for updatewill (as it implies) select certain rows but also lock them as if they have already been updated by the current transaction (or as if the identity update had been performed). This allows you to update them again in the current transaction and then commit, without another transaction being able to modify these rows in any way.

一select ... for update会（因为这意味着）选择特定的行，但也锁住他们，如果他们已经被当前事务（如果被执行的身份更新已为）更新。这允许您在当前事务中再次更新它们然后提交，而另一个事务无法以任何方式修改这些行。

Another way of looking at it, it is as if the following two statements are executed atomically:

另一种看待它的方式，就好像以下两个语句是原子地执行的：

select * from my_table where my_condition;

update my_table set my_column = my_column where my_condition;

Since the rows affected by my_conditionare locked, no other transaction can modify them in any way, and hence, transaction isolation level makes no difference here.

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

Note also that transaction isolation level is independent of locking: setting a different isolation level doesn't allow you to get around locking and update rows in a different transaction that are locked by your transaction.

另请注意，事务隔离级别与锁定无关：设置不同的隔离级别不允许您绕过锁定和更新由您的事务锁定的不同事务中的行。

What transaction isolation levels do guarantee (at different levels) is the consistency of data while transactions are in progress.

事务隔离级别（在不同级别）保证的是事务进行时数据的一致性。