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.
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 |