LearnNowOnline

Blog

Isolation Levels and Transactions - Microsoft SQL

Excerpt by Don Kiely | January 01, 2014

An isolation level defines how much a transaction is isolated from changes made by other, concurrent transactions. The current isolation level setting determines the side effects that can occur due to uncommitted changes that other transactions make. SQL Server supports all four of the isolation levels defined in the SQL ISO standard as well as two others related to row versioning:

• The default READ COMMITTED isolation level is in effect with autocommit transactions. This ensures that every transaction meets the requirements of the ACID test without requiring extra code. READ COMMITTED prevents concurrent transactions from reading uncommitted data or interfering with the transaction until it is complete. This level results on locks on resources that could affect concurrency and the volume of transactions that a server can handle.
• The READ UNCOMMITTED isolation level lets other transactions read changed data that has not yet been committed by other concurrent transactions, called dirty reads. This isolation level does not issue as many locks as READ COMMITTED, so has a lighter touch on resources and allows higher transaction rates.
• The REAPEATABLE READ isolation level is similar to READ COMMITTED in that the transaction cannot read changed data not yet committed from other transactions. But it goes further: this isolation level prevents other transactions from modifying data read by the current transaction until it completes. The benefit is that a transaction at this isolation level can repeatedly read the data and get the same results, even if there are other transactions pending.
• The SERIALIZABLE isolation level is similar to the REPEATABLE READ isolation level, but takes it up a notch. The SERIALIZABLE isolation level prevents both updates and inserts. It ensures that if a query is reissued inside the same transaction, existing rows won't change and new rows won't suddenly appear. SERIALIZABLE employs a range of locks that prevents edits, deletions, or insertions until the transaction is complete.
• The SNAPSHOT isolation level essentially saves a snapshot of data at the start of the transaction to ensure that any data read during the life of the transaction will be the same as at the start of the transaction, no matter what changes have been made to the data in the meantime by other transactions (the current transaction will see its own data changes). This isolation level generally doesn't require locks on data until the current transaction attempts to update data.
• The READ COMMITTED SNAPSHOT isolation level is similar to SNAPSHOT except that instead of providing a snapshot of the data when the transaction started, READ COMMITTED SNAPSHOT provides one when the statement started. The behavior of this isolation level is similar to READ COMMITTED except that it doesn't acquire shared locks and changes aren't blocked when resources are exclusively locked.

NOTE Isolation levels are described in the "Isolation Levels in the Database Engine" topic in SQL Server Books Online (BOL). As of this writing, Microsoft has not updated this topic for SQL Server 2012, but the SQL Server 2008 version is still valid. This is the case for some of the other BOL references included in this chapter.

You can set the isolation level of the current session using the SET TRANSACTION ISOLATION LEVEL <isolation level name> statement, as well as with a table hint for a specific query using the WITH clause. Isolation levels can affect what data your code reads when there are other concurrent transactions executing. The choice of isolation level you make can significantly impact the amount of activity an instance of SQL Server can support, so if you have performance issues on a heavily used database you might want to tweak the isolation levels used by your code.

This post is an excerpt from the online courseware for our Microsoft SQL Server 2012 Developer course written by expert Don Kiely.