agora inbox for pgsql-docs@postgresql.org  

help / color / mirror / Atom feed

From: Laurenz Albe <laurenz.albe@cybertec.at>
To: daniel.bickler@goprominent.com
To: pgsql-docs@lists.postgresql.org
Subject: Re: Serializable Transaction Anomoly
Date: Tue, 05 Nov 2024 18:21:36 +0100
Message-ID: <12ba622133fe91e9ecfb5a74ae80d74253d1956b.camel@cybertec.at> (raw)
In-Reply-To: <173081912264.705.9788227512147158659@wrigleys.postgresql.org>
References: <173081912264.705.9788227512147158659@wrigleys.postgresql.org>

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

view thread (5+ messages)  latest in thread

Message-ID: <12ba622133fe91e9ecfb5a74ae80d74253d1956b.camel@cybertec.at>
Permalink:  ../12ba622133fe91e9ecfb5a74ae80d74253d1956b.camel@cybertec.at/
Also on:    postgresql.org/message-id/12ba622133fe91e9ecfb5a74ae80d74253d1956b.camel@cybertec.at

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-docs@postgresql.org
  Cc: laurenz.albe@cybertec.at, daniel.bickler@goprominent.com, pgsql-docs@lists.postgresql.org
  Subject: Re: Serializable Transaction Anomoly
  In-Reply-To: <12ba622133fe91e9ecfb5a74ae80d74253d1956b.camel@cybertec.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox