Re: Serializable Transaction Anomoly - Mailing list pgsql-docs

On Tue, 2024-11-05 at 15:05 +0000, PG Doc comments form wrote:
> 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.?

I don't see a contradiction.

#1 defines what an anomaly is.

#2 says that if there would be an anomaly with SERIALIZABLE isolation,
you will get a serialization error.
So there cannot be any false negatives.

#3 says that it is possible to get false positive serialization errors,
that is, serialization errors that occur even though the transactions really
would be serializable.

> 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.

The transactions you show above are serializable: if you execute transaction 2
strictly before transaction 1, you would end up with the same result.
So there is an equivalent serial execution.

Yours,
Laurenz Albe