何时使用 SELECT.FOR 更新？

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

问题1 : 下面是否是应该使用 SELECT ... FOR UPDATE的好例子？

给出:

  • 房间[身分证]
  • 标签[ id，name ]
  • Room _ tag [ room _ id，tag _ id ]
    • Room _ id 和 tag _ id 是外键

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

  • 最初:
    • 包含 [id = 1]的房间
    • 标签包含 [id = 1, name = 'cats']
    • Room _ tag 包含 [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.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和 SELECT ... FOR UPDATE？

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

137235 次浏览
小开
最佳答案

要实现房间和标签之间的一致性，并确保房间在被删除后永远不会返回，唯一可移植的方法是用 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一样。

什么时候应该使用 REPEATABLE_READ事务隔离和使用 READ_COMMITTED和 SELECT ... FOR UPDATE？

通常，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”时，我真的应该加上“因为某些数据库引擎实现的副作用”。

小开

简短的回答:

问题1: 是的。

问题2: 你用哪个并不重要。

长话短说:

select ... for update将(正如它所暗示的那样)选择某些行，但是也将它们锁定，就好像它们已经被当前事务更新了一样(或者就好像标识更新已经执行了一样)。这允许您在当前事务中再次更新它们，然后提交 而其他事务无法以任何方式修改这些行。

从另一个角度来看，就好像下面两个语句是自动执行的:

select * from my_table where my_condition;


update my_table set my_column = my_column where my_condition;

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

还要注意的是，事务隔离级别与锁定无关: 设置不同的隔离级别不允许绕过锁定，也不允许更新被事务锁定的不同事务中的行。

事务隔离级别(在不同级别)能够保证的是事务处理过程中数据的一致性。

小开

什么是更新选择？

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

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

提交答案