SELECT FOR UPDATE violates READ COMMITTED isolation?
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.