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.

.

Relevant Pages

  • Re: Stateless clients and locking schemes (or rather isolation levels)
    ... > locking" has the same problem as replication: ... >>> stateless clients. ... >>> statement or a transaction level. ... >>> How does snapshot isolation help here? ...
    (comp.databases.informix)
  • Snapshot isolation with pooled connections
    ... Please let me know if it's possible to use snapshot isolation with the MS ... Only connections that were previously ... insert the row that has been modified or deleted by another transaction. ... public void run{ ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Transaction Isolation Level
    ... New rows must not be added to either table A and B while my transaction ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
  • Re: ADO.NET 2.0 - Question for Microsoft Employees
    ... snapshot isolation. ... That exists at System.Data - which leads me to believe that nothing really stops me from setting that isolation level on Oracle. ... They all work more or less the same: depending on the transaction isolation level, other transactions can read the data changed or have to wait till the exclusive lock is lifted from the row or table. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: computational model of transactions
    ... the rows that have been changed in the transaction log. ... varieties of the SNAPSHOT isolation that Microsoft faithfully, ... the same consistent database state. ... At time T4 the $100 transaction completes, recording a balance of $450. ...
    (comp.databases.theory)