Re: 7.4 Wishlist

Bruce Momjian wrote:
> Kevin Brown wrote:
> > I'd also like to see (if this is even possible) a transaction
> > isolation mode that would make it possible for multiple concurrent
> > updates to the same row to happen without blocking each other (I
> > imagine one way to make this possible would be for the last
> > transaction to commit to be the one that "wins". Each transaction
> > that commits gets its updates written so that other transactions that
> > begin after they commit will see them, of course). Neither "read
> > committed" nor "serialized" modes offer this. Don't know if it's
> > possible, but it would be nice (such that a transaction sees the
> > database as if it has it all to itself and doesn't block on
> > updates)...
>
> How would you do the update if you don't know of the transaction commits
> or aborts?

Maybe I should explain what I'm thinking a little further.

What I'm proposing is a transaction isolation model where each
transaction thinks that it's the only one making changes to the
database. That assumption obviously fails *outside* of a transaction,
but inside a transaction it should be possible to maintain the
illusion.

We already get this with serialized transaction isolation mode, with
one caveat: when an update conflicts with that of another transaction
that committed while the transaction of interest is in progress, the
transaction of interest gets rolled back immediately with a
serialization error.

What I'm proposing is to extend the illusion to updates. A
transaction running in this new isolation mode (call it "full
isolation" or something) never sees updates that other committed
transactions perform, whether they would conflict or not. The view
the running transaction sees of the database is a snapshot in time, as
it were, plus any updates the transaction itself has made.

Now, there are a couple of approaches we can take at transaction
commit that I can think of right now:

1. If there were any serialization errors, abort the transaction at
commit time. This allows transactions to safely make changes to their
local view of the database without compromising serialization. This
probably wouldn't yield any benefits over the serializable isolation
level except that it would make it possible to perform experiments
on a database that currently can't be performed (since serializable
isolation aborts the transaction at the first serialization
error). Applications would be easier to write since there would
be only one point during a transaction that the application would
have to check for unexpected errors: the commit.

2. We commit the changed rows. Updates only happen to rows that
exist at commit time. Rows which the transaction deleted and which
still exist at commit time are deleted. Referential integrity
rules are enforced, of course, so it's possible for the database to
retain some of its sanity even in the face of this model. But the
overall state of the database will probably be unpredictable (in
this scenario, the last concurrent transaction to commit "wins",
more or less).

3. We do something more sophisticated than 1 or 2. Perhaps something
analogous to the branch merge functions that CVS and other concurrent
version control systems can perform, where multiple branches are
finally merged into a single unified source snapshot. I have no idea
how this would work for real, or if it's even possible (I believe
CVS requires manual intervention to resolve branch conflicts during
a merge, an option that would obviously not be available to us).

How useful would it be? Beats me. Like I said, you could perform
some "what if" games with a database this way that you currently
can't, but I don't know how useful that would be. On thinking about
it a bit, it seems option 1 would be the most useful and perhaps the
most sensible.

Of course, perhaps the whole thing is just another stupid idea...