I recently had the article Locking, Blocking and Deadlocking published here on SQL Server Central. This article is continuing from there and discusses Transaction Isolation Levels, and how the selection of a transaction isolation level affects the locking mechanism previously discussed in the earlier article.

If we look at the Books Online (BOL) topic Isolation Levels in the Database Engine, we can see that a transaction isolation level controls:

• Whether locks are taken when data is read, and what type of locks are requested.
• How long the read locks are held.
• Whether a read operation referencing rows modified by another transaction:
  • Blocks until the exclusive lock on the row is freed.
  • Retrieves the committed version of the row that existed at the time the statement or transaction started.
  • Reads the uncommitted data modification.

Notice that these all affect only the reading of data. The locks acquired when writing data are not affected – these are still required to protect the data modifications. The transaction isolation level controls how the read operations are protected from the other (write) operations.

ISO Isolation Levels

The following table shows the different ISO isolation levels, and the concurrency side effects of them:

As we examine this table, we can see that the different transaction isolation levels are designed to eliminate the concurrency effects.

SQL Server 2005 adds two additional transaction isolation levels, both of which deal with utilizing snapshots:

READ_COMMITTED_SNAPSHOT is a database-level setting and if it is turned on and the transaction isolation level is read committed, then it will use row versioning to present a transactionally consistent view of the data at the time that the statement started.

The Snapshot isolation level also utilizes row versioning to present a transactionally consistent view of the data at the time that the statement started. This requires that the ALLOW_SNAPSHOT_ISOLATION database setting be turned on, and for the query to issue the SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement.

In both of these snapshot isolation levels, the effect is that readers don’t block writers, and writers don’t block readers. Furthermore, readers will not be able to read any in-flight data modifications from other transactions.

As I’ve already mentioned, both of these utilize row versioning. When using row versioning, the Database Engine within SQL Server will maintain versions of the rows being affected by transactions. Utilizing row-versioning will:

1. Eliminate shared locks on read transactions.
2. Reduce blocking (on read transactions).
3. Increases resources needed for data modifications.
4. Increases activity in tempdb (where the row versioning information is stored).
   1. All database data modifications will have row-versioning.
5. Each data record will have a 14 byte record suffix appended to it.

Concurrency Effects

The above charts mention several different concurrency side effects, so let’s explain each of these. These effects are defined in BOL at Concurrency Effects:

• Dirty Read (referred to in ISO as “Uncommitted Dependency”) occurs when a second transaction selects a row that is being updated by another transaction. A dirty read occurs when the modified data is being read before the other transaction actually commits the transaction that is modifying the data. If this transaction were to be rolled back, then the second transaction has just returned a row with data that does not exist in the database. This effect can be avoided by preventing the reading of data that is being changed.
• Nonrepeatable Read (referred to in ISO as “Inconsistent Analysis”) occurs when a transaction reads the same row multiple times, and the results are different between the different reads. This can occur when another transaction modifies and commits a change to a row. Whilst similar to a Dirty Read, the difference is that in Nonrepeatable Read the writing transaction has successfully committed the transaction, whereas in a Dirty Read the writing transaction is rolled back. This effect can be avoided by preventing the changing of data until the reading of data has been completed.
• Phantom Reads occurs when a transaction that is reading data is reading a range of data, and another transaction inserts or deletes a row. If the statement issued by the reading transaction were to be issued again, there would be additional rows returned (for the insert transaction), or fewer rows returned (for the delete transaction). This effect can be avoided by preventing transactions from inserting or deleting data while the data is being read.
• Missing / Double Reads occurs when:
  • A reading transaction is reading a range of rows in an index scan operation, and during the read a row is updated by a second transaction, changing the index key column(s) and thus its position in the scan. If the update moves a row from the end of the scan to the beginning, the reading transaction can miss reading that row; conversely if the update moves the row from the beginning of the scan to the end then that row can be read twice.
  • If a reading transaction in the READ UNCOMMITTED isolation level is performing an allocation order scan (which uses the IAM pages) and another transaction causes a page split, rows can be missed being read by the reading transaction.

As you read over these effects, you should be able to see that as you work to prevent any of these concurrency effects, you are creating more locking (and thus the potential for more blocking) in the database.

Examples of the concurrency effects

Let’s run some examples to see how these different concurrency effects manifest themselves in the different transaction isolation levels. All of these examples work by using two query windows; one will be running a read transaction while another runs a write transaction. The queries utilize “WAITFOR DELAY” to give you a little bit of time to start the one transaction, and switch to the other query window to run the other one.

First up is the database initialization code. This code needs to be run prior to running each of the tests. It is being put into a stored procedure so that it can be easily run as necessary.

IF DB_ID('IsolationLevelTest') IS NOT NULL BEGIN
    USE IsolationLevelTest;
    ALTER DATABASE IsolationLevelTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    USE master;
    DROP DATABASE IsolationLevelTest;
END;
CREATE DATABASE IsolationLevelTest;
GO
USE IsolationLevelTest;
GO
CREATE PROCEDURE dbo.db_reset AS
IF OBJECT_ID('dbo.IsolationTests','U') IS NOT NULL DROP TABLE dbo.IsolationTests;
CREATE TABLE dbo.IsolationTests (    
    Id      INTEGER IDENTITY,    
    ColA    CHAR(1));
INSERT INTO dbo.IsolationTests(ColA)
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A';
SELECT  *
FROM    dbo.IsolationTests;
IF EXISTS (SELECT 1 FROM sys.databases WHERE database_id = DB_ID('IsolationLevelTest') AND snapshot_isolation_state = 1)
    ALTER DATABASE IsolationLevelTest SET ALLOW_SNAPSHOT_ISOLATION OFF;
GO
EXECUTE dbo.db_reset;
GO

Read Uncommitted

In the Read Uncommitted isolation level, we will look at how dirty reads are allowed. This will be performed by starting a transaction in one query window that is performing an update, and concurrently in a second query window running a select statement in the read uncommitted transaction isolation level, so that the query will read the data being modified. After a period of time, the transaction in the first query window is rolled back. You will see that the second query window has returned data that was never committed to the table.

1. In the first query window, run these statements:

USE IsolationLevelTest;
GO
EXECUTE dbo.db_reset;
GO
BEGIN TRANSACTION;
UPDATE  dbo.IsolationTests
SET     ColA = 'Z';
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:10';
ROLLBACK;
GO
SELECT *
FROM dbo.IsolationTests;
GO

2. In the second query window, run these statements:

USE IsolationLevelTest;
GO
-- READ UNCOMMITTED
-- Run this in query window 2 while the 1st query is running
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM dbo.IsolationTests;

3. As you can see from the results, the second query returns immediately, and it returns the values that were subsequently rolled back in the first query window.

Read Committed

In the read committed test, we will rerun these statements. The second query window is set to use the read committed transaction isolation level. Hence, the select statement running in the second query window will have to wait until the first transaction is completed (the transaction is either committed or rolled back) before it can then read the data – it is being blocked by the open transaction.

1. In the first query window, run these statements:

USE IsolationLevelTest;
GO
EXECUTE dbo.db_reset;
GO
BEGIN TRANSACTION;
UPDATE  dbo.IsolationTests
SET     ColA = 'Y';
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:10';
ROLLBACK;

2. In the second query window, run these statements:

USE IsolationLevelTest;
GO
-- READ COMMITTED
-- Run this in query window 2 while the 1st query is running
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT * FROM dbo.IsolationTests;

3. As you can see, the statements in query window 2 have to wait for the transaction in query window 1 to complete before it can run, and query window 2 returns the values in the table after query window 1 completed.

Repeatable Read

For the next isolation level, repeatable read, we will show how a transaction in this isolation level that is reading the data from a table twice, with a period of time between the reads, will be required to return the same data. Under this isolation level, it must read the exact same data for the rows that were read, so it will be blocking a second transaction that is trying to update some of these rows. We will then change this from repeatable read to read committed to show the effects of allowing the update to run.

1. In the first query window, run these statements:

USE IsolationLevelTest;
GO
EXECUTE dbo.db_reset;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;
SELECT * FROM dbo.IsolationTests;
WAITFOR DELAY '00:00:10';
SELECT * FROM IsolationTests;
ROLLBACK;

2.  In the second query window, run these statements:

USE IsolationLevelTest;
GO
UPDATE dbo.IsolationTests SET Col1 = -1;

3. Notice that query window 2 waits until query window 1 is finished, because query window 1 is in repeatable read.
4. Rerun steps 1-3:
   1. Change query window 1 to use the READ COMMITTED isolation level, and run the code.
   2. Run the code in query window 2.
5. Notice that query window 2 completed immediately, and that in query window 1 the second select statement returned different results from the first select statement.

Serializable

In the repeatable read test just performed, we saw how updates to data were prevented. The serializable isolation level takes this one step further, and also prevents inserts or deletes from occurring to this table. To test this, we will essentially rerun the test from repeatable read, and we will change the isolation level to serializable and attempt to perform an insert instead of an update. We will then run this test in the repeatable read isolation level, showing how that does allow the insert to run.

1. In the first query window, run these statements:

USE IsolationLevelTest;
GO
EXECUTE dbo.db_reset;
GO
-- SERIALIZABLE
-- Run this in query window 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
--SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- what will happen if this is set instead?
BEGIN TRANSACTION;
SELECT * FROM dbo.IsolationTests;
WAITFOR DELAY '00:00:10';
SELECT * FROM dbo.IsolationTests;
ROLLBACK;

2. In the second query window, run these statements:

USE IsolationLevelTest;
GO
INSERT INTO dbo.IsolationTests(ColA)
VALUES ('W');

3. Notice that the insert in query window 2 waits until the transaction in query window 1 is completed.
4. Rerun steps 1-3:
   1. Change query window 1 to use the REPEATABLE READ isolation level, and run the code.
   2. Run the code in query window 2.
5. Notice that the insert in query window 2 runs immediately, and that the second select statement in query window 1 returns the inserted row.

Snapshot

The read committed / uncommitted  transaction levels that we have looked at also have the issue of having missing / double reads. The repeatable read / serializable isolation levels eliminate this problem, but do so under the penalty of severe blocking of other transactions. The snapshot isolation levels eliminate all of the same concurrency side effects that the serializable isolation level does, and additionally does this without introducing locking (thus eliminating blocking). In this test, we will first show the absence of blocking in the snapshot isolation level, and then show how these same statements are blocked when using the serializable isolation level.

1. In the first query window, run these statements:

USE IsolationLevelTest;
GO
EXECUTE dbo.db_reset;
GO
-- SNAPSHOT
ALTER DATABASE IsolationLevelTest SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
-- Run this in query window 1
USE IsolationLevelTest;
GO
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
SELECT * FROM dbo.IsolationTests;
WAITFOR DELAY '00:00:10';
SELECT * FROM dbo.IsolationTests;
ROLLBACK;

2. In the second query window, run these statements:

USE IsolationLevelTest;
GO
INSERT INTO dbo.IsolationTests(ColA)
VALUES ('X');
SELECT * FROM dbo.IsolationTests;

3. Notice that query window 2 completes immediately, but the data modification is not reflected in query window 1.
4. If you were to change query window 1 to utilize the serializable isolation level and rerun the tests, you would see that query window 2 will now be blocked and will now wait until query window 1 has been completed before it can insert the row.

How does the NOLOCK query hint fit it?

The table hint NOLOCK (which is the same as the table hint READUNCOMMITTED) is the same as specifying SET TRANSACTION LEVEL READUNCOMMITTED. You can see this by running the code for READ UNCOMMITTED, and for query window 2 run the following code instead:

SELECT * FROM dbo.IsolationTests WITH (NOLOCK);

If you decide to implement snapshot isolation, and your current code is utilizing the NOLOCK (or READUNCOMMITTED) table hints, these specified hints will have precedence – you will need to change the code to obtain the benefits of utilizing the snapshot isolation levels.

Summary

Under the ISO isolation levels, as we change the isolation level that a query runs in away from the SQL Server default of READ COMMITTED, we are either reducing locks (but allowing for the reading of dirty data), or increasing the locking involved to minimize the concurrency effects. The snapshot isolation levels eliminate all of the concurrency effects while maintaining zero blocking on read transactions, but since nothing comes free, you pay the price with increased tempdb activity and increased storage space requirements in the user database and tempdb. That being said, I feel that if you are using read uncommitted (or nolock), that you should switch to using the read committed snapshot isolation level instead to achieve the no blocking you are trying to achieve for that query.

References

• Isolation Levels in the Database Engine (BOL) - http://technet.microsoft.com/en-us/library/ms189122%28v=sql.105%29.aspx
• SET TRANSACTION ISOLATION LEVEL (BOL) - http://technet.microsoft.com/en-us/library/ms173763%28v=sql.105%29.aspx
• Concurrency Effects (BOL) - http://technet.microsoft.com/en-us/library/ms190805%28v=sql.105%29.aspx
• Row Versioning Resource Usage (BOL) - http://technet.microsoft.com/en-us/library/ms175492%28v=sql.105%29.aspx