Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners

© Dmitri Korotkevitch 2018

Dmitri KorotkevitchExpert SQL Server Transactions and Lockinghttps://doi.org/10.1007/978-1-4842-3957-5_6

6. Optimistic Isolation Levels

Dmitri Korotkevitch¹ 

(1)

Land O Lakes, Florida, USA

 

Optimistic transaction isolation levels were introduced in SQL Server 2005 as a new way to deal with blocking problems and address concurrency phenomena in a system. With optimistic transaction isolation levels, queries read “old” committed versions of rows while accessing data modified by the other sessions, rather than being blocked by the incompatibility of shared (S) and exclusive (X) locks.

This chapter will explain how optimistic isolation levels are implemented and how they affect the locking behavior of the system.

Row Versioning ...