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 = c.execute(qs) ---
try: r = c.execute(qs) except psycopg.ProgrammingError, perr: _serialize_failure = "ERROR: Can't serialize
access due to concurrent update"
if perr.args[0][:len(_serialize_failure)] ==
_serialize_failure:
raise ConflictError raise
-Randy