Transaction Isolation Level in SQL Server

Isolation levels in SQL Server control the way locking works between transactions.

SQL Server supports the following isolation levels

• Read Uncommitted
• Read Committed (The default)
• Repeatable Read
• Serializable
• Snapshot

Read Uncommitted

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

• Dirty Reads – This is when you read uncommitted data, when doing this there is no guarantee that data read will ever be committed meaning the data could well be bad.

• Phantom Reads – This is when data that you are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different.

Let Prepare for Demo

SET NOCOUNT OFF

GO

CREATE TABLE dbo.TestIsolationLevels (

EmpID INT NOT NULL,

EmpName VARCHAR(100),

EmpSalary MONEY,

CONSTRAINT pk_EmpID PRIMARY KEY(EmpID) )

GO

INSERT INTO dbo.TestIsolationLevels 

VALUES 

(2322, 'Dave Smith', 35000),

(2900, 'John West', 22000),

(2219, 'Melinda Carlisle', 40000),

(2950, 'Adam Johns', 18000) 

GO

BEGIN TRAN

UPDATE  dbo.TestIsolationLevels 

SET     EmpSalary = 25000

WHERE   EmpID = 2900

Now select the value that's being updated using the following (in a separate query window):

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON

GO

SELECT EmpID, EmpName, EmpSalary

FROM dbo.TestIsolationLevels

WHERE EmpID = 2900

Note the value for empSalary reflects the current *uncommitted* value. You can view the intent lock on the key (empID) and the intent exclusive locks on the object containers (the page on which the row is located and the object) imposed by the UPDATE statement using the following:

SELECT      es.login_name, tl.resource_type, 

            tl.resource_associated_entity_id,

            tl.request_mode, 

            tl.request_status

FROM        sys.dm_tran_locks tl

INNER JOIN  sys.dm_exec_sessions es ON tl.request_session_id = es.session_id 

WHERE       es.login_name = SUSER_SNAME() AND tl.resource_associated_entity_id <> 0

Now rollback the transaction to reset the EmpSalary for this employee to 22000.00.

Read Committed

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

• If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

• If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

ALTER DATABASE [YourDB] SET READ_COMMITTED_SNAPSHOT ON
GO
BEGIN TRAN
UPDATE  dbo.TestIsolationLevels 
SET     EmpSalary = 25000
WHERE   EmpID = 2900

Now in a separate query window:

SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.TestIsolationLevels WITH (READCOMMITTEDLOCK)
WHERE   EmpID = 2900

The query will hang as it is waiting for the key lock on EmpID to be released. Allow the query to execute by issuing in your first window:

ROLLBACK;

Snapshot Isolation

Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

The ALLOW_SNAPSHOT_ISOLATION database option must be set to ON before you can start a transaction that uses the SNAPSHOT isolation level. If a transaction using the SNAPSHOT isolation level accesses data in multiple databases, ALLOW_SNAPSHOT_ISOLATION must be set to ON in each database.

A transaction cannot be set to SNAPSHOT isolation level that started with another isolation level; doing so will cause the transaction to abort. If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.

A transaction running under SNAPSHOT isolation level can view changes made by that transaction. For example, if the transaction performs an UPDATE on a table and then issues a SELECT statement against the same table, the modified data will be included in the result set.

ALTER DATABASE [DBName] SET ALLOW_SNAPSHOT_ISOLATION ON

SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET NOCOUNT ON
GO
BEGIN TRAN
UPDATE  dbo.TestIsolationLevels 
SET     EmpSalary = 25000
WHERE   EmpID = 2900

Now in a separate query window:


SELECT  EmpID, EmpName, EmpSalary

FROM    dbo.TestIsolationLevels 
WHERE   EmpID = 2900

Repeatable Read

Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction. Other transactions can insert new rows that match the search conditions of statements issued by the current transaction. If the current transaction then retries the statement it will retrieve the new rows, which results in phantom reads. Because shared locks are held to the end of a transaction instead of being released at the end of each statement, concurrency is lower than the default READ COMMITTED isolation level. Use this option only when necessary.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.TestIsolationLevels 
WHERE   EmpID = 2900
WAITFOR DELAY '00:00:10'
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.TestIsolationLevels 
WHERE   EmpID = 2900
COMMIT

Now while this is executing, execute the following in a separate query window:

BEGIN TRAN
UPDATE  dbo.TestIsolationLevels 
SET     EmpSalary = 25000
WHERE   EmpID = 2900
COMMIT

Note the results below. Despite the two SELECTs being in one explicit transaction, the empSalary value differs between the individual statements in that transaction. The next isolation level helps to solve this problem.


Here is an example of using REPEATABLE READ when a concurrent UPDATE is occurring:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.TestIsolationLevels 
WHERE   EmpID = 2900
WAITFOR DELAY '00:00:10'
SELECT  EmpID, EmpName, EmpSalary
FROM    dbo.TestIsolationLevels 
WHERE   EmpID = 2900
COMMIT
Run the below while the above is executing:

BEGIN TRAN
UPDATE  dbo.TestIsolationLevels
SET     EmpSalary = 25000
WHERE   EmpID = 2900
COMMIT




You'll notice that the UPDATE transaction is waiting on the SELECT transaction, and that the SELECT transaction yields the correct data if the transaction consistency as a whole is considered. Interestingly though, this still doesn't hold true for phantom rows - it's possible to insert rows into a table and have the rows returned by a calling SELECT transaction even under the REPEATABLE READ isolation level.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpName
FROM    dbo.TestIsolationLevels 
WAITFOR DELAY '00:00:10'
SELECT  EmpName
FROM    dbo.TestIsolationLevels 
COMMIT

Run the below while the above is executing:
BEGIN TRAN
INSERT INTO dbo.TestIsolationLevels VALUES (3427, 'Phantom Employee 1', 30000)
COMMIT



To counter this problem, we need to use the SERIALIZABLE isolation level - the toughest of the bunch.





Serializable Isolation Level





Specifies the following:


Statements cannot read data that has been modified but not yet committed by other transactions.


No other transactions can modify data that has been read by the current transaction until the current transaction completes.


Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.



Range locks are placed in the range of key values that match the search conditions of each statement executed in a transaction. This blocks other transactions from updating or inserting any rows that would qualify for any of the statements executed by the current transaction. This means that if any of the statements in a transaction are executed a second time, they will read the same set of rows. The range locks are held until the transaction completes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction completes. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET NOCOUNT ON
GO
BEGIN TRAN
SELECT  EmpName
FROM    dbo.TestIsolationLevels 
WAITFOR DELAY '00:00:10'
SELECT  EmpName
FROM    dbo.TestIsolationLevels 
COMMIT

Run the below while the above is executing and it should fail now.


BEGIN TRAN
INSERT INTO dbo.TestIsolationLevels VALUES (3427, 'Phantom Employee 1', 30000)
COMMIT