Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 176

Many people misunderstand isolation levels. You may have seen large
amounts of database code out there that use the NOLOCK hint, for exam-
ple. To help combat this, you could i nd ways to educate people about
isolation levels. Kendra Little has drawn a poster about them, shown in
Figure 6-17. (You can i nd all her posters at www.littlekendra.com/
sqlserverposters . ) It could hang on a wall and serve as a conversation
piece — people will ask you about it, providing an opportunity to talk to
them about isolation levels.

Kendra's poster highlights the pessimism/optimism balance between
the various isolation levels. There are four pessimistic isolation levels,
and two optimistic ones. The optimistic levels involve the creation of
snapshot data to allow additional concurrency, rather than the
pessimistic behavior of blocking.

The following sections i rst describe the pessimistic isolation levels,
followed by the optimistic ones.

FIGURE 6-17

Serializable
The serializable isolation level is the most pessimistic isolation level in SQL Server. It exhibits none
of the concurrency problems that were shown earlier in the chapter. It simply locks everything up to
ensure that no side-effects can take place. It does this by taking out range locks, which appear in the
larger of the two lock compatibility matrices. These locks ensure that the whole range of any data
that has been queried during the transaction is preserved, including avoiding insertions, to avoid the
problem of phantom reads. These range locks typically conl ict with each other, much more so than
intent locks, thereby keeping the isolation as its utmost level.

Range locks can be seen in the following code (code i le Ch6IsolationLevels.sql ) and in
Figure 6-18:

USE AdventureWorks2012;
GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM Person.Person
where BusinessEntityID < 10;

SELECT resource_type
,resource_subtype
,resource_description
,resource_associated_entity_id
,request_mode
,request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

ROLLBACK TRAN;