Re: User-facing aspects of serializable transactions

Jeff Davis <pgsql(at)j-davis(dot)com> writes:
> On Wed, 2009-05-27 at 18:54 -0500, Kevin Grittner wrote:
>> 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.

> That sounds like the "migration path" sort of GUC, which sounds
> reasonable to me.

> But what about all the other possible behaviors that were brought up
> (mentioned in more detail in [1]), such as:

> 1. implementation of the paper's technique sans predicate locking, that
> would avoid more serialization anomalies but not all?
> 2. various granularities of predicate locking?

> Should these be things the user controls per-transaction? If so, how?

I think it's important to draw a distinction between performance issues
and correctness issues. True serializability vs snapshot
serializability is a fundamental behavioral issue, whereas fooling
around with lock granularity might improve performance but it doesn't
make the difference between a correct application and an incorrect one.

A lesson that I think we've learned the hard way over the past few years
is that GUCs are fine for controlling performance issues, but you expose
yourself to all sorts of risks if you make fundamental semantics vary
depending on a GUC.

Putting those two thoughts together, I would say that the right thing
is

* SET TRANSACTION ISOLATION LEVEL SERIALIZABLE should mean what the spec
says.

* SET TRANSACTION ISOLATION LEVEL something-else should provide our
current snapshot-driven behavior. I don't have a strong feeling about
whether "something-else" should be spelled REPEATABLE READ or SNAPSHOT,
but lean slightly to the latter.

* Anything else you want to control should be a GUC, as long as it
doesn't affect any correctness properties.

regards, tom lane