BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Transaction Isolation Levels

Permitting concurrent transactions in database management systems is essential for performance but introduces the possibility of reading inconsistent data. To balance concurrency problems and performance, SQL Server has four transaction isolation levels.

Transaction Isolation Levels

The ANSI standard for SQL DBMS's describes four transaction isolation levels, all of which are supported by Microsoft SQL Server. These isolation levels control the types of locking that are applied during transactions, allowing or eliminating the concurrency problems described earlier. The isolation level names are listed below. The first item is the most permissive, using the fewest number of locks and allowing all of the stated concurrency problems. As you go down the list, progressively more restrictive locking is used, lowering the risk of concurrency issues but also lowering the average performance.

  • Read Uncommitted
  • Read Committed
  • Repeatable Read
  • Serializable

An isolation level can be set using the Transact-SQL (T-SQL) statement, SET TRANSACTION ISOLATION LEVEL. We'll demonstrate this command with a sample database table containing four rows. To initialise the table, create a new database and run the following script:

CREATE TABLE People
(
    Name VARCHAR(20)
)
INSERT INTO People VALUES('Bob')
INSERT INTO People VALUES('Jim')
INSERT INTO People VALUES('Mel')
INSERT INTO People VALUES('Sam')

Read Uncommitted

The least restrictive of the transaction isolation levels is read uncommitted. When using this option, a transaction may read information that has been changed by other, uncommitted transactions. It means that it is possible to encounter dirty reads, non-repeatable reads and phantom reads. However, if these do not cause a problem in your specific scenario, you can use the read uncommitted level to ensure that you achieve the best performance and the highest levels of concurrency.

We can demonstrate a dirty read by executing two transactions concurrently. This can be achieved by running SQL Server Management Studio (SSMS) and opening two query windows. In the first window, start a new transaction and make an update to the sample table. In the script below, the Name value of every row is changed to "Ben". This may suggest an error in the UPDATE statement. Note that the transaction is not committed.

-- Transaction 1
BEGIN TRAN
UPDATE People SET Name = 'Ben'

In the second window, set the isolation level for the transaction to read uncommitted and select all of the rows from the table using the following code. You will see that each row shows the name, "Ben", despite this update not being committed.

-- Transaction 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM People

You can now roll back the first transaction.

-- Transaction 1
ROLLBACK TRAN

Read Committed

Read committed is the default isolation level for SQL Server transactions. It prevents the current transaction reading rows that have been updated in another, uncommitted transaction. If you try the previous example using the read committed isolation level you will find that the query in transaction 2 is blocked until the update is committed or rolled back.

When using read committed, non-repeatable reads and phantom reads are still possible. We can demonstrate a non-repeatable read with two concurrent transactions. In the first, set the transaction isolation level, start a new transaction and execute the query below:

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN
SELECT * FROM People

In the second SSMS window, update the information that has just been read.

-- Transaction 2
UPDATE People SET Name = 'Ben'

Now switch back to the first transaction and execute the same query for a second time. You should see that the updated information is now returned, rather than the original results, which would be present if the transaction was serializable.

-- Transaction 1
SELECT * FROM People

As the data has now been changed, roll back any active transactions, drop the People table and recreate and repopulate it using the initial script.

Repeatable Read

The repeatable read isolation level uses additional locking on rows that are read by the current transaction, preventing them from being updated elsewhere. This removes the possibility of non-repeatable reads. If you try the previous example using the more restrictive isolation level, you will find that the UPDATE statement is blocked until the transaction performing selections is committed or rolled back.

With the repeatable read transaction isolation level it is still possible to encounter phantom reads. We can demonstrate this with another pair of transactions. Start by setting the isolation level and querying the table as shown below:

-- Transaction 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM People

In the second window, insert a new row into the People table using the statement below:

-- Transaction 2
INSERT INTO People VALUES ('Tim')

If you now run the query in the first transaction again, you will see that the new row is returned. This is a phantom read.

-- Transaction 1
SELECT * FROM People

Reset the table and data again for the next example.

5 August 2012