Isolation Levels in SQL Server

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.

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.

ALTER DATABASE Northwind SET READ_COMMITTED_SNAPSHOT ON

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

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

       

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

       

BEGIN TRAN

       

       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.

COMMIT TRAN

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.

           

Dirty Reads

Lost Updates

Nonrepeatable reads

Phantom reads

Concurrency model

Conflict Detection

Read Uncommitted

Yes

Yes

Yes

Yes

Pessimistic

No

Read Committed

No

Yes

Yes

Yes

Pessimistic

No

Repeatable Read

No

No

No

Yes

Pessimistic

No

Serializable

No

No

No

No

Pessimistic

No

Snapshot

No

No

No

No

Optimistic

Yes

Read Committed Snapshot

No

Yes

Yes

Yes

Optimistic

No

 

About the author:

Dinesh Priyankara is a SQL Server MVP in Sri Lanka and is currently employed as a Database Administrator in a private firm. He teaches, consults and runs training programs for various institutes/companies in Sri Lanka and he is one of founders of www.sqlserveruniverse.com.  Dinesh maintains his technical blog at http://dineshpriyankara.spaces.live.com.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

One Response to “Isolation Levels in SQL Server”

  1. As per your article,dirty reads can happen in “Read uncommitted” but not in “Read Committed Snapshot”.

    But after reading this article , if you see the example given in “Read Committed Snapshot”.
    begin tran — in connection 1
    UPDATE dbo.Employees
    SET HireDate = ’5/2/1992?
    WHERE EmployeeID = 1

    In connection 1 hiredate will be changed to ’5/2/1992?but in connection 2 when I select from the table it still shows the old hiredate.Is this not the same as Read Uncommitted? then why in the above table, it is mentioned that all the concurrency related problems except dirty reads can happen in “Read Committed Snapshot”.Please clarify this.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |