SQL Server Isolation Levels

                                                                    SQL Server Locks - Part I, Part II, Part III, Part IV

In previous two posts we have been discussing SQL Server locking mechanism and how deadlocks can occur. Today we will talk about Isolation Levels. Different Isolation levels allow for various levels of concurrent operations that can be performed on the same data.

Isolation is the level to which one operation / transaction should be isolated from the other operation.

Serialization is an important concept in database management systems. The serialization allows concurrent transactions to run one after another serially. For example, if one transaction is running a read operation on a set of data, and another concurrent operation is running an update on the same data, both operations will be serialized in order the requests were received by the database management engine. For example, if read operation was requested first, a shared lock will be issued to this transaction and any other update/delete operations will be blocked or queued. Once the read operation is complete, an update lock will be issued to the update operation next in line which will read the data and then receive an exclusive lock to update the data.

But what if you don't really need this level of isolation? For example, you want to read the data even though the data may be exclusively locked by another transaction? You can achieve this by using a lower level transaction or by using locking hints (we will discuss locking hints in future post).

There are following types of isolation levels according to SQL-92 specifications.

Read Uncommitted - This is the lowest level of isolation. In this level, transactions are isolated only enough to ensure that corrupt data is not read. This level allows dirty reads, i.e. the data not yet committed.

Read Committed - This is the default level. Only committed data will be read. When a transaction has an exclusive lock, all other transactions requesting shared lock will be blocked

Repeatable Read - Repeatable read locks all the rows that it touches unlike read committed. Let's explain this with an example.

Let's say you are running a cursor where you are fetching one row at a time and then adding them to a temporary table (see below)

    DECLARE @tmpTable TABLE (FirstName varchar(50), LastName varchar(50),
                                                            Address varchar(255)

    DECLARE @customerID INT

    DECLARE CURSOR curCustomer READ_ONLY FOR

    SELECT CustomerID FROM Customers

    OPEN curCustomer

    FETCH NEXT FROM curCustomer INTO @customerID

    WHILE @@FETCH_STATUS=0

          BEGIN

              INSERT INTO @tmpTable(FirstName,LastName,Address)

              SELECT FirstName,LastName,Address FROM Customers
              WHERE   CustomerID=@customerID

              FETCH NEXT FROM curCustomer INTO @customerID

          END

    CLOSE curCustomer

    DEALLOCATE curCustomer

If you run this transaction at read committed level, only one row will be locked for reading at a time and then the lock will be released. Imagine you read the first row where address was "123, Roswell Road, Atlanta, GA, 30330" and insert this row in tmpTable. Now you lock the second row to read and insert into @tmpTable. Now imagine another concurrent transaction locked the first row and updated the address to "567, Peachtree Road, Atlanta, GA 30101". The record in your temp table is now incorrect.

If you run this transaction under repeatable read, this isolation level will lock the 1st row and then the second row and then the third row and so on, but will not release any lock until the entire transaction is complete. This ensures that the data read cannot be updated while the transaction is still running. While this promotes better data integrity, concurrency suffers because all other transactions must now wait for the resources to be released, resulting in timeouts.

Although repeatable read prevents data integrity, phantom inserts/updates/deletes are still possible. If one transaction reads the data more than once, then the rows are locked as they are read first time and will then be released. But, before the data is read second time, another transaction may insert a new row or update/delete rows that were read previously, resulting in phantom data.  

Serialization Level - Serialization level works by using key range locks. Using the same example as above, when this isolation is read, entire key range (in this case entire table since we are reading all records in customer table) will be locked until the entire transaction completes, which will prevent phantom rows since the other transactions must wait until this transaction ends. Serialization is the highest isolation level and while it promotes data integrity, it reduces concurrency. To know more about key range locks, refer back to my post about SQL Server Locks.

In addition to these isolation levels, SQL Server supports two additional isolation levels.

READ_COMMITTED_SNAPSHOT - This isolation level uses row versioning instead of shared lock when reading the data. Read operations only require SCH-S table level locks (i.e. to prevent DDL modifications) but no page or row level locks are needed. When reading rows modifed by another transaction, only the version of the row that existed when the transaction started is read. This only works when another isolation level "ALLOW_SNAPSHOT_ISOLATION" option is ON. 

You can set isolation level by using the following syntax...

    SET TRANSACTION ISOLATION LEVEL

    {
        READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ
        | SNAPSHOT |  SERIALIZABLE

     }

For Example,

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

    GO

    DECLARE @tmpTable TABLE (FirstName varchar(50), LastName varchar(50),
                                                           Address varchar(255)

    DECLARE @customerID INT

    DECLARE CURSOR curCustomer READ_ONLY FOR

    SELECT CustomerID FROM Customers

    OPEN curCustomer

    FETCH NEXT FROM curCustomer INTO @customerID

    WHILE @@FETCH_STATUS=0

          BEGIN

              INSERT INTO @tmpTable(FirstName,LastName,Address)

              SELECT FirstName,LastName,Address FROM Customers
              WHERE CustomerID=@customerID

              FETCH NEXT FROM curCustomer INTO @customerID

          END

    CLOSE curCustomer

    DEALLOCATE curCustomer

    GO

You can set ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT option at the database level by using the following script...

    ALTER DATABASE <DBName>

    SET

    {
      ALLOW SNAPSHOT_ISOLATION {ON | OFF}

       | READ_COMMITTED_SNAPSHOT {ON | OFF}

    }

Hopefully this post will help you determine the best isolation environment if you must change it. Although, SQL Server defaults work in most cases.

Thank you!