postgresql - 事务是否会知道其他事务中所做的更改

Question

假设我有一个表 (Table1)，其值字段的值为 0。

然后两个用户(USER1 和 USER2)同时运行下面的事务

BEGIN
  UPDATE Table1 SET Value=Value+1; 
  UPDATE Table1 SET StatusField='Y' WHERE Value=2; 
COMMIT;

如果命令按以下顺序执行

USER1 开始交易

USER2开始交易

USER1结束交易；

USER2结束交易；

statusfield 会被设置为 Y 吗？

注意!这不是一个真实的例子，你可能永远不想使用这样的代码，但我只是想了解事务是如何工作的。换句话说，我想知道的是，如果两个事务同时开始并且其中一个完成，那么已经开始的事务是否知道第一个事务所做的更改？

Answer 1

这取决于选择的隔离级别。

只要默认隔离级别是 Read Committed 并且您没有更改它 - 答案是“是的，它将对其他事务可见”。

UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress). If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row.

其他隔离级别行为:

• Read uncommitted 的行为相同；
• Repeatable read 和 Serializable 将失败(如果第一个事务已成功提交)。

更多信息:13.2.1. Read Committed Isolation Level