SQL Server Isolation Levels

SQL Server Isolation Levels with examples

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

SQL Server 2008 supports the following isolation levels

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

Isolation level	Dirty read	Nonrepeatable read	Phantom read	Lock
Read uncommitted	✔	✔	✔	No Lock
Read committed	✗	✔	✔	Shared Lock
Repeatable read	✗	✗	✔	Shared Lock
Serializable	✗	✗	✗	Range locks
Snapshot	✗	✗	✗	row versioning

Before I run through each of these in detail you may want to create a new database to run the examples, run the following script on the new database to create the sample data. Note: You’ll also want to drop the IsolationTests table and re-run this script before each example to reset the data.

CREATE TABLE IsolationTests 

(

    Id INT IDENTITY,

    Col1 INT,

    Col2 INT,

    Col3 INT

)

INSERT INTO IsolationTests(Col1,Col2,Col3) 

SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3 

UNION ALL SELECT 1,2,3

Also before we go any further it is important to understand these two terms….

1. Dirty Reads – When a transaction is allowed to read a row that has been modified by an another transaction which is not committed yet that time Dirty Reads occurred. It is mainly occurred because of multiple transaction at a time which is not committed.

Explanation –
If we have a ticket booking system and One Customer is trying to book a ticket at that time available number of the ticket is 10, before completing the payment, the Second Customer wants to book a ticket that time this 2nd transaction will show the second customer that the number of the available tickets is 9. The twist is here if the first customer does not have sufficient fund in his debit card or in his wallet then the 1st transaction will Rollback, that time 9 seat available which is read by the 2nd transaction is Dirty Read

2. Phantom Reads – Phantom Read occurs when two same queries are executed, but the rows retrieved by the two are different. For example, suppose transaction T1 retrieves a set of rows that satisfy some search criteria. Now, Transaction T2 generates some new rows that match the search criteria for transaction T1. If transaction T1 re-executes the statement that reads the rows, it gets a different set of rows this time.
3. Non Repeatable read – Non Repeatable read occurs when a transaction reads same row twice, and get a different value each time. For example, suppose transaction T1 reads data. Due to concurrency, another transaction T2 updates the same data and commit, now if transaction T1 rereads the same data, it will retrieve a different value.

Read Uncommitted

Read Uncommitted is the lowest isolation level. In this level, one transaction may read not yet committed changes made by other transaction, thereby allowing dirty reads. In this level, transactions are not isolated from each other.

To see read uncommitted in action lets run Query1 in one tab of Management Studio and then quickly run Query2 in another tab before Query1 completes.

--Query1

BEGIN TRAN 

UPDATE IsolationTests SET Col1 = 2 

--Simulate having some intensive processing here with a wait

WAITFOR DELAY '00:00:10' 

ROLLBACK

--Query2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT * FROM IsolationTests

Notice that Query2 will not wait for Query1 to finish, also more importantly Query2 returns dirty data. Remember Query1 rolls back all its changes however Query2 has returned the data anyway, this is because it didn't wait for all the other transactions with exclusive locks on this data it just returned what was there at the time.

There is a syntactic shortcut for querying data using the read uncommitted isolation level by using the NOLOCK table hint. You could change the above Query2 to look like this and it would do the exact same thing.

SELECT * FROM IsolationTests WITH(NOLOCK)

Read Committed

This isolation level guarantees that any data read is committed at the moment it is read. Thus it does not allows dirty read. The transaction holds a read or write lock on the current row, and thus prevent other transactions from reading, updating or deleting it

You can see an example of a read transaction waiting for a modify transaction to complete before returning the data by running the following Queries in separate tabs as you did with Read Uncommitted.

--Query1

BEGIN TRAN 

UPDATE IsolationTests SET Col1 = 2 

--Simulate having some intensive processing here with a wait

WAITFOR DELAY '00:00:10' 

ROLLBACK

--Query2

SELECT * FROM IsolationTests

Notice how Query2 waited for the first transaction to complete before returning and also how the data returned is the data we started off with as Query1 did a rollback. The reason no isolation level was specified is because Read Committed is the default isolation level for SQL Server. If you want to check what isolation level you are running under you can run “DBCC useroptions”. Remember isolation levels are Connection/Transaction specific so different queries on the same database are often run under different isolation levels.

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. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction, this means any transactions that try to modify these records are forced to wait for the read transaction to complete.

This is the most restrictive isolation level. The transaction holds read locks on all rows it references and writes locks on all rows it inserts, updates, or deletes. Since other transaction cannot read, update or delete these rows, consequently it avoids non-repeatable read

As before run Query1 then while its running run Query2

--Query1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 

BEGIN TRAN 

SELECT * FROM IsolationTests 

WAITFOR DELAY '00:00:10' 

SELECT * FROM IsolationTests 

ROLLBACK 

--Query2

UPDATE IsolationTests SET Col1 = -1

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.

If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.

One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.

Serializable

This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.

Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock. If table has index then it locks records based on index range used in WHERE clause (like where ID between 1 and 3). If table doesn't have index then it locks complete table.

You know the drill by now run these queries side by side…

--Query1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 

BEGIN TRAN 

SELECT * FROM IsolationTests 

WAITFOR DELAY '00:00:10' 

SELECT * FROM IsolationTests 

ROLLBACK 

--Query2

INSERT INTO IsolationTests(Col1,Col2,Col3) 

VALUES (100,100,100)

You’ll see that the insert in Query2 waits for Query1 to complete before it runs eradicating the chance of a phantom read. If you change the isolation level in Query1 to repeatable read, you’ll see the insert no longer gets blocked and the two select statements in Query1 return a different amount of rows.

Snapshot

This provides the same guarantees as serializable. So what's the difference? Well it’s more in the way it works, 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. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

So 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 IsolationTests 

SET ALLOW_SNAPSHOT_ISOLATION ON

If you rerun the examples from serializable but change the isolation level to snapshot you will notice that you still get the same data returned but Query2 no longer waits for Query1 to complete.

To check the isolation level

      dbcc useroptions

To set the isolation level

          SET TRANSACTION ISOLATION LEVEL <requiredisolationlevel>