Are all class 40 errors normal in serializable transactions?

Question

Some of my transactions are declared as SERIALIZABLE. Sometimes I get back class 40 errors such as 40001 serialization_failure and 40P01 deadlock_detected (I haven't seen other codes yet), particularly when I'm stress testing the system by doing lots of concurrent transactions.

If I understood the manual correctly, this is to be expected. When these errors happen, I'm therefore suppose to retry the transaction. My doubt is whether all of class 40 errors are "normal" when handling SERIALIZABLE transactions. In other words, can I just assume that any class 40 error should lead me to retry the transaction, or is this assumption correct only for a subset of class 40 errors?

Answer 1

Per the error codes section of the manual the 40xxxx SQLSTATE category is "transaction rollback".

It includes the error codes:

• 40000 transaction_rollback
• 40002 transaction_integrity_constraint_violation
• 40001 serialization_failure
• 40003 statement_completion_unknown
• 40P01 deadlock_detected

Of these, I would not generally expect transaction_integrity_constraint_violation to trigger a retry, since that'll be raised when a DEFERRED foreign key check causes a transaction to abort when commit is attempted. This is unlikely to go away if retried unless your application has issues with concurrency and locking design.

I haven't encountered statement_completion_unknown; I'd suggest looking it up.

Personally I would retry on deadlock_detected and serialization_failure only.

Answer 2

In the meantime, Kevin Grittner gave me an unequivocal answer in the pgsql-general mailing list. To summarise, both errors 40001 and 40P01 are "normal" when issuing serializable transactions in a concurrent setting.