Re: User-facing aspects of serializable transactions

On Wed, May 27, 2009 at 7:54 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>> On Wed, 2009-05-27 at 15:34 -0500, Kevin Grittner wrote:
>
>>> (C)  One or more GUCs will be added to control whether the new
>>> behavior is used when serializable transaction isolation is
>>> requested or whether, for compatibility with older PostgreSQL
>>> releases, the transaction actually runs with snapshot isolation.
>>> In any event, a request for repeatable read mode will provide the
>>> existing snapshot isolation mode.
>>
>> I'm not sure a GUC is the best way here, are you talking about as a
>> migration path, or something that would exist forever?
>
> I've gotten the distinct impression that some would prefer to continue
> to use their existing techniques under snapshot isolation.  I was sort
> of assuming that they would want a GUC to default to legacy behavior
> with a new setting for standard compliant behavior.
>
> Another alternative here would be to just change a request for a
> serializable transation to give you a serializable transaction, and
> document that the existing snapshot isolation is now available only by
> requesting repeatable read mode.  Right now you get snapshot isolation
> mode on a request for either repeatable read mode or serializable
> mode.
>
> I think that many people only use read committed; they would not be
> impacted at all.
>
> What do you think would be best here?

I think we should introduce a new value for SET TRANSACTION ISOLATION
LEVEL, maybe SNAPSHOT, intermediate between READ COMMITTED and
SERIALIZABLE.

IOW, SET TRANSACTION ISOLATION LEVEL READ COMMITTED should do what it
does now. SET TRANSACTION ISOLATION LEVEL SNAPSHOT should do what
SERIALIZABLE currently does, which is take and keep the same snapshot
for the whole transaction. And SERIALIZABLE should do that, plus
whatever new and better stuff we add.

...Robert