Re: [GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: [GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?
Дата
Msg-id CAKFQuwarqkYj-FAaUOGGtGceEh31THfxW3PHoU_rmioPmwgdgQ@mail.gmail.com
обсуждение исходный текст
Ответ на [GENERAL] SELECT FOR UPDATE violates READ COMMITTED isolation?  (Gavin Wahl <gwahl@fusionbox.com>)
Список pgsql-general
Дерево обсуждения
On Wed, Apr 12, 2017 at 3:14 PM, Gavin Wahl <gwahl@fusionbox.com> wrote:
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.

​"... they will only find target rows that were committed as of the command start time"​

The newly added test row from <one> did not exist when <two> began so it can never been seen by <two>.  Period.

"In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress)"

The <two> SELECT * FROM test FOR UPDATE; query sees every single row in test but it indeed must wait due to the FOR UPDATE

"If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row."

Once <one> commits then <two> continues and ignores the now deleted rows.

Thus:

So, while there was never a time when the table contained zero rows all of the rows that <two> wanted to see were gone by the time it got to them and so it had nothing to show.  This is what is documented and what you show.

In short, this behavior is the price you pay for not having to concern yourself with serialization errors - which is exactly what you would get if you executed these transactions in any of the higher isolation levels.

David J.

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

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