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

On Dec6, 2010, at 22:53 , Kevin Grittner wrote:
>> The alternative seems to be to drop the guarantee that a
>> SERIALIZABLE READ ONLY DEFERRABLE won't be starved forever by a
>> stream of overlapping non-READ ONLY transactions. Then a flag in
>> the proc array that marks non-READ ONLY transactions should be
>> sufficient, plus a wait-and-retry loop to take snapshots for
>> SERIALIZABLE READ ONLY DEFERRABLE transactions.
>
> If I can find a way to pause an active process I already have
> functions in which I maintain the count of active SERIALIZABLE READ
> WRITE transactions as they begin and end -- I could release pending
> DEFERRABLE transactions when the count hits zero without any
> separate loop.  That has the added attraction of being a path to the
> more complex checking which could allow the deferrable process to
> start sooner in some circumstances.  The "simple" solution with the
> heavyweight lock would not have been a good path to that.

I'm starting to wonder if you couldn't get a weaker form of the non-starvation guarantee back by doing the waiting
*after*you acquire the snapshot of a SERIALIZABLE RAD ONLY transaction instead of before. AFAICS, the main reason for a
SERIALIZABLERAD ONLY transaction's snapshot to be inconsistent that it sees some transaction A as committed and B as
uncommittedwhen on the other hand B must happen before A in any serial schedule. In other words, if there is no
dangerousstructure even if you add an rw-dependency edge from the SERIALIZABLE RAD ONLY transaction to every concurrent
transaction,the SERIALIZABLE RAD ONLY transaction's snapshot is consistent. I'm thus envisioning something along the
lineof 

1) Take a snapshot, flag the transaction as SERIALIZABLE READ ONLY DEFERRED, and add a rw-dependency to every other
runningREAD 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
DEFERREDand mark it INCONSISTENT if it is. 

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

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

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
ponderingthis for a bit before I went to bed yesterday. 

best regards,
Florian Pflug