The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/index.html
Description:
Dear Sir / Madam,
according the
https://www.postgresql.org/docs/current/transaction-iso.html#XACT-SERIALIZABLE
a transaction must be retried if its commit failed.
Quotes: " using Serializable transactions will allow one transaction to
commit and will roll the other back".
Later: "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"
Indeed, when the commit is rejected the error is "could not serialize access
due to read/write dependencies among transactions" and the reason code is
"Canceled on identification as a pivot, during commit attempt.".
Basic test case: check if a room is available at given dates (select),
insert a new reservation if it is and throw a business exception
otherwise.
However, sometime the error is thrown when insert is executed. The message
is still "could not serialize access due to read/write dependencies among
transactions" but the reason code is "Canceled on identification as a pivot,
during write.".
From a developer point of view it has impact on the retry strategy because I
should consider that any insert/update/delete during the transaction can
result in a 40001 (and not only at commit).
This page https://wiki.postgresql.org/wiki/SSI does dot mention "Canceled
on identification as a pivot, during write." either.
According the this message https://stackoverflow.com/a/9984640/10938834 from
Kevin Grittner "When using SERIALIZABLE transactions in 9.1, your
application should be prepared for serialization failures on any statement
except for ROLLBACK ". "any statement" is the key point, I think it could me
clearer in the documentation. I acknowledge that 13.2.2 section mention
serialization failure in a more general way (i.e. does not emphasis on
commit time).
Regards,
Alaric Hermant