Message-ID: <12ba622133fe91e9ecfb5a74ae80d74253d1956b.camel@cybertec.at>
Subject: Re: Serializable Transaction Anomoly
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: daniel.bickler@goprominent.com, pgsql-docs@lists.postgresql.org
Date: Tue, 05 Nov 2024 18:21:36 +0100
In-Reply-To: <173081912264.705.9788227512147158659@wrigleys.postgresql.org>
References: <173081912264.705.9788227512147158659@wrigleys.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-2.fc40) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/12ba622133fe91e9ecfb5a74ae80d74253d1956b.camel%40cybertec.at>
Precedence: bulk

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 no=
t
> 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.
>=20
> 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." (emphas=
is
> 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 s=
et
> of serializable transactions behave in a manner inconsistent with *all
> possible* serial (one at a time) executions of those transactions." (agai=
n
> 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 seria=
l
> 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 - t=
hat
> 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 reall=
y
would be serializable.

> The example I found is caused by poor application code design but based o=
n
> 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.
>=20
> Serializable Transaction 1:
> INSERT INTO example (name) VALUES ('test1') RETURNING id; -- assume it
> returns id: 10
> -- Don't commit
>=20
> Serializable Transaction 2:
> SELECT * from example WHERE id =3D 10 FOR UPDATE; -- Other databases bloc=
k
> here, postgreSQL does not and returns 0 rows
> UPDATE example SET name =3D 'test2' WHERE id =3D 10; -- updates 0 rows be=
cause
> insert wasn't committed
>=20
> Serializable Transaction 1:
> COMMIT; -- example record with id 10 now exists in the database
>=20
> Serializable Transaction 2:
> COMMIT; -- I expected 40001 error but instead transaction committed witho=
ut
> updating name.
>=20
> I understand that with Snapshot Isolation the new record doesn't exist wh=
en
> either SELECT FOR UPDATE or UPDATE execute in the 2nd transaction and the
> docs do specify "Predicate locks in PostgreSQL, like in most other databa=
se
> 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,=C2=A0 And I believe the application code should not have been trigg=
ering a
> background process (Transaction 2) before Transaction 1 commits because i=
t
> could rollback.

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

Yours,
Laurenz Albe