Re: Theoretical Basis for SELECT FOR UPDATE

Tony Andrews wrote:
> vc wrote:
> > Tony Andrews wrote:
> > > vc wrote:
> > [...]
> > > > This feature is interesting because in fact it implies that the
> > > > hypothetical database has to implement multiversioning. How else can
> > > > it get the last consistent value of 'a' after 'a' has already been
> > > > changed ? This would exclude from consideration all purely locking
> > > > databases similar to DB2, SQL Server, Informix, Sybase, etc.
> > >
> > > Well I suppose the DBMS could gather all the data it will need before
> > > it changes any and store it temporarily somewhere?
> >
> > That's what multiversioning does, more or less ;)
>
> More or less maybe, but you DON'T need the full works of
> multi-versioning just to store data temporarily; you just need a TEMP
> data area or enough memory.

A simplistic "save just before" clearly is not enough, in the first place because during the time taken to read and store the 'before' value, the value may get too old due to the fact that another concurrent transaction may have just produced a newer version. A solution is to make the transaction that creates a new row version responsible for maintaining the version store.

Also, you need multiple committed versions of the same row with the history going back far enough to accomodate concurrent transactions that started at various times and may need older versions. A transaction that needs a 'before' value will select the version with the timestamp older but closest to its own.

As soon as you've built all that version maintenance stuff, you practically have a multiversion concurrency control database that you can use as such.

>I imagine DB2 et al already have something
> like that?

It does not. Received on Wed Oct 05 2005 - 19:34:10 CEST