[Zope-dev] Postgres DAs and Serializable Isolation Level

[Randall F. Kern]

This post is about fixing the "ProgrammingError:  Can't serialize access
due to concurrent update" exceptions you will often get when using
PostgreSQL with Zope.

First a little background on the error, for those of you not familiar
with Postgres's transaction isolation.  ANSI defines 4 levels of
transaction isolation for SQL: Read uncommitted, Read committed,
Repeatable read, and Serializable.  Postgres implements both Read
committed, and Serializable.

Read Committed is what most people expect from a database; SELECTs give
the most up to date information, but don't show results from other
transactions that have not been committed (this means two identical
SELECTs run back to back in the same transaction may produce different
results).  Serializable transactions only see data committed before the
transaction began (in this case, the identical SELECTs will always
return the same data).

Concurrent updates are not supported in the serializable transaction
mode; if one transaction tries to update (or delete) a row that has
already been updated by a concurrent uncommitted transaction, the second
transaction will fail with a "concurrent update" error.

In most cases, the proper response to an error of this type is to simply
redo the entire transaction.

It seems that ZPoPyDA and ZPsycopgDA both default to running in the
Serializable isolation level, rather than the Read Committed isolation
level.  This is of course kind of slow, and with most queries isn't
required.  However, it will produce the most robust results.

Given that the default transaction mode for these DAs is Serializable,
and Zope has this nifty support for ConflictErrors and retrying
transactions already, I propose Postgres DAs should convert the
concurrent update serialization errors into ConflictErrors
automatically.

I've been running this way for a few days, and everything seems great.
Any reasons why this might be good/bad/ugly very welcome.  (Like: is it
safe to throw a ConflictError at any random time during processing?
>From a quick glance at ZODB it seems they are only thrown at commit time
now)

Here is a small patch to ZPsyccopgDA 0.5.1 that implements this change:

ZPsycopgaDA/db.py
100a101,102
> from ZODB.POSException import ConflictError
>
183c185,191
<                 r =3D c.execute(qs)
---
>                 try:
>                     r =3D c.execute(qs)
>                 except psycopg.ProgrammingError, perr:
>                     _serialize_failure =3D "ERROR:  Can't serialize
access due to concurrent update"
>                     if perr.args[0][:len(_serialize_failure)] =3D=3D
_serialize_failure:
>                         raise ConflictError
>                     raise


-Randy