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.

I think it depends upon the data/programming goals you are trying to achieve. Frankly, trying to categorize the myriad options currently available in SQL server into simple “Optimistic” and “Pessimistic” buckets is limiting and short-sighted. {Read Uncommited through Serializable} times {declared vs. implicit transactions} times {varying kinds of snapshotting} to the {log shipping, mirroring, database replication} power equals more than two solutions. — Philip Kelley, May 21 '12 at 14:02

score 2 · Accepted Answer · answered May 19 '12 at 16:33

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

As a rule, I try to make hard-to-answer questions irrelevant. Here goes. Fuzzy words like Optimistic and Pessimistic don't really mean anything. I guess you could observe that the difference between Optimistic and Pessimistic is where in the process a lock is made. But, the point of buying SQLServer and paying all that money was to STOP worrying about such things. The yes/no answers on the chart above DO give you information that you can use to understand consequences of your choices about isolation level. Think about transactions through the first three columns of answers on the chart. — jerry, May 22 '12 at 18:30

score 0 · Answer 2 · answered May 19 '12 at 14:28

After reading some articles and documents from Microsoft. I got the following conclusion.

On SQL Server 2005+

If you are using read uncommitted, repeatable read or serializable isolation level, you are using pessimistic concurrency model.
If you are using snapshot isolation level, you are using optimistic concurrency model.
If you are using read committed isolation level and the database setting read_committed_snapshot is ON, then you are using optimistic concurrency model
If you are using read committed isolation level and the database setting read_committed_snapshot is OFF, then you are using pessimistic concurrency model

However, I still need confirmation. Also, If there are some code to test the concurrency model that would be great.

score 0 · Answer 3 · answered May 19 '12 at 14:53

Basically:

Pessimistic: you lock the record only for you until you have finished with it. So read committed transaction isolation level. (not uncommitted as you said)

Optimistic concurrency control works on the assumption that resource conflicts between multiple users are unlikely, and it permits transactions to execute without locking any resources. The resources are checked only when transactions are trying to change data. This determines whether any conflict has occurred (for example, by checking a version number). If a conflict occurs, the application must read the data and try the change again. Optimistic concurrency control is not provided with the product, but you can build it into your application manually by tracking database access. (Source)

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

3 Answers3