Re: Serializable snapshot isolation error logging

Well I guess one would like some way to find out which statements in the
involved transactions are the cause of the serialization failure and what
programs they reside in.

Also which relations were involved, the sql-statements may contain many
relations but just one or a few might be involved in the failure, right ?

The tuples involved if available.

I don't know how helpful it would be to know the pages involved might be, I
certainly wouldn't know what to do with that info.

All this is of course to be able to guess at which statements to modify or
change execution order of, take an explicit lock on and so on to reduce
serialization failure rate.

If holding a list of the involved transactions turns out to be expensive,
maybe one should be able to turn it on by a GUC only when you have a problem
and need the extra information to track it down.

Best Regards
Dan S

2010/9/20 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>

> Dan S <strd911(at)gmail(dot)com> wrote:
>
> > I wonder if the SSI implementation will give some way of detecting
> > the cause of a serialization failure.
> > Something like the deadlock detection maybe where you get the
> > sql-statements involved.
>
> I've been wondering what detail to try to include. There will often
> be three transactions involved in an SSI serialization failure,
> although the algorithm we're using (based on the referenced papers)
> may only know about one or two of them at the point of failure,
> because conflicts with multiple other transactions get collapsed to
> a self-reference. (One "optimization" I want to try is to maintain
> a list of conflicts rather than doing the above -- in which case we
> could always show all three transactions; but we may run out of time
> for that, and even if we don't, the decreased rollbacks might not
> pay for the cost of maintaining such a list.)
>
> The other information we would have would be the predicate locks
> held by whatever transactions we know about at the point of
> cancellation, based on what reads they've done; however, we wouldn't
> know about the writes done by those transaction, or which of the
> reads resulting in conflicts.
>
> So, given the above, any thoughts on what we *should* show?
>
> -Kevin
>