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

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