Re: Informix vs. Sybase vs. Oracle vs. (gasp) MS SQL Server

I remember attending a session on Interbase at Borland's dog and pony show in Anaheim last year where they described a scheme much like this in connection with their own concurrency model. Interbase was (I think) previously called Starbase and sold by Quasar Systems, now also renamed to be Cognos.

Stephen D. Williams wrote:
>
> Let me just branch in a completely new direction with this conversation:
>
> I believe that many (not all) locking issues are much cleaner when you
> introduce versioning into a system.
>
> While I believe that versioning is most useful when used as the basic
> concurrency control and consistency mechanism, I recently designed a
> versioning scheme on top of standard SQL, assuming PLL/RLL.
>
> The requirement I was trying to address was a situation where the user
> wanted a long term lock in a situation where this would have instantly
> caused a deadlock. Something similar to a web server where you want to
> take time publishing the next version of a site, while actively serving
> the previous version of the site. The Logical commit automatically makes
> this site available. I wanted to find a way to do this in a generic, SQL
> compatible way.
>
> In this scheme, the underlying locking mechanism is only used for very
> short transactions and the concurrency/consistency for desired
> tables/joins is done at the logical level, normally in a stored
> procedure. The required versioning tables and processing would normally
> be cached in memory and purging would be easily controlled.
>
> Very simply (i.e. filtering many details), I kept a table of generations
> for both pending and committed. All rows in all tables of the same
> generation (transaction) 'pointed to' the same generation. Each
> transaction simply adds a new record to each table with the current
> generation. The 'logical version commit' moves the generation from the
> pending to committed table, combining ranges to keep the generation table
> small.
>
> 'Reads' select the row with the highest generation that exists in the
> committed table. Recovery after a restart deletes all rows with
> generations in the pending table.
>
> Aside from Log entries, there should be little performance penalty since
> the generation tables never get larger than the number of simultaneous
> transactions + generation purge level. (As with any versioning system,
> old versions can be kept and purged as desired, allowing time based
> reports on live databases to be completely consistent.)
>
> Two requests:
> o Please try to show why this idea is all wet. (Even I don't trust my
> ideas completely until they are vetted.)
>
> o Explain why RDBMS vendors (as opposed to OODBMS) haven't adopted
> versioning more readily. (I was never comfortable with the 'before
> image' (Oracle) style of consistency control OR the lack thereof
> (Sybase). I know that Oracle has some support for versioning in recent
> versions, but it's unclear how pervasive it is.)
>
> sdw
> --
> Stephen D. Williams 25Feb1965 VW,OH (FBI ID) sdw_at_lig.net
> http://www.lig.net/sdw
> Consultant, Vienna,VA Mar95- 703-918-1357W 43392 Wayside Cir.,Ashburn, VA
> 20147
> CTO:Netword LLC
> OO/Unix/Comm/Video/DBMS
> Pres.:Concinnous Consulting,Inc.;SDW Systems;Local Internet Gateway
> Co.3Nov1997
 

-- 
_______________________________________________________________
Richard Scranton  LDA SYSTEMS Information Management Consulting
<http://www.netcom.com/~scrantr/>