[storm] questions around "could not serialize access due to concurrent update"

[James Henstridge]

On Tue, Feb 24, 2009 at 4:57 AM, Martin DeMello <martindemello at gmail.com> wrote:
> I'm using storm, with psycopg2, for a mildly complicated application
> (two processes, each of which have 2-3 threads running at a time), and
> I'm getting several instances of the following:
>
>> TransactionRollbackError: could not serialize access due to concurrent update
>
> I'm new to storm (and newish to db programming in general), and the
> docs don't have much to say about this. Several questions:
>
> - Is it a sign of a design or programming flaw in my code, or
> something to be expected when two processes are trying to access the
> same db objects?

It is expected behaviour.  Each transaction works under the lie that
it is the only one accessing the database (so can assume no one else
is fiddling with the tables).  When the database realises that two
transactions are relying on the same data it will kill one of them
with a TransactionRollbackError.

> - How would I go about identifying what I'm doing wrong and debugging it?

It isn't caused by something you're doing wrong.

> - Can I have storm log which rows exactly are triggering the error?
> - Is there a recommended design pattern to mitigate this?
> - Is it enough to wrap problematic commits in a block that retries
> them until they succeed?

Pretty much.  You should set a limit on the number of retries you make
though.  Also be careful about making changes that have side effects
outside of the transaction.

For instance, if you want to write a file concurrently with the
transaction, you want to do something like:
 1. write file contents to a temporary file location.
 2. commit the transaction
 3. if the commit succeeds, rename file to real name.
 4. if the transaction fails, remove the temporary file.

A function decorator can be a good way to write your retry code.
Alternatively, if you're using a framework like Zope 3, telling the
publisher that a psycopg2 TransactionRollbackError should be retried
is enough.

> - Any general advice on writing code that accesses the same db tables
> from multiple processes?

Make sure your code is written to expect serialisation failures and
retry as appropriate.  If some of the programs are cron jobs, it can
be enough to let them fail if the next run will pick up where the
failed one left off.

James.