Re: Serializable Snapshot Isolation

<p>Just to be clear I wasn't saying it was or wasn't a problem, I was just trying to see if I understand the problem
andif I do maybe help bring others up to speed.<div class="gmail_quote">On 25 Sep 2010 23:28, "Kevin Grittner" <<a
href="mailto:Kevin.Grittner@wicourts.gov">Kevin.Grittner@wicourts.gov</a>>wrote:<br type="attribution" />> Greg
Starkwrote:<br />> <br /> >> So T1 must have happened before TN because it wrote something based<br />>>
ondata as it was before TN modified it. But T0 can see TN but not<br />>> T1 so there's no complete ordering
betweenthe three transactions<br /> >> that makes them all make sense.<br />> <br />> Correct.<br />>
<br/>>> The thing is that the database state is reasonable, the database<br />>> state is after it would be
ifthe ordering were T1,TN with T0<br /> >> happening any time. And the backup state is reasonable, it's as if<br
/>>>it occurred after TN and before T1. They just don't agree.<br />> <br />> I agree that the database
stateeventually "settles" into a valid<br /> > long-term condition in this particular example. The point you are<br
/>>conceding seems to be that the image captured by pg_dump is not<br />> consistent with that. If so, I agree.
Youdon't see that as a<br /> > problem; I do. I'm not sure where we go from there. Certainly that<br />> is
betterthan making pg_dump vulnerable to serialization failure --<br />> if we don't implement the SERIALIZABLE READ
ONLYDEFERRABLE<br /> > transactions I was describing, we can change pg_dump to use<br />> REPEATABLE READ and we
willbe no worse off than we are now.<br />> <br />> The new feature I was proposing was that we create a
SERIALIZABLE<br/> > READ ONLY DEFERRABLE transaction style which would, rather than<br />> acquiring predicate
locksand watching for conflicts, potentially<br />> wait until it could acquire a snapshot which was guaranteed to
be<br/>> conflict-free. In the example discussed on this thread, if we<br /> > changed pg_dump to use such a
mode,when it went to acquire a<br />> snapshot it would see that it overlapped T1, which was not READ ONLY,<br
/>>which in turn overlapped TN, which had written to a table and<br />> committed. It would then block until
completionof the T1<br /> > transaction and adjust its snapshot to make that transaction visible.<br />> You
wouldnow have a backup entirely consistent with the long-term<br />> state of the database, with no risk of
serializationfailure and no<br /> > bloating of the predicate lock structures.<br />> <br />> The only down
sideis that there could be blocking when such a<br />> transaction acquires its snapshot. That seems a reasonable
priceto<br />> pay for backup integrity. Obviously, if we had such a mode, it would<br /> > be trivial to add a
switchto the pg_dump command line which would<br />> let the user choose between guaranteed dump integrity and
guaranteed<br/>> lack of blocking at the start of the dump.<br />> <br />> -Kevin<br /></div>