Serializable Transaction Anomoly - Mailing list pgsql-docs

From PG Doc comments form
Subject Serializable Transaction Anomoly
Date
Msg-id 173081912264.705.9788227512147158659@wrigleys.postgresql.org
Whole thread Raw
Responses Re: Serializable Transaction Anomoly
List pgsql-docs
The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/17/transaction-iso.html
Description:

I discovered an oddity in Serializable Transaction behavior and while
referencing the current docs there is a possible contradiction and I'm not
sure if this is a bug or expected behavior. At minimum there seems to be a
contradiction in the Transaction Isolation page of the docs.

1. At the top "serialization anomoly" is defined as "The result of
successfully committing a group of transactions is inconsistent with *all
possible* orderings of running those transactions one at a time." (emphasis
mine).
2. In the first paragraph of 13.2.3, sentence 4 states "In fact, this
isolation level works exactly the same as Repeatable Read except that it
also monitors for conditions which could make execution of a concurrent set
of serializable transactions behave in a manner inconsistent with *all
possible* serial (one at a time) executions of those transactions." (again
I'm emphasizing 'all possible")
3. In the first large paragraph above the unordered list at the bottom it
states "While PostgreSQL's Serializable transaction isolation level only
allows concurrent transactions to commit if it can prove there is a serial
order of execution that would produce the same effect, it doesn't always
prevent errors from being raised that would not occur in true serial
execution." - "if it can prove there is a serial order" implies if it can
find a serial execution of statements that would have the same effect - that
seems at odd with 1. and 2.?

The example I found is caused by poor application code design but based on
the docs I would expect the serialization anomaly detection to report a
concurrent modification. The example I'm looking at assumes there is a
`example` table with id and name.

Serializable Transaction 1:
INSERT INTO example (name) VALUES ('test1') RETURNING id; -- assume it
returns id: 10
-- Don't commit

Serializable Transaction 2:
SELECT * from example WHERE id = 10 FOR UPDATE; -- Other databases block
here, postgreSQL does not and returns 0 rows
UPDATE example SET name = 'test2' WHERE id = 10; -- updates 0 rows because
insert wasn't committed

Serializable Transaction 1:
COMMIT; -- example record with id 10 now exists in the database

Serializable Transaction 2:
COMMIT; -- I expected 40001 error but instead transaction committed without
updating name.

I understand that with Snapshot Isolation the new record doesn't exist when
either SELECT FOR UPDATE or UPDATE execute in the 2nd transaction and the
docs do specify "Predicate locks in PostgreSQL, like in most other database
systems, are based on data actually accessed by a transaction." which
implies if transaction 2 can't see the data it can't predicate lock the
data,  And I believe the application code should not have been triggering a
background process (Transaction 2) before Transaction 1 commits because it
could rollback.

This seems to be in alignment with #3 but at odds with #1 and #2. Based on
the docs I referenced above, our observed behavior seems to be in alignment
with some of the documentation but in conflict with others. Am I misreading
#1 and 2?

Thanks,
Daniel

pgsql-docs by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: https://www.postgresql.org/docs/current/sql-insert.html
Next
From: Laurenz Albe
Date:
Subject: Re: Serializable Transaction Anomoly