Re: 7.4 Wishlist - Mailing list pgsql-hackers

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
makechanges to their   local view of the database without compromising serialization.  This   probably wouldn't yield
anybenefits over the serializable isolation   level except that it would make it possible to perform experiments   on a
databasethat 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
transactionthat 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
deletedand which   still exist at commit time are deleted.  Referential integrity   rules are enforced, of course, so
it'spossible for the database to   retain some of its sanity even in the face of this model.  But the   overall state
ofthe database will probably be unpredictable (in   this scenario, the last concurrent transaction to commit "wins",
moreor less).
 

3.  We do something more sophisticated than 1 or 2.  Perhaps something   analogous to the branch merge functions that
CVSand other concurrent   version control systems can perform, where multiple branches are   finally merged into a
singleunified source snapshot.  I have no idea   how this would work for real, or if it's even possible (I believe
CVSrequires manual intervention to resolve branch conflicts during   a merge, an option that would obviously not be
availableto 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...