Posted by Erland Sommarskog on 02/06/07 22:49

Guedes (susanaguedes@gmail.com) writes:
> I am new at SQL Server 2005 and have been reading everything I canfind
> about the new optimistic concurrency control mechanisms. As far as I
> can tell, the Snapshot Isolation Level is based avoids the use of
> shared locks using rowversioning instead.
>
> To control rowversions in SQL Server 2000 I was using an extra column
> in each table containing a rowversion datatype. What i do with this is
> find out if a row was modified. Is it possible in SQL Server 2005 to
> avoid the use of this extra column, using the new versioning features?

I don't think so. That is, there may be a way to do it, but it would
require far more work than that timestamp/rowversion column.

Snapshot isolation addresses a different issue than a rowversion column.
The rowversion is for optimistic locking: "let's hope the row does not
get modified while we are looking at it."

Snapshot isolation is rather about consistency. I want to know the
state of matters in this precise now. So it is actually a more strict
isolation level than the default READ COMMITTED.

It is also possible to configure a database so that READ COMMITTED is
implemented through the snapshot mechanism, but this works differently
from pure Snapshot isolation. In pure Snapshot Isolation, you don't
want to see wnything that happened after your transaction started,
not even if has been committed, which you accept with Read Committed
Snapshot.

It's nevertheless possible to do optimistic concurrency without
rowversion: you simply compare all columns. Several client APIs
employ this method. In my opinion, it's far less elegant.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]