Re: serializable read only deferrable - Mailing list pgsql-hackers

Florian Pflug <fgp@phlo.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
writexacts)     break;  // we got it the easy way   associate all active read write xacts with this xact   block until
toldto wake } while (inconsistent); clear xact from any SSI structures its in run with the snapshot // each xact
associatedwith 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