Re: Serializable snapshot isolation error logging

Dan S <strd911@gmail.com> wrote:
> 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.
Unless we get the conflict list optimization added after the base
patch, you might get anywhere from one to three of the two to three
transactions involved in the serialization failure.  We can also
report the position they have in the "dangerous structure" and
mention that there are other, unidentified, transactions
participating in the conflict.  Once I get through with the issue
I'm working on based on Heikki's observations, I'll take a look at
this.
> 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 conflicts would have occurred on specific relations, but we
don't store all that -- it would be prohibitively expensive.  What
we track is that transaction T0's read couldn't see the write from
transaction T1.  Once you know that, SSI doesn't require that you
know which or how many relations were involved in that -- you've
established that T0 must logically come before T1.  That in itself
is no problem, of course.  But if you also establish that T1 must
come before TN (where TN might be T0 or a third transaction), you've
got a "pivot" at T1.  You're still not dead in the water yet, but if
that third logical transaction actually *commits* first, you're
probably in trouble.  The only way out is that if T0 is not TN, T0
is read only, and TN did *not* commit before T0 got its snapshot,
you're OK.
Where it gets complicated is that in the algorithm in the paper,
which we are following for the initial commit attempt, each
transaction keeps one "conflictIn" and one "conflictOut" pointer
for checking all this.  If you already have a conflict with one
transaction and then detect a conflict of the same type with
another, you change the conflict pointer to a self-reference --
which means you conflict with *all* other concurrent transactions
in that direction.  You also have lost the ability to report all
transaction which are involved in the conflict.
> 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.
That information would only be available on the *read* side.  We
count on MVCC data on the *write* side, and I'm not aware of any way
for a transaction to list everything it's written.  Since we're not
recording the particular points of conflict between transactions,
there's probably not a lot of point in listing it anyway -- there
might be a conflict on any number of tuples out of a great many read
or written.
> 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.
I understand the motivation, but the best this technique is likely
to be able to provide is the transactions involved, and that's not
always going to be complete unless we convert those single-
transaction conflict fields to lists.
> 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.
That might be doable.  If we're going to add such a GUC, though, it
should probably be considered a tuning GUC, with the "list" setting
recommended for debugging problems.  Of course, if you change it
from "field" to "list" the problem might disappear.  Hmmm.  Unless
we also had a "debug" setting which kept track of the list but
ignored it for purposes of detecting the dangerous structures
described above.
Of course, you will always know what transaction was canceled.
That does give you something to look at.
-Kevin