Re: [HACKERS] Serializable snapshot isolation error logging

[Dan S]

A starvation scenario is what worries me:

Lets say we have a slow complex transaction with many tables involved.
Concurrently smaller transactions begins and commits .

Wouldn't it be possible for a starvation scenario where the slower
transaction will
never run to completion but give a serialization failure over and over again
on retry ?

If I know at what sql-statement the serialization failure occurs can i then
conclude that
some of the tables in that exact query were involved in the conflict ?

If the serialization failure occurs at commit time what can I conclude then
?
They can  occur at commit time right ?

What is the likelyhood that there exists an update pattern that always give
the failure in the slow transaction ?

How would one break such a recurring pattern ?
You could maybe try to lock each table used in the slow transaction but that
would be prohibitively costly
for concurrency.
But what else if there is no way of knowing what the slow transaction
conflicts against.

As things with concurrency involved have a tendency to pop up in production
and not in test I think it is important to
start thinking about them as soon as possible.

Best Regards
Dan S

Re: [HACKERS] Serializable snapshot isolation error logging

[Dan S]

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 

> Dan S  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
>

[HACKERS] Serializable snapshot isolation error logging

[Dan S]

Hi !

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.

Best Regards
Dan S