Close

Isolation Levels in SQL Transaction

Related to the shared database pattern are isolation levels in SQL Server. It’s very old stuff, more of a recap really. 

SQL Server supports the following isolation levels

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

Before we go any further it is important to understand these two terms:

  • Dirty Reads – A dirty read occurs when one transaction is permitted to read data that is being modified by another transaction that is running concurrently but which has not yet committed itself. If the transaction that modifies the data commits itself, the dirty read problem doesn’t occur. However if the transaction that modifies the data is rolled back after the other transaction has read the data, the latter transaction has dirty data that doesn’t actually exist.
  • Phantom Reads – In the above example, the problem does occur when a concurrent transaction commits data between two reads. In that case, subsequent reads will return different data. Let’s say: value x and value y. This is actually a special type of phantom read, that is: a non-repeatable read. Another example is query: count(*) from table. Let’s say this query first returns 43 and then 42. In this case, it’s not an unrepeatable read, because there’s no row retrieved twice with different column values.  Still, it’s a phantom read, because there are two different sets of rows counted (apparently with a row being deleted in the meantime).

Isolation levels:

Read Uncommitted

It allows other transactions to modify data that you are reading.

Query 1:
BEGIN TRAN
UPDATE Table SET Col1=2
WAITFOR DELAY ’00:00:10’
ROLLBACK

Query 2:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM Table

Because of the rollback in Query 1, we can get dirty reads. Query 2 simply returns the data that were their at the time. Query 1 places no exclusive lock on the data.

Read Committed (default)

Selects will only return committed data.

Query 1:
BEGIN TRAN
UPDATE Table SET Col1=2
WAITFOR DELAY ’00:00:10’
ROLLBACK

Query 2:
SELECT * FROM Table

Select statements like Query 2 will issue a shared lock request. Query2 will wait for Query 1 to complete, before running the select and retrieve the rolled back data. Query 1 places an exclusive lock on the data.

Repeatable Read

This is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times.

Query 1:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM Table
WAITFOR DELAY ’00:00:10’
SELECT * FROM Table
ROLLBACK

Query 2:
UPDATE Table SET Col1=2

Notice that Query1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.

Note: Repeatable Read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records from being inserted though.

Serializable

This isolation level is similar to Repeatable Read and adds the guarantee that no new data will be added.

Query 1:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRAN
SELECT * FROM Table
WAITFOR DELAY ’00:00:10’
SELECT * FROM Table
ROLLBACK

Query 2:
INSERT INTO Table (Col1,Col2,Col3) VALUES (100, 100, 100)

Insert in Query2 waits for Query1 to complete. If you change the isolation level in Query 1 to REPEATABLE READ, you’ll see the insert no longer gets blocked and the two select statements in Query 1 return a different amount of rows.

Snapshot

Provides the same guarantee as SERIALIZABLE, it just works differently. Using snapshot doesn’t block other queries from inserting or updating the data touched by the snapshot transaction. Instead row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. On the plus side your not blocking anyone else from modifying the data whilst you run your transaction but…. You’re using extra resources on the SQL Server to hold multiple versions of your changes.

To use the snapshot isolation level you need to enable it on the database by running the following command:

ALTER DATABASE Table
SET ALLOW_SNAPSHOT_ISOLATION ON