Isolation Levels in SQL Server 2005

Serializable Isolation Level

This is the highest Isolation Level and it avoids all the concurrency related problems. The behavior of this level is just like the Repeatable Read with one additional feature. It obtains key range locks based on the filters that have been used. It locks not only current records that stratify the filter but new records fall into same filter. Change the stored procedure we used for above sample and set the Isolation Level as Serializable.

IF OBJECT_ID(N'dbo.AddBirthdayAllowance', N'P') IS NOT NULL

BEGIN

        DROP PROC dbo.AddBirthdayAllowance

END

GO

CREATE PROC dbo.AddBirthdayAllowance

AS

BEGIN

        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

        BEGIN TRAN

                -- inserts records to allowances table

                INSERT INTO Allowances

                        (EmployeeID, MonthAndYear, Allowance)

                SELECT EmployeeID, getdate(), 100.00

                FROM dbo.Employees

                WHERE IsBirthdayAllowanceGiven = 0

                        AND MONTH(BirthDate) = MONTH(getdate())

Run the clean up code again to bring the Employees table to the original state.

Now test the stored procedure and INSERT statement with two connections. You will notice that INSERT operation is blocked until Connection1 completes the transaction, avoiding Phantom Reads.

Run the clean up code again and drop the new table Allowances and added column IsBirthdayAllowanceGiven in the Employees table.

Whenever we set the Isolation Level to a transaction, SQL Server makes sure that the transaction is not disturbed by other transactions. This is called concurrency control. All the Isolation Levels we discussed so far come under a control called Pessimistic Control. The Pessimistic control, SQL Server locks the resource until user performs the action she/he needs and then release for others. The other concurrency control is Optimistic Control. Under Optimistic Control, SQL Server does not hold locks but once read, check for inconsistency for next read. The two newly introduced Isolation Levels with SQL Server 2005 are Snapshot and Read Committed Snapshot. These two Isolation Levels provide Optimistic Control and they use Row Versioning.

Snapshot Isolation Level

The Snapshot Isolation Level works with Row Versioning technology. Whenever the transaction requires a modification for a record, SQL Server first stores the consistence version of the record in the tempdb. If another transaction that runs under Snapshot Isolation Level requires the same record, it can be taken from the version store. This Isolation Level prevents all concurrency related problems just like Serializable Isolation Level, in addition to that it allows multiple updates for same resource by different transactions concurrently.

Since there is a performance impact with Snapshot Isolation Level it has been turned off by default. The impact is explained below with the sample. You can enable it by altering the database.

ALTER DATABASE Northwind SET ALLOW_SNAPSHOT_ISOLATION ON

Let’s look at a simple sample. Make sure you have enabled Snapshot Isolation Level in the database before running below query. Open a new connection (Connection1) and execute query below;

USE Northwind

BEGIN TRAN

        -- update the HireDate from 5/1/1992 to 5/2/1992

        UPDATE dbo.Employees

                SET HireDate = '5/2/1992'

        WHERE EmployeeID = 1

Now open the second connection (Connection2) and try to retrieve the same record.

SELECT *

FROM dbo.Employees

WHERE EmployeeID = 1

As you have seen with examples discussed under other levels, the record cannot be retrieved. Since we have enabled Snapshot Isolation Level in the database, SQL Server stores version of the record. Use below dynamic management view for retrieving versions stored in the store.

SELECT * FROM sys.dm_tran_version_store;

You will see one record in the store. Now set the Isolation Level of the Connection2 as Snapshot and try to retrieve the record.

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

        SELECT *

        FROM dbo.Employees

        WHERE EmployeeID = 1

This returns record from the store that was the last consistence version of the record. Note that HireDate of the employee is 05/01/1992 not 05/02/1992. Now go back to the Connection1 and commit the transaction.

COMMIT TRAN

Again open the Connection2 and execute the query. Note that even though the Connection1 has committed the change, Connection2 still gets the older record. This is because it was the consistence record in the version store when the Connection2 started the transaction and the same version is read during the transaction. SQL Server keeps this version of the record until no reference for it. If another transaction starts changing same record, another version will be stored and goes on; results longer link list in the version store. Maintaining longer link list and traversing through list will impact the performance. Committing the transaction in Connection2 will remove the reference for the first version and the first version in the store will be removed from separate clean-up process.

There is another great feature with Snapshot Isolation Level. It is Conflict Detection. One transaction reads a record from the version store and later tries to update the record.  Another transaction updates the same record before previous transaction’s update. This conflict detects by the SQL Server and aborts the previous transaction.

Open a connection (Connection1) and run the below query. The update statement causes to add the current consistence version to the version store.

USE Northwind

BEGIN TRAN

       -- update the HireDate from 5/1/1992 to 5/2/1992

       UPDATE dbo.Employees

              SET HireDate = '5/2/1992'

       WHERE EmployeeID = 1

Open the second connection (Connection2) and read the same record. Note the Isolation Level.

USE Northwind

GO

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

       SELECT *

       FROM dbo.Employees

       WHERE EmployeeID = 1

Go back to Connection1 and commit the transaction.

COMMIT TRAN

Go back to Connection2 and try to update the record. Note that the current transaction still runs. Whenever you execute the UPDATE statement, SQL Server detects the modification that has been done by Connection1 in between read and write, it throws an error.

UPDATE dbo.Employees

       SET HireDate = '5/3/1992'

WHERE EmployeeID = 1

Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Employees' directly or indirectly in database 'Northwind' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Once the conflict is detected, it terminates the transaction in Connection2. Though this Isolation Level has some great advantageous, this level is not recommended for a database that has many updates. This is suitable for database that is mainly used for read data with occasional updates.