Re: serializable read only deferrable

Dan Ports  wrote:
> On Tue, Dec 07, 2010 at 10:14:24AM -0600, Kevin Grittner wrote:
> The only thing I'm worried about here is how much risk of
> starvation remains. You'd need to wait until there are no running
> r/w transactions accessing overlapping data sets; for some
> applications that might not be any better than waiting for the
> system to be idle. But I think there's no way around that, it's
> just the price you have to pay to get a snapshot that can never see
> an anomaly.
Right -- this can't be a default behavior because of that, but it
rounds out the options for backups and big reports.  Without it you
have the choice between the potential for other transactions to
cancel because a cycle was completed by the READ ONLY transaction or
getting a view of data which may not be consistent with the later
state of the database[1].  This guarantees consistency without
causing rollbacks, with the additional benefit of faster runtime by
skipping SSI logic.
>> Pseudo-code of idea (conveniently ignoring locking issues and
>> non-serializable transactions):
>
> This seems reasonable to me. Let me know if you need help
> implementing it; I have some spare cycles right now.
That would be great.  I'm getting on a train today to go spend a week
on vacation in New Orleans, and I've been fretting about where this
patch is at compared to the release cycle.  :-(  I can suck down my
hurricanes with a calmer mind if I know you're on this.  :-)
In conjunction with this feature, it would be great if you could take
a look at how to recognize these conditions for a READ ONLY
transaction which is running under SSI, and back it out of SSI when
it hits that condition.  SIRead predicate locks, conflicts, and other
structures can be released, and we can stop checking the MVCC data on
reads.  Basically, we should be able to get to the DEFERRABLE type of
state while running -- it's just that we might cause some number of
transactions to cancel along the way before we hit that state. (These
two seem likely to be less work if done at the same time.)
-Kevin
[1] It has struck me that the receipting example is one case of a
more general pattern which I've frequently seen in business software
which is vulnerable to this sort of anomaly -- batch processing.
Basically, any time you have a control record which controls the
batch into which detail is placed, if the control information is
updated and that is committed while detail is still in flight, you
can have this class of anomaly.