Inside Microsoft® SQL Server™ 2005, Fourth Edition

Row Versioning

At the beginning of this chapter, I described two concurrency models that SQL Server can use. Pessimistic concurrency uses locking to guarantee the appropriate transactional behavior and avoid problems such as dirty reads, according to the isolation level you are using. Optimistic concurrency uses a new technology called row versioning to guarantee your transactions. In SQL Server 2005, optimistic concurrency is available after you enable one or both of the new database properties called READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION. Exclusive locks are acquired when you use optimistic concurrency, so you still need to be aware of all issues related to lock modes, lock resources, and lock duration, as well as the resources ...