Thread: Normal errors codes in serializable transactions

Hi,

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?

Thanks in advance!
Best,
Jon

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

40P01 is mentioned in the manual. See "A. PostgreSQL Error Codes" of
Appendixes.

In most cases it means that transaction is aborted because PostgreSQL
detected deadlock.

Grepping source indicates that part of HOT standby code uses the error
code as well, I'm not sure what is the situation when the error code
is supposed to be generated, however.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

Jon Smark <jon.smark@yahoo.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

Hi,

> 40P01 is mentioned in the manual. See "A. PostgreSQL Error Codes" of

> Appendixes.

I meant "mentioned in the manual in the section about concurrency control".
Since I alluded to class 40 errors, I think it was safe to assume that I was
familiar with Appendix A...

Best,
Jon

Hi,

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

Thank you very much for the prompt and informative reply!
That clears up my doubt.  For future reference: both 40001
and 40P01 are "normal" errors when issuing SERIALIZABLE
transactions in a concurrent setting...

Best,
Jon