In SQL Server 2005 and 2008, how to tell I'm using pessimistic concurrency model or optimistic one?

Question

I know SQL Server 2000 has a pessimistic concurrency model. And the optimistic model was added in SQL Server 2005. So how do I tell whether I'm using the pessimistic concurrency model or the optimistic one in SQL Server 2005 and 2008?

Thanks.

Answer 1

SQL 2005 (and 2008) introduces SNAPSHOT issolation. This is the way to move to optimistic concurrency. Take a look to Transaction Isolation and the New Snapshot Isolation Level article:

Isolation level     Dirty Reads    Non-repeatable Phantom reads  Concurrency 
                                   reads                         control
READ UNCOMMITTED     Yes           Yes            Yes            Pessimistic
READ COMMITTED       No            Yes            Yes            Pessimistic
(with locking)    
READ COMMITTED       No            Yes            Yes            Optimistic
(with snapshot)
REPEATABLE READ      No            No             Yes            Pessimistic
SNAPSHOT             No            No             No             Optimistic
SERIALIZABLE         No            No             No             Pessimistic