© Dmitri Korotkevitch 2016

Dmitri Korotkevitch, Pro SQL Server Internals, 10.1007/978-1-4842-1964-5_21

21. Optimistic Isolation Levels

Dmitri Korotkevitch

(1)Tampa, Florida, USA

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

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

Row Versioning Overview

With optimistic transaction isolation ...

Get Pro SQL Server Internals, Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.