Re: serializable read only deferrable

Florian Pflug <fgp(at)phlo(dot)org> wrote:

> reason for a SERIALIZABLE READ ONLY transaction's snapshot to be
> inconsistent that it sees some transaction A as committed and B as
> uncommitted when on the other hand B must happen before A in any
> serial schedule.

Precisely right, and very well stated.

> I'm thus envisioning something along the line of
>
> 1) Take a snapshot, flag the transaction as SERIALIZABLE READ ONLY
> DEFERRED, and add a rw-dependency to every other running READ
> WRITE transaction
> 2) Wait for all these concurrent transaction to either COMMIT or
> ABORT
> 3) Check if the transaction has been marked INCONSISTENT. If not,
> let the transaction proceed. If it was, start over with (1)
>
> *) During conflict detection, you'd check if one of the
> participating transaction is flagged as SERIALIZABLE READ ONLY
> DEFERRED and mark it INCONSISTENT if it is.

That is brilliant.

> Essentially, instead of adding dependencies as you go along and
> abort once you hit a conflict, SERIALIZABLE READ ONLY DEFERRED
> transactions would assume the worst case from the start and thus
> be able to bypass the more detailed checks later on.

Right -- such a transaction, having acquired a good snapshot, could
release all SSI resources and run without any of the SSI overhead.

> With this scheme, you'd at least stand some chance of eventually
> acquiring a consistent snapshot, even in the case of an endless
> stream of overlapping READ WRITE transactions.

Yeah, I'd been twisting ideas around trying to find a good way to do
this; you've got it right at the conceptual level, I think.

> I have to admit though that I didn't really think this through
> thoroughly yet, it was more of a quick idea I got after pondering
> this for a bit before I went to bed yesterday.

[reads through it a few more times, sips caffeine, and thinks]

Really, what you care about is whether any of the READ WRITE
transactions active at the time the snapshot was acquired commit
after developing a rw-conflict with a transaction which committed
before the READ ONLY DEFERRABLE snapshot was acquired. (The reader
would have to appear first in any serial schedule, yet the READ ONLY
transaction can see the effects of the writer but not the reader.)
Which brings up another point, that reader must also write to a
permanent table before it commits in order to become the pivot in
the dangerous structure.

Pseudo-code of idea (conveniently ignoring locking issues and
non-serializable transactions):

// serializable read only deferrable xact
do
{
get a snapshot
clear inconsistent flag
if (no concurrent read write xacts)
break; // we got it the easy way
associate all active read write xacts with this xact
block until told to wake
} while (inconsistent);
clear xact from any SSI structures its in
run with the snapshot

// each xact associated with the above
on transaction completion
if (commit
and has written
and has conflict out
to xact committed before deferrable snapshot)
{
flag deferrable as inconsistent
unblock deferrable xact
}
else
if this is termination of last associated read write xact
unblock deferrable xact

Seem sane?

-Kevin