[GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?

Поиск
Список
Период
Сортировка
От Gavin Wahl
Тема [GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?
Дата
Msg-id CAAAf5g-ZSrgVi+G5Yzhc8jewoaBuMiex+x-=hCRYfv32GhQJFw@mail.gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список 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.


В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: [GENERAL] Error During PostGIS Build From Source on Linux
Следующее
От: Gavin Wahl
Дата:
Сообщение: [GENERAL] Concurrency-safe Replacing a Set of Rows (Without SERIALIZABLE)