| 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: | Whole Thread | Raw Message | 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 |