Isolation Levels

As a follow up to my NOLOCK blog I thought it would be a good idea to discuss the five different isolation levels in SQL Server. These are as follows:

·         Read Uncommitted – Lowest Level

o   Can read uncommitted modifications

o   Does not issue shared locks

o   Is not blocked by exclusive locks

·         Read Committed – SQL Server Default

o   Cannot read uncommitted modifications

o   Issues shared locks when READ_COMMITTED_SNAPSHOT is set to OFF

§  Row locks released before reading next row

§  Page locks released when reading next page

§  Table locks released at the end of the statement

o   Uses row versioning when READ_COMMITTED_SNAPSHOT is set to ON

·         Repeatable Read

o   Cannot read uncommitted modifications

o   Issues shared locks on all data read

§  All locks are released at the end of the statement

·         Snapshot

o   Data read by any statement in a transaction will be consistent with the data at the beginning of the transaction

o   Modifications by other transactions after the start of the transaction are not visible to statements in the current transaction

o   Does not issue locks when reading data

o   The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON

·         Serializable – Highest Level

o   Cannot read uncommitted modifications

o   Other transactions cannot modify data read by the current transaction until the current transaction completes.

o   Range locks are issued and only released at the end of the transaction.

What Are Isolation Levels?

Isolation levels are so named as they describe the degree that one transaction must be isolated from modifications made by other transactions. Locking is used by the database engine to enforce data integrity while we use the isolation level to choose what kind of locking takes places, how long it takes place and whether or not we select uncommitted data or block until the modification lock is released. With our highest level of isolation, serializable, we have the highest level of data integrity at the cost of concurrency while our lowest level, read uncommitted, offers the highest level of concurrency at the cost of data integrity. As developers we are striving for the best balance between data integrity and concurrency. Isolation levels only affect read operations. Data modification statements always take an exclusive lock and holds the lock until the transaction completes.

Concurrency Side Effects

Concurrency side effects occur in five different ways namely dirty reads, non-repeatable reads, phantom reads, lost updates and missing & double reads. Missing & double reads (MD) can occur via a key update or a page split.

Dirty Reads

Dirty reads quite simply mean that one transaction may read data that has been updated by another transaction but has not yet been committed. This modification may be rolled back leaving the first transaction having read incorrect data.

Non-repeatable Reads

A non-repeatable read occurs when the first transaction issues a read for the same row multiple times and in-between each read request another transaction has updated that row. This means the first transaction would read different data for the same row for each read.

Phantom Reads

A phantom read is when two or more read range requests are issued by the first transaction and in-between each read request another transaction has issued an insert or delete against that particular range. This means the first transaction would read a different number of rows each time.

Lost Updates

Lost updates occur when two or more transactions are updating the same row at the same time. The transaction that issues a commit last will retain its update while the other transactions will have their update overwritten.

Missing & Double Reads (MD) – Updated Key

This occurs when the first transaction is scanning an index and another transaction changes the index key column of the row during the read. The row will appear again if the key change moved the row ahead of the scan while the row will not appear if the key change moved the row behind the scan.

Missing & Double Reads (MD) – Page Split

This occurs when the first transaction is doing an allocation order scan and a second transaction issues an insert or update that causes a page split. The page split could cause an already read page to move ahead of the scan or an unread page to move behind the scan.

	Concurrency Side Effects
Isolation Level	Dirty Reads	MD - Page Split	MD - Key Update	Non-Repeatable	Phantom
Read uncommitted	YES	YES	YES	YES	YES
Read committed	-	-	YES	YES	YES
Repeatable read	-	-	-	-	YES
Snapshot	-	-	-	-	-
Serializable	-	-	-	-	-

Setting Isolation Level

Isolation level is specified at a session level or as a table hint. When set at a session level it remains in effect until the session is terminated or the isolation level is set to another level.

Session Level

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Table Hint

-- Serializable Hints

SELECT * FROM MyTable WITH (SERIALIZABLE, HOLDLOCK);

-- Repeatable Read Hints

SELECT * FROM MyTable WITH (REPEATABLEREAD);

-- Read Committed Hints

SELECT * FROM MyTable WITH (READCOMMITTED, READCOMMITTEDLOCK);

-- Read Uncommitted Hints

SELECT * FROM MyTable WITH (READUNCOMMITTED, NOLOCK);

Notes

WITH (READCOMMITTED) will take locks when the database option READ_COMMITTED_SNAPSHOT is OFF.  When READ_COMMITTED_SNAPSHOT is ON the read operation will run under the SNAPSHOT isolation level.

WITH (READCOMMITTEDLOCK) will take locks regardless of the database option READ_COMMITTED_SNAPSHOT.