Re: computational model of transactions

Brian Selzer wrote:
[...]
> SQL Server 2005 provides this capability, it's called READ COMMITTED
> SNAPSHOT isolation. It accomplishes it by accessing the previous states of
> the rows that have been changed in the transaction log.

That is not entirely corect. SQL Server 2005 actually has two varieties of the SNAPSHOT isolation that Microsoft faithfully, at least at the conceptual level, copied from Oracle: read commtted snapshot and transactional snapshot which correspond to Oracle's READ COMMITTED and SERIALIZABLE respectively. The only true innovation MS can boast about would be the correct name for the transactional snapshot (rather than the Oracle 'serializable'). It's worth to remind that Oracle has had the snapshot isolation aka row versioning aka MVCC since at least 1984 (version 4).

> The mechanism is
> similar to that used by the Volume Shadow Copy service in Windows 2003 and
> many of the open file backup options that have been available for years.

The above statement does not make any obvious sense in the context of database concurrency control (except perhaps some superficial similarities).

>
> The benefit of this mechanism is that it provides the same level of
> protection for the issuing transaction as READ COMMITTED without applying
> any shared locks. Fewer locks means less blocking and fewer deadlocks.
> Fewer locks means better overall performance. But it's not a silver bullet.
> There are times when it is definitely contraindicated. The danger is that
> DBAs and developers will use it without fully understanding the
> ramifications of using it.

Such DBA and developers have only themselves to blame for the hypothetical ignorance since various MVCC systems have been readily available for use and study for many years, e.g. Interbase, Rdb, Postgres (in addition to Oracle).

>
> This should never be used in a posting routine because the information read
> out is already stale even before the read is completed, so a subsequent
> update based on that information would introduce garbage into the database.
> It is inevitable that overzealous DBAs and developers will use this to boost
> performance and end up with corrupt databases.

Unfortunately, without further elaboration, the above paragraph does not make much sense. Received on Thu Aug 03 2006 - 23:35:50 CEST