PostgreSQL中单语句执行过程中的幻影读取

Question

我想知道，当幻影读取发生在单个SQL语句执行过程中时，会出现并发情况吗？

给出：tx_test是一个3行的表，id列是主键，value是一些文本有效负载。表one_row_table1、one_row_table2、one_row_table3在事务开始时都有一行。考虑在READ COMMITTED事务中执行的SQL语句：

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED ;
WITH cte1 AS (
    UPDATE tx_test
        SET value = 'updated'
        WHERE id = (SELECT COUNT(*) FROM one_row_table1)
        RETURNING id),
     cte2 AS (
         UPDATE tx_test
             SET value = 'updated'
             WHERE id = (SELECT COUNT(*) FROM one_row_table1)
                                         /* what if one_row_table2 is used here? */
             RETURNING id),
     cte3 AS (
         UPDATE tx_test
             SET value = 'updated'
             WHERE id = (SELECT COUNT(*) FROM one_row_table1)
                                         /* what if one_row_table3 is used here? */
             RETURNING id)
SELECT * FROM cte1
UNION ALL
SELECT * FROM cte2
UNION ALL
SELECT * FROM cte3;
COMMIT;

当上述声明更新超过1条记录时，是否会出现这样的情况？

在类似情况下的实验中，在一个事务中有3条不同的语句，我很容易就可以得到2行的更新。当然，只有当隔离级别低于SERIALIZABLE时才是正确的。

BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 1
UPDATE tx_test
SET value = 'updated'
WHERE id = (SELECT COUNT(*) FROM one_row_table1);
-- 2
-- Another transaction inserts into one_row_table1
UPDATE tx_test
SET value = 'updated'
WHERE id = (SELECT COUNT(*) FROM one_row_table1);
-- 3
UPDATE tx_test
SET value = 'updated'
WHERE id = (SELECT COUNT(*) FROM one_row_table1);
COMMIT;

从类似的问题如何设置选择语句的隔离级别？我知道

..。这意味着，无论底层数据的当前状态如何，每个SQL语句都会看到数据的快照(数据库版本)。这样可以防止语句查看并发事务在相同数据行上执行更新所产生的不一致数据，从而为每个数据库会话提供事务隔离。..。

我能解释为PostgreSQL总是为每个语句提供事务隔离的SERIALIZABLE级别吗？或者是什么样的隔离？

Answer 1

是的，幻影读取可以在READ COMMITTED的单个语句中发生。触发它的简单方法是使用表联接。10月份我们在这里有一个问题，其中有一个加入UPDATE因并发更新而中断，还有一个引用期间异常LEFT JOIN的问题。

在REPEATABLE READ隔离级别检测或防止这些异常。

示例如何导致2次更新(在正常情况下只触发第一次更新)：

1. 在另一个会话(B)中，锁定tx_test：BEGIN的行；从tx_test中选择*进行更新；
2. 在会话A中执行示例查询。它将阻塞第一个CTE。
3. 当该示例被阻塞时，在会话B中向one_row_table1添加一行并提交事务:插入到one_row_table1 (数据)值(999)；提交；
4. 更新将触发两次，第一次是id=1，第二次是id=2。第三种方法不会再次触发更新id=2。

在REPEATABLE READ，插入到one_row_table1是不可见的，并且只有一个UPDATE触发。