Re: Normal errors codes in serializable transactions

Jon Smark <jon(dot)smark(at)yahoo(dot)com> wrote:

> The manual mentions that SERIALIZABLE transactions may abort with
> error 40001, in which case the client application is supposed to
> retry the transaction.  I've been stress testing an application
> by issuing lots of concurrent requests, and sure enough, every
> now and then I get back those 40001 errors.  However, sometimes I
> also get back error 40P01.  It seems no ill comes to pass if I
> also retry those transactions, but since this error code is not
> explicitly mentioned in the manual, one question arises: which
> error codes can be considered "normal" (in the sense it's
> reasonable for the client to retry) when issuing SERIALIZABLE
> transactions, and which ones (within the scope of class 40, of
> course) are to be considered real errors?

In PostgreSQL, 40001 is used for serialization failures due to MVCC
issues, and 40P01 is used for serialization failures due to
deadlocks.  I think that many years back when PostgreSQL moved to
MVCC it was judged important to differentiate between them with
different SQLSTATE values because deadlocks tend to be somewhat
more problematic.  Had I been involved with PostgreSQL at the time,
I would have argued the value of staying with the standard
serialization failure SQLSTATE (40001) for both, but it is unlikely
to be changed at this point.  From the application perspective,
both can (and generally should) be treated as meaning that there
was nothing wrong with the transaction in itself; it only failed
because of conflicts with one or more concurrent transactions and
is likely to succeed if retried from the start.

These two values are the only ones specifically geared toward
dealing with concurrency issues, but it might be worth noting that
some constraints (specifically UNIQUE, PRIMARY KEY, FOREIGN KEY,
and EXCLUSION) also deal with concurrency issues internally.  Those
SQLSTATE values aren't something you want to just automatically
schedule retries of a transaction for, though; it's just something
to keep in mind if an earlier test in a transaction indicated that
an operation should be able to succeed and then it fails on the
constraint.  Such cases normally indicate concurrency issues, not
any bug in PostgreSQL or necessarily even in the application.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company