SQL Server isolation levels by Example

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

SQL Server supports the following isolation levels

• Read UNCOMMITTED
• Read Committed (the default)
• Repeatable Read
• Serializable
• Snapshot

Before I run through each of the these in detail if 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 intupdate te) INSERT into Isolationtests (COL1,COL2,COL3) Select All-in-a UNION ALL select-All-in-a UNION ALL  Select.  Select All-in-a UNION ALL select the union ALL select the union ALL select-  

Also before we go any further it's important to understand these ....

1. Dirty Reads–this is while you read the uncommitted data when doing this there are no guarantee that data read would ever be Co mmitted meaning the data could well is bad.
2. Phantom Reads–this is when data, is working with have been changed by another transaction since you first read I T in. This means subsequent reads of this data in the same transaction could well is different.

Read UNCOMMITTED

The lowest isolation level there is. READ UNCOMMITTED causes no shared locks to being requested which allows you to read data that's currently being modified in Other transactions. It also allows other transactions to modify data is reading.

As can probably imagine this can cause some unexpected results in a variety of different ways. For example data returned by the select could is in a half-in-a-state if a update is running in another transaction Causi ng Some of your rows to come back with the updated values and some not to.

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

Query1

BEGIN TRAN  UPDATE isolationtests SET Col1 = 2  --simulate have some intensive processing here with a waitwaitfor DELAY ' 00:00:10 '  ROLLBACK

Query2

SET TRANSACTION Isolation Level READ UNCOMMITTED  SELECT * from isolationtests

Notice that Query2 would not wait for Query1-to-finish, also more importantly Query2 returns dirty data. Remember Query1 rolls back all its changes however Query2 have returned the data anyway, this was because it didn ' t wait for All of the other transactions with exclusive locks in this data it just returned what is 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

The default isolation level and means selects would only return committed data. Select statements would issue shared lock requests against data you ' re querying this causes your to wait if another transact Ion already has a exclusive lock on that data. Once you have your GKFX lock any other transactions trying to modify that data would request an exclusive lock and be mad E to wait until your Read Committed transaction finishes.

You can see an example of a read transaction waiting for a modify transaction to complete before returning the data by run Ning the following Queries in separate tabs as do with Read uncommitted.

Query1

BEGIN TRAN  UPDATE Tests SET Col1 = 2  --simulate have some intensive processing here with a waitwaitfor DELAY ' 00 : 00:10 '  ROLLBACK

Query2

SELECT * from Isolationtests

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

Repeatable Read

This was similar to Read Committed but with the additional guarantee so if you issue the same select twice in a transacti On you'll get the same results both times. It does the holding on the shared locks it obtains on the records it reads until the end of the transaction, this m EANs any transactions this try to modify these records is forced to wait for the read transaction to complete.

As before run Query1 then and its running run Query2

Query1

SET TRANSACTION Isolation level repeatable READ  BEGIN TRAN  SELECT * from isolationtests  WAITFOR DELAY ' 00:00: Ten '  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 were forced to wait for Query1 to finish due to the exclusive locks that were opened as yo U specified repeatable Read.

If you rerun the above Queries but change Query1 to Read Committed you'll notice the both selects return different data a nd, Query2 does not wait for Query1 to finish.

One last thing to know on repeatable Read is, the data can change between 2 queries if more records is added. Repeatable Read guarantees records queried by a previous select is not being changed or deleted, it does not stop new Recor DS being inserted so it's 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 would be added eradicating the chance of Getting Phantom Reads. It does the placing range locks on the queried data. This causes any and transactions trying to modify or inserts data touched on by the transaction to wait until it has fin Ished.

You know the "drill by" 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 inserts 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 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 the-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 if data is changed the old version was kept in tempdb so existing transactions would see The version without the change. When all transactions this started before the changes is complete the previous row version is removed from tempdb. This means that even if another transaction have made changes you'll always get the same results as do the first Tim E in that transaction.

So on the plus side your not blocking anyone else from modifying the data whilst you run your transaction .... You ' re using extra resources on the SQL Server to hold multiple versions of your changes.

To use the snapshot isolation level 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'll notice that you still get The same data returned but Query2 no longer waits for Query1 to complete.

Summary

You should now has a good idea how each of the different isolation levels work. You can see how the higher the level your use of the less concurrency you is offering and the more blocking your bring to the Table. You should always try-to-use the lowest isolation level you can which are usually read committed.

SQL Server isolation levels by Example