SQL Server Concurrency Terminology
Optimistic concurrency control inside the SQL Server 2005/2008 database engine technically means that row versioning is used instead of a pessimistic locking approach. I believe this is a source of much confusion to both application developers and DBAs because row versioning isn’t required for applications to use optimistic concurrency control by using hints like (nolock).
Applications have used optimistic concurrency long before row versioning was introduced in SQL Server 2005/2008. In fact, the rowversion data type (a.k.a. timestamp) exists specifically to facilitate optimistic concurrency control.
Choosing the SQL Server concurrency control features (transaction isolation levels, transactions, locking hints) that are most appropriate for the task at hand without getting tripped up on terminology. For example, SQL Server optimistic row versioning should not be used solely because the application uses optimistic concurrency. A better choice for a well-tuned, highly transactional application might very well be default pessimistic SQL Server concurrency control behavior.
Row Versioning and Concurrency
Row versioning isolation levels can improve concurrency because committed data can be read from the version store instead of acquiring locks on data read. All update and delete statements store pre-update versions of changed data in the tempdb version store once either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database option is turned on. SQL Server then returns these pre-update row versions to applications reading data in the READ_COMMITTED (with READ_COMMITTED_SNAPSHOT database option on) or SNAPSHOT transaction isolation levels. The main difference between row versioning in these isolation levels is that READ_COMMITTED is single-statement level and SNAPSHOT is transaction level (multi-statement). See Row Versioning-based Isolation Levels in the Database Engine in the Books Online for details.
A SQL Server DBA can sometimes address blocking/deadlocking concurrency problems unilaterally by simply turning on the READ_COMMITTED_SNAPSHOT database option because statement-level row versioning is transparent to applications running in the default READ_COMMITTED isolation level. However, this shouldn’t be done indiscriminately because row versioning has an associated cost in terms of disk I/O, storage, CPU and DBA management.
One can even make concurrency problems worse by enabling row versioning unless there are sufficient server resources to handle the additional work introduced by row versioning. SNAPSHOT isolation level row versioning can also improve concurrency. However, SNAPSHOT isolation is a different paradigm than most SQL Server developers are accustomed to. With SNAPSHOT isolation, applications have a consistent view of the database from the time the transaction starts so developers need to consider the ramifications of retrieving stale data and perhaps delegating the task of checking for changed data in an optimistic model to the database engine.
The following Transact-SQL statement enables READ_COMMITTED_SNAPSHOT:
ALTER DATABASE AdventureWorks2008R2
SET READ_COMMITTED_SNAPSHOT ON;
The following Transact-SQL statement will enable ALLOW_SNAPSHOT_ISOLATION:
ALTER DATABASE AdventureWorks2008R2
SET ALLOW_SNAPSHOT_ISOLATION ON;
Unlike the READ_COMMITED_Snapshot method, the UPDATE statement in the SNAPSHOT level doesn’t need to check for changed data. SQL Server will raise an exception during the UPDATE if data was changed by another session since the start of the transaction. The Error 3960 is raised if the row changed since the transaction started:
Msg 3960, Level 16, State 2, Line 13
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table ‘dbo.Suppliers’ directly or indirectly in database ‘Northwind’ to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.