Re: Handling transaction failure due to concurrency errors

Christopher BROWN <brown@reflexe.fr> writes:
> I have an application running on a server, that (once per hour, in
> general) imports batches of data across several tables.  The INSERT
> and UPDATE operations apply to several tables, and are executed in a
> specific repeatable order, all within one big TRANSACTION_SERIALIZABLE
> operation (one commit at the end, no partial commits or rollbacks). It
> almost always works without error. The files are provided from an
> external organisation, and when an error occurs, they are
> automatically notified by e-mail.  Sometimes there is inconsistent
> data in the files, so they need to know when to fix this.

> However, sometimes it fails even when there's nothing wrong with their
> file, and the message indicates a transaction concurrency error.  I'm
> having trouble isolating the error; normally the tables affected by
> the transaction aren't affected by any other action (they are normally
> read by other processes to create transformed data in other tables).

The short answer, most likely, is that the interlocks that check
for serializability violations are approximate in the conservative
direction, and will sometimes throw a serializability error even when
the changes made by two concurrent transactions didn't really conflict.

Basically, if you use SERIALIZABLE mode, you *must* be prepared to retry
on serialization failure, and you shouldn't really question too hard
why you got such an error.  It's the price you pay for not having to
analyze the concurrent behavior of your application logic in detail.

What you might consider is automatically retrying a few times, and only
kicking out an error for human consideration if the serializability
failure seems persistent.

            regards, tom lane