Sql server® 2012 t-sql fundamentals

Isolation Levels

Isolation levels determine the behavior of concurrent users who read or write data. A reader is any 

statement that selects data, using a shared lock by default. A writer is any statement that makes a 

modification to a table and requires an exclusive lock. You cannot control the way writers behave in 

terms of the locks that they acquire and the duration of the locks, but you can control the way read-

ers behave. Also, as a result of controlling the behavior of readers, you can have an implicit influence 

on the behavior of writers. You do so by setting the isolation level, either at the session level with a 

session option or at the query level with a table hint.

SQL Server supports four traditional isolation levels that are based on pessimistic concurrency 

control (locking): READ UNCOMMITTED, READ COMMITTED (the default in on-premises SQL Server 

instances), REPEATABLE READ, and SERIALIZABLE. SQL Server also supports two isolation levels that are 

based on optimistic concurrency control (row versioning): SNAPSHOT and READ COMMITTED SNAP-

SHOT (the default in SQL Database). SNAPSHOT and READ COMMITTED SNAPSHOT are in a sense the 

optimistic-concurrency-based counterparts of READ COMMITTED and SERIALIZABLE, respectively. 

www.it-ebooks.info

---

310  

Microsoft SQL Server 2012 T-SQL Fundamentals

Note that some texts refer to READ COMMITTED and READ COMMITTED SNAPSHOT as one isola-

tion level with two different semantic treatments.

You can set the isolation level of the whole session by using the following command.

SET TRANSACTION ISOLATION LEVEL ;

You can use a table hint to set the isolation level of a query.

SELECT ... FROM WITH ();

Note that with the session option, you specify a space between the words in case the name of the 

isolation level is made of more than one word, such as REPEATABLE READ. With the query hint, you 

don’t specify a space between the words—for example, WITH (REPEATABLEREAD). Also, some of the 

isolation level names used as table hints have synonyms. For example, NOLOCK is the equivalent of 

specifying READUNCOMMITTED, and HOLDLOCK is the equivalent of specifying SERIALIZABLE.

The default isolation level in an on-premises SQL Server instance is READ COMMITTED (based on 

locking). The default in SQL Database is READ COMMITTED SNAPSHOT (based on row versioning). 

If you choose to override the default isolation level, your choice affects both the concurrency of the 

database users and the consistency they get from the data. 

With the first four isolation levels, the higher the isolation level, the tougher the locks that readers 

request and the longer their duration; therefore, the higher the isolation level, the higher the consis-

tency and the lower the concurrency. The converse is also true, of course. 

With the two snapshot-based isolation levels, SQL Server is able to store previous committed ver-

sions of rows in tempdb. Readers do not request shared locks; instead, if the current version of the 

rows is not what they are supposed to see, SQL Server provides them with an older version. 

The following sections describe each of the six supported isolation levels and demonstrate their 

behavior.