何时使用SELECT ... FOR UPDATE? [英] When to use SELECT ... FOR UPDATE?

问题描述

请帮助我了解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.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.正确吗?

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

MySQL(和其他一些旧系统)中的

• MyISAM确实在查询期间锁定了整个表.

• 在SQL Server中，SELECT查询将共享锁放置在已检查的记录/页面/表上，而DML查询将更新锁放置(随后将其升级为互斥或降级为共享锁) .排他锁与共享锁不兼容，因此SELECT或DELETE查询都将锁定，直到提交另一个会话为止.

• 在使用MVCC的数据库中(例如Oracle，PostgreSQL，MySQL和InnoDB)，DML查询将创建记录的副本(以一种或另一种方式)通常，读者不会阻止作家，反之亦然.对于这些数据库，SELECT FOR UPDATE会派上用场:它将锁定SELECT或DELETE查询，直到提交另一个会话为止，就像SQL Server一样.

相对于READ_COMMITTED和SELECT ... FOR UPDATE，何时应该使用REPEATABLE_READ事务隔离?

通常，REPEATABLE READ不会禁止幻像行(在另一笔交易中出现或消失的行，而不是被修改的行)

• 在Oracle和更低版本的PostgreSQL中，REPEATABLE READ实际上是SERIALIZABLE的同义词.基本上，这意味着事务在启动后不会看到所做的更改.因此，在此设置中，最后一个Thread 1查询将返回房间，就好像它从未被删除过(可能是您想要的，也可能不是您想要的).如果不想在删除房间后显示房间，则应使用SELECT FOR UPDATE

锁定行

• 在InnoDB中，REPEATABLE READ和SERIALIZABLE是不同的东西:在SERIALIZABLE模式下的读取器在其评估的记录上设置了下一键锁定，从而有效地防止了它们上的并发DML.因此，您无需在可序列化模式下使用SELECT FOR UPDATE，而在REPEATABLE READ或READ COMMITED中需要它们.

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

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

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

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

Given:

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

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:

• 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

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 rooms until Thread 1 is done. Is that correct?

Question 2: When should one use SERIALIZABLE transaction isolation versus READ_COMMITTED with SELECT ... FOR UPDATE?

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

解决方案

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.

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

---

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

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

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

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

• In databases which use MVCC (like Oracle, PostgreSQL, MySQL with InnoDB), a DML query 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 UPDATE would come handy: it would lock either SELECT or the DELETE query until another session commits, just as SQL Server does.

When should one use REPEATABLE_READ transaction isolation versus READ_COMMITTED with SELECT ... FOR UPDATE?

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

• In Oracle and earlier PostgreSQL versions, REPEATABLE READ is 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 1 query 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 READ and SERIALIZABLE are different things: readers in SERIALIZABLE mode set next-key locks on the records they evaluate, effectively preventing the concurrent DML on them. So you don't need a SELECT FOR UPDATE in serializable mode, but do need them in REPEATABLE READ or 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 MVCC or otherwise).

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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！