Phantom read during single statement execution in PostgreSQL

Question

I wonder could there ever be a concurrent situation when phantom read occurs during single SQL statement execution?

Given: tx_test is a table with 3 rows and id column is primary key, value is some text payload. Tables one_row_table1, one_row_table2, one_row_table3 all have 1 row at start of transaction. Consider SQL statement executed in READ COMMITTED transaction:

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;

Could there be a case when the statement above updates more then 1 record?

In my experiments with similar situation but with 3 different statements inside one transaction I was easily able to get 2 rows updated. Of course it is true only when isolation level is lower than 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;

From similar question How do I set the isolation level for SELECT statement? I know that

... This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This prevents statements from viewing inconsistent data produced by concurrent transactions performing updates on the same data rows, providing transaction isolation for each database session. ...

Can I interpret this as PostgreSQL always provides SERIALIZABLE level of transaction isolation per statement? Or what kind of isolation?

Answer 1

Yes, phantom reads can occur in a single statement at READ COMMITTED. The easy way to trigger it is with table joins. We had a question here in October where a join in UPDATE was disrupted by a concurrent update, and another referencing anomalies during LEFT JOIN.

These anomalies are detected or prevented at the REPEATABLE READ isolation level.

How your example causes 2 updates (which only fires the first update in the normal case):

1. In another session (B), lock the rows of tx_test:

   BEGIN;
   SELECT * FROM tx_test FOR UPDATE;
   

2. Execute the example query in session A. It will block on the first CTE.

3. While the example is blocked, add a row to one_row_table1 in session B and commit the transaction:

   INSERT INTO one_row_table1 (data) VALUES (999);
   COMMIT;
   

4. The update will fire twice, first with id=1 and second with id=2. The third will not fire to update id=2 again.

At REPEATABLE READ, the insert into one_row_table1 is not visible, and only one UPDATE fires.