Re: Handling transaction failure due to concurrency errors

Hi Tom,

Thanks for the quick reply. OK for the explanation, and I don't mind
implementing the retry logic for this case... I just don't know how to
detect when my code encounters this case (as opposed to other cases that
can arise, such as unresolved foreign keys when importing data; I don't
want to get into an infinite retry loop because it will never work in these
other cases).

For example, are there some specific values I can query for using
SQLException.getErrorCode() or SQLException.getSQLState(), or perhaps I can
check to see if the SQLException is an instance of a specific subclass? I
don't know where to start, because trial and error isn't an option: I'm
unable to cause the same errors locally that I'm sometimes seeing on a live
server.

​Thanks
Christopher

On 2 March 2018 at 16:10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Christopher BROWN <brown(at)reflexe(dot)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
>