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: