Set Transaction Isolation Level in SQL 

In this post we’ll discuss SQL server “Set Transaction Isolation Level”. These are of 5 types, and we’ll discuss all one by one below.

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SNAPSHOT
• SERIALIZABLE

-- Syntax for SQL Server ISOLATION LEVEL
  
SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

READ UNCOMMITTED

Read uncommitted specifies that current SQL statements can read rows data that have been modified by some other transactions but not yet committed.

Transactions running at the READ UNCOMMITTED level do not acquire shared locks to prevent other transactions from altering data read by the current transaction.

READ UNCOMMITTED transactions are also not hindered 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 enabled, it is possible to read uncommitted modifications, which are referred to as dirty reads. Values in the data can be altered and rows can appear or disappear in the data set before the end of the transaction. This option has the same impact as setting NOLOCK on all tables in all SELECT statements within a transaction. This is the least restrictive of the isolation levels.

In SQL Server, you can also minimize contention caused by locking while safeguarding/protecting transactions against dirty reads of uncommitted data modifications by using the following:

1. The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

READ COMMITTED

This option is the SQL Server default option which specifies that statements cannot access data that has been altered but not yet committed by other transactions. This prevents reading of uncommitted data. Data can be modified by other transactions between separate statements within the ongoing transaction, leading to inconsistent reads or fictional data.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT options in database. It can be ON or OFF as per the requirement.

a) READ_COMMITTED_SNAPSHOT is set to OFF

It is the default option and in this the database engine utilizes mutual locks to hinder other transactions from altering rows while the ongoing transaction is executing a read operation. The mutual locks also impede the statement from accessing rows modified by other transactions until the other transaction is finished. The mutual lock category determines when it will be relinquished. Row locks are relinquished prior to processing the subsequent row. Page locks are relinquished upon reading the next page, and table locks are relinquished upon completion of the statement.

b) READ_COMMITTED_SNAPSHOT is set to ON

This is the default option for Azure SQL Database. Under this option the Database Engine employs row versioning to provide each statement with a transactionally consistent snapshot of the data as it appeared at the beginning of the statement. Locks are not utilized to safeguard/protect the data from updates by other transactions.

Under this option you can utilize the ReadCommittedLock table which give the hit to demand shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.

REPEATABLE READ

REPEATABLE READ specifies that SQL statements cannot access data that has been altered but not yet committed by other transactions and that no other transactions can alter data that has been accessed by the current transaction until the current transaction is finished.

Shared locks are applied to all data accessed by each statement in the transaction and are maintained until the transaction is completed. This prevents other transactions from modifying any rows that have been accessed by the current transaction. Other transactions can insert new rows that meet the search conditions of statements issued by the current transaction. If the current transaction, then repeats the statement, it will retrieve the new rows, resulting in phantom reads. Because shared locks are held until 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.

SNAPSHOT

Snapshot specifies that information read by any statement in a transaction will be the transactionally consistent version of the information that existed at the beginning of the transaction.

The transaction can only acknowledge information modifications that were committed before the start of the transaction. Information modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The impact is as if the statements in a transaction get a snapshot of the committed information as it existed at the beginning of the transaction.

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

During the roll-back phase of a database recovery, SNAPSHOT transactions will request a lock if an attempt is made to read information that is locked by another transaction that is being rolled back. The SNAPSHOT transaction is blocked until that transaction has been rolled back. The lock is released immediately after it has been granted.

SERIALIZABLE

Serializable consist of the followings.

• Statements cannot access 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 prevents 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 again, they will access the same set of rows. The range locks are held until the transaction finishes. This is the most restrictive of the isolation levels because it locks entire ranges of keys and holds the locks until the transaction finishes. 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.

SQL Server SET TRANSACTION isolation level in stored procedure

If you set the isolation level within a procedure, the isolation level will revert to its previous level once the procedure finishes. The available options are: READ UNCOMMITTED – this will Allows dirty reads, meaning data can be read from a transaction that is not yet complete. Hance it will be limited to the scope of stored procedure.

WITH (NOLOCK) vs SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

Both of these are the same thing. If you use the set transaction isolation level statement, it will apply to all the tables in the present connection, so if you only want a [nolock] on one or two tables use that in your query with table otherwise can use TRANSACTION ISOLATION LEVEL.

Hence, both will give you dirty reads. If one is okay with that, then can use this. If you don’t want dirty reads, then you can consider snapshot or serializable hints too.

• NOLOCK is local to the table or views etc.
• READ UNCOMMITTED is per session/connection.