SELECT FOR UPDATE violates READ COMMITTED isolation?

From: Gavin Wahl <gwahl(at)fusionbox(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: SELECT FOR UPDATE violates READ COMMITTED isolation?
Date: 2017-04-12 22:14:08
Message-ID: CAAAf5g-ZSrgVi+G5Yzhc8jewoaBuMiex+x-=hCRYfv32GhQJFw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have this table:

CREATE TABLE test (id INT PRIMARY KEY);
INSERT INTO test VALUES (1);

Then I run these two transactions simultaneously:

one | two
-------------------------------+-------------------------------
BEGIN; |
| BEGIN;
DELETE FROM test; --DELETE 1 |
| SELECT * FROM test FOR UPDATE; -- Blocks...
INSERT INTO test VALUES (1); |
COMMIT; |
| -- ...returns 0 rows

How is it possible that the select in transaction two returns 0 rows? There was
never a transaction that committed with 0 rows in test. Shouldn't read
committed isolation prevent this?

I think this paragraph explains why it happens:
https://www.postgresql.org/docs/9.6/static/transaction-iso.html#XACT-READ-COMMITTED.

> If the first updater commits, the second updater will ignore the row if the
> first updater deleted it

How is that allowed in READ COMMITTED? I never committed with 0 rows in test,
so I expected to never have a SELECT that returns 0 rows.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gavin Wahl 2017-04-12 22:32:57 Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)
Previous Message Adrian Klaver 2017-04-12 22:09:27 Re: Error During PostGIS Build From Source on Linux