SQL Server Isolation Levels and Locks

We will learn about SQL Server Isolation Levels and Locks. Isolation levels come into play when you need to isolate a resource for a transaction and protect that resource from other transactions. The protection is done by obtaining locks. What locks need to be set and how it has to be established for the transaction is determined by SQL Server referring to the Isolation Level that has been set. Lower Isolation Levels allow multiple users to access the resource simultaneously (concurrency) but they may introduce concurrency related problems such as dirty-reads and data inaccuracy. Higher Isolation Levels eliminate concurrency related problems and increase the data accuracy but they may introduce blocking.

Note that first four Isolation Levels described below are ordered from lowest to highest. The two subsequent levels are new to SQL Server 2005, and are described separately.

 SQL Server Isolation Levels and Locks

à Read Uncommitted Isolation Level

à Read Committed Isolation Level

à Repeatable Read Isolation Level

à Serializable Isolation Level

à Snapshot Isolation Level

à Read Committed Snapshot Isolation Level

Read Uncommitted Isolation Level

This is the lowest level and can be set, so that it provides higher concurrency but introduces all concurrency problems; dirty-reads, Lost updates, Nonrepeatable reads (Inconsistent analysis) and phantom reads. This Isolation Level can be simply tested.

Connection1 opens a transaction and starts updating Employees table.

USE Northwind

BEGINTRAN

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

UPDATE dbo.Employees

SET HireDate =’5/2/1992′

WHERE EmployeeID = 1

Connection2 tries to read same record.

USE Northwind

SELECT HireDate

FROM dbo.Employees

WHERE EmployeeID = 1

You will see that Connection2 cannot read data because an exclusive lock has been set for the resource by Connection1. The exclusive locks are not compatible with other locks. Though this reduces the concurrency, as you see, it eliminates the data inaccuracy by not allowing seeing uncommitted data for others. Now let’s set the Isolation Level of Connection2 to Read Uncommitted and see.

USE Northwind

SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED

SELECT HireDate

FROM dbo.Employees

WHERE EmployeeID = 1

— results HireDate as 5/2/1992

As you expected, Connection2 can see the record that is being modified by Connection1 which is an uncommitted record. This is called dirty-reading. You can expect higher level of concurrency by setting the Isolation Level to Read Uncommitted but you may face all concurrency related problems. Imagine the consequences when Connection1 rolls back the transaction but Connection2 makes a decision from the result before the roll back.

Read Committed Isolation Level

This is the default Isolation Level of SQL Server. This eliminates dirty-reads but all other concurrency related problems. You have already seen this. Look at the sample used above. Connection2 could not read data before the Isolation Level was set to Read Uncommitted. That is because it had been set to the default Isolation Level which is Read Committed which in turn disallowed reading uncommitted data. Though it stops dirty-reads, it may introduce others. Let’s take a simple example that shows Lost Updates.

Employee table contains data related to employee. New employee joins and record is made in the table.

USE Northwind

INSERTINTO dbo.Employees

(LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate)

VALUES

(‘Lewis’,’Jane’,’Sales Representative’,’Ms.’,’03/04/1979′,’06/23/2007′)

This table contains a column called Notes that describes the employee’s education background. Data entry operators fill this column by looking at her/his file. Assume that the update code has been written as below. Note that no Isolation Level has been set, means default is set.

IFOBJECT_ID(N’dbo.UpdateNotes’, N’P’)ISNOTNULL

BEGIN

DROPPROC dbo.UpdateNotes

END

GO

CREATEPROCEDURE dbo.UpdateNotes @EmployeeID int, @Notes ntext

AS

BEGIN

DECLARE @IsUpdated bit

BEGINTRAN

SELECT @IsUpdated =CASEWHEN Notes ISNULLTHEN 0 ELSE 1 END

FROM dbo.Employees

WHERE EmployeeID = @EmployeeID — new record

— The below statement added to hold the transaction for 5 seconds

— Consider it is as a different process that do something else.

WAITFORDELAY’00:00:5′

IF(@IsUpdated = 0)

BEGIN

UPDATE dbo.Employees

SET Notes = @Notes

WHERE EmployeeID = @EmployeeID

END

ELSE

BEGIN

ROLLBACKTRAN

RAISERROR(‘Note has been alreasy updated!’, 16, 1)

RETURN

END

COMMITTRAN

END

Operator1 makes Connection1 and executes the following query.

EXEC dbo.UpdateNotes 15,’Jane has a BA degree in English .’

Within few seconds (in this case, right after Operator1 started) Operator2 makes Connection2 and executes the same with a different note, before completing the Operator1’s process.

EXEC dbo.UpdateNotes 15,’Jane holds a Bcom degree in English.’

If you query the record after both processes, you will see that note that was entered by the Operator2 has been set for the record. Operator1 made the update and no error messages were returned to it, but it has lost its update. This could be avoided if the record was locked and held as soon as it was identified as a not updated record. But obtaining and holding a lock is not possible with Read Committed Isolation Level. Because of this, concurrency related problems such as Lost Updates, Nonrepeatable reads and Phantom reads can happen with this Isolation Level.

Repeatable Read Isolation Level

This Isolation Level addresses all concurrency related problems except Phantom reads. Unlike Read Committed, it does not release the shared lock once the record is read. It obtains the shared lock for reading and keeps till the transaction is over. This stops other transactions accessing the resource, avoiding Lost Updates and Nonrepeatable reads. Change the Isolation Level of the stored procedure we used for Read Committed sample.

IFOBJECT_ID(N’dbo.UpdateNotes’, N’P’)ISNOTNULL

BEGIN

DROPPROC dbo.UpdateNotes

END

GO

CREATEPROCEDURE dbo.UpdateNotes @EmployeeID int, @Notes ntext

AS

BEGIN

DECLARE @IsUpdated bit

SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD

BEGINTRAN

SELECT @IsUpdated =CASEWHEN Notes ISNULLTHEN 0 ELSE 1 END

FROM dbo.Employees

WHERE EmployeeID = @EmployeeID — new record

Now make two connections and execute below queries just as you did with Read Committed sample. Make sure you set the Note column value back to NULL before executing them.

With Connection1;

EXEC dbo.UpdateNotes 15,’Jane has a BA degree in English.’

With Connection2;

EXEC dbo.UpdateNotes 15,’Jane has a Bcom degree in English.’

Once you execute the code with Connection2, SQL Server will throw 1205 error and Connection2 will be a deadlock victim. This is because, Connection1 obtain and hold the lock on the resource until the transaction completes, stopping accessing the resource by others, avoiding Lost Updates. Note that setting DEADLOCK_PRIORITY to HIGH, you can choose the deadlock victim.

Since the lock is held until the transaction completes, it avoids Nonrepeatable Reads too. See the code below.

SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD

BEGINTRAN

SELECT Notes

FROM dbo.Employees

WHERE EmployeeID = 10

It reads a record from the Employees table. The set Isolation Level guarantees the same result for the query anywhere in the transaction because it holds the lock without releasing, avoiding modification from others. It guarantees consistency of the information and no Nonrepeatable reads.

Now let’s take another simple example. In this case, we add one new table called Allowances and one new column to Employees table called IsBirthdayAllowanceGiven. The code for changes is as below;

USE Northwind

GO

— table holds allowances

CREATETABLE Allowances (EmployeeID int, MonthAndYear datetime, Allowance money)

GO

— additional column that tells whether the birthday allowance is given or not

ALTERTABLE dbo.Employees

ADD IsBirthdayAllowanceGiven bitDEFAULT(0)NOTNULL

GO

Assume that company pays an additional allowance for employees whose birth date fall on current month. The below stored procedure inserts allowances for employees whose birth date fall on current month and update employees record. Note that WAITFOR DELAY has been added hold the transaction for few seconds in order to see the problem related to it. And no Isolation Level has been set, default applies.

IFOBJECT_ID(N’dbo.AddBirthdayAllowance’, N’P’)ISNOTNULL

BEGIN

DROPPROC dbo.AddBirthdayAllowance

END

GO

CREATEPROC dbo.AddBirthdayAllowance

AS

BEGIN

BEGINTRAN

— inserts records to allowances table

INSERTINTO Allowances

(EmployeeID, MonthAndYear, Allowance)

SELECT EmployeeID,getdate(), 100.00

FROM dbo.Employees

WHERE IsBirthdayAllowanceGiven = 0

ANDMONTH(BirthDate)=MONTH(getdate())

— hold the transaction for 5 seconds

— Consider this is as some other process that takes 5 seconds

WAITFORDELAY’00:00:05′

— update IsBirthdayAllowanceGiven column in Employees table

UPDATE dbo.Employees

SET IsBirthdayAllowanceGiven = 1

WHERE IsBirthdayAllowanceGiven = 0

ANDMONTH(BirthDate)=MONTH(getdate())

COMMITTRAN

END

Before running any queries, make sure at least one employee’s birth date falls on current month. Now open a new connection (let’s name it as Connection1) and run the stored procedure. In my Northwind database, I have one record that stratifies the criteria; EmployeeId 6: Michael Suyama.

USE Northwind

GO

EXEC dbo.AddBirthdayAllowance

Immediately, open Connection2 and insert a new employee whose birth date falls into current month.

USE Northwind

GO

INSERTINTO dbo.Employees

(LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate)

VALUES

(‘Creg’,’Alan’,’Sales Representative’,’Ms.’,’07/13/1980′,’07/20/2007′)

Go back to Connection2. Once the transaction completed, query the Allowances table and see. You will see a one record that is generated for Michael. Then open the Employees table and see that how many records have been updated. It has updated two, not only Michael but Alan. Note that no record has been inserted to the Allowances table for Alan. In this case, the new record is considered as a Phantom record and read of the new record called as Phantom Read. This cannot be avoided with default Isolation Level that is Read Committed. Change the stored procedure and set the Isolation Level as Repeatable Read.

IFOBJECT_ID(N’dbo.AddBirthdayAllowance’, N’P’)ISNOTNULL

BEGIN

DROPPROC dbo.AddBirthdayAllowance

END

GO

CREATEPROC dbo.AddBirthdayAllowance

AS

BEGIN

SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD

BEGINTRAN

— inserts records to allowances table

INSERTINTO Allowances

(EmployeeID, MonthAndYear, Allowance)

SELECT EmployeeID,getdate(), 100.00

FROM dbo.Employees

WHERE IsBirthdayAllowanceGiven = 0

ANDMONTH(BirthDate)=MONTH(getdate())

Now bring the Employees table to original state.

UPDATE dbo.Employees

SET IsBirthdayAllowanceGiven = 0

DELETE dbo.Employees

WHERE FirstName =’Alan’

DELETE dbo.Allowances

Open two connections again and try the same. Check the result. Still the Phantom Reads problem exists. In order to avoid this problem, you need to use highest Isolation Level that is Serializable.

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.

IFOBJECT_ID(N’dbo.AddBirthdayAllowance’, N’P’)ISNOTNULL

BEGIN

DROPPROC dbo.AddBirthdayAllowance

END

GO

CREATEPROC dbo.AddBirthdayAllowance

AS

BEGIN

SETTRANSACTIONISOLATIONLEVELSERIALIZABLE

BEGINTRAN

— inserts records to allowances table

INSERTINTO Allowances

(EmployeeID, MonthAndYear, Allowance)

SELECT EmployeeID,getdate(), 100.00

FROM dbo.Employees

WHERE IsBirthdayAllowanceGiven = 0

ANDMONTH(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.

ALTERDATABASE Northwind SETALLOW_SNAPSHOT_ISOLATIONON

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

BEGINTRAN

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

UPDATE dbo.Employees

SET HireDate =’5/2/1992′

WHERE EmployeeID = 1

Nowopen the second connection (Connection2)andtryto 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 enabledSnapshotIsolationLevelin the database,SQLServer stores version of the record. Use below dynamic management viewfor retrieving versions stored in the store.

SELECT*FROMsys.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.

SETTRANSACTIONISOLATIONLEVELSNAPSHOT

BEGINTRAN

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.

COMMITTRAN

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

BEGINTRAN

— 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)andread the same record. Note the Isolation Level.

USE Northwind

GO

SETTRANSACTIONISOLATIONLEVELSNAPSHOT

BEGINTRAN

SELECT*

FROM dbo.Employees

WHERE EmployeeID = 1

Go back to Connection1 and commit the transaction.

COMMITTRAN

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.

Read Committed Snapshot Isolation Level

This is the new implementation of the Read Committed Isolation Level. It has to be set not at session/connection level but database level. The only different between Read Committed and Read Committed Snapshot is, Read Committed Snapshot is Optimistic whereas Read Committed is Pessimistic. The Read Committed Snapshot differs from Snapshot in two ways; Unlike Snapshot, it always returns latest consistence version and no conflict detection.

Let’s test this out. First, enable the Isolation Level.

ALTERDATABASE Northwind SETREAD_COMMITTED_SNAPSHOTON

Now open a new connection (Connection1) and run the below query.

USE Northwind

BEGINTRAN

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

UPDATE dbo.Employees

SET HireDate =’5/2/1992′

WHERE EmployeeID = 1

This makes a last consistence version in the version store. Now open the second connection (Connection2) and try to retrieve the record.

USE Northwind

GO

BEGINTRAN

SELECT*

FROM dbo.Employees

WHERE EmployeeID = 1

You get a record from the version store. The value for the HireDate will be the last consistence value that is 05/01/1992. Go back to Connection1 and commit the transaction.

COMMITTRAN

In Connection1, execute the SELECT statement again. Unlike Snapshot the latest consistence is returned that has the HireDate as 05/02/1992. Commit the Connection2 transaction too.

Since the maintaining old versions are not necessary with this level, there will be no impact for performance like Snapshot but all the concurrency related problems except dirty reads can happen.

Finally, let’s summarize. The below table depicts importance points of each level.