Understanding Isolation Levels in SQL Server 2008 R2 and 2012 through Examples

Home > Blogs > SQL Server > Understanding Isolation Levels in SQL Server 2008 R2 and 2012 through Examples

Understanding Isolation Levels in SQL Server 2008 R2 and 2012 through Examples

Like This Blog 18Peter Avila
Added by August 22, 2012

On your way to work, you stop by an ATM. Just as you are punching in your secret code, so is a family member punching in their secret code at an ATM across town. You are going to transfer $400 out of an account that has a $500 balance, and your family member is about to withdraw $300 from that same account. Were it not for isolation levels, trouble would be brewing on the horizon…

SQL Server prevents problems that can arise from concurrency situations, such as the above in which concurrent attempts to access and/or modify the same data are made, by implementing six isolation levels. This blog post will help you experience all six isolation levels. As you see them “in action,” you will understand what each one accomplishes and when to use each.

To understand the examples in this post, you will first need to understand what a transaction is and some things about isolation levels. Before we get to the examples, we will cover those topics briefly. The 10776 course, Developing Microsoft SQL Server 2012 Databases, provides a more comprehensive explanation of all the relevant topics.

The examples provided are run against the AdventureWorks database. See Introducing the Examples, below, for links where you can obtain the necessary files to run the examples on your own.

A Brief Introduction to Transactions

All but one of the six isolation levels in SQL Server work by isolating transactions from one another. They are referred to as transaction isolation levels. The other one works at the statement level. We’ll discuss the one statement isolation level at the end of this post. Here, we will start by looking at what a transaction is.

Back at the ATM, you were about to transfer funds between two accounts. Note that the transfer of funds involves at least two data changes; one account needs to be debited and the other one credited. Now, imagine that the debit goes through but the credit fails! You don’t want that to happen, because then you’d lose money. You want both data updates to be treated collectively as a unit of work that either succeeds or fails in its entirety. We call that unit of work a transaction. A transaction is a unit of work against a database that involves one or more changes to data that have to collectively be treated as a single unit—either they all succeed or none of them ever happened.

Here are some more examples of situations requiring multiple data changes that have to either all succeed or never happen: When data are merged into a database, several tables may need to be updated. When a customer places an order, data may need to change in an Order table, an Invoice Line Item table, and a Product table. Purchasing airline tickets may require updates to a passenger table and a reservations table. Whenever an operation requires multiple data changes that have to collectively be treated as a single unit, that’s an example of when a transaction can be used.

We’re ready to introduce two useful concepts at this point—commit and rollback. Data changes that occur in a transaction can only be committed (made permanent in the database) if all data changes in the transaction succeed; otherwise, all the data changes in the transaction made up to the failure point must be rolled back (undone—never happened!).

You may also like:  Microsoft White Paper - SQL Server 2016 and Windows Server 2016 Better Together

And now we’re ready to look at some actual commands that allow us to work with transactions. In the examples in this post, we will be using the Explicit Transaction Mode. In this mode, we initiate a transaction with the BEGIN TRANSACTION command. We terminate a transaction with either the COMMIT TRANSACTION or ROLLBACK TRANSACTION command as appropriate.

A Brief Introduction to Isolation Levels

Concurrency situations have to be looked at carefully because they can give rise to known concurrency issues, including dirty reads, non-repeatable reads, and phantom reads that can in turn result in negative consequences for the data as we were about to experience at the ATM (find out more about concurrency issues). We’ve seen that, in order to prevent concurrency issues, isolation levels are used to isolate transactions or statements from each other. Here are the isolation levels by name:

A. Transaction Isolation Levels

  1. READ UNCOMMITTED
  2. READ COMMITTED (Default)
  3. REPEATABLE READ
  4. SERIALIZABLE
  5. SNAPSHOT

B. Statement Isolation Level

6. READ COMMITTED SNAPSHOT

As you will see in the examples that follow, the higher the isolation level, the higher the level of protection (the more concurrency issues are prevented). Also, each isolation level includes the protections provided by the previous level so that each successively higher isolation level provides added protection in the form of more concurrency issues avoided. But, alas, nothing is free, and so the higher the isolation level, the less data availability there will be. Choosing the appropriate isolation level is a balancing act between highly safe concurrency and high data availability.

Let’s see all this in action.

Introducing the Examples

The examples provided are run against the AdventureWorks database. You can download the AdventureWorks databases (AdventureWorks2008_SR4.exe, which includes all but the 2008R2 versions of the AdventureWorks database)

Download the actual examples provided in this blog post as script files

To create a concurrent environment, all examples use two SQL Server sessions, with each session running a different transaction and with each transaction accessing the same resources. In SQL Server Management Studio, each query window represents a different session, so you can use different query windows for the different transactions in SQL Server Management Studio.

All examples include real word scenarios that allow you to ground all this in reality.

Example 1: The Read Uncommitted Transaction Isolation Level

The Read Uncommitted Transaction Isolation Level provides no isolation at all between transactions and permits one of the most basic forms of concurrency violations, the dirty read. A dirty read occurs when a transaction can read data that have been updated in another transaction but not yet committed.

Use this on a one-user system, on systems in which the likelihood of two transactions accessing the same resources is nil or almost nil, or when using the Rowversion data type to control concurrency.

SQL 2012 Read Uncommitted Transaction Isolation Level

Example 2: The Read Committed Transaction Isolation Level

The Read Committed Transaction Isolation Level prevents dirty reads by allowing only committed data to be read by the transaction. This is the default transaction isolation level in SQL Server.

SQL 2008 2012 Read Committed Transaction Isolation Level

Example 3: The Repeatable Read Transaction Isolation Level

You may also like:  Optional Parameters in SSRS

As you read about in steps 2 of the previous transaction isolation levels, the transaction in Session 2 was able to modify data that had been read by the transaction in Session 1. And as the Real Workd Scenario described, this can result in a “lost update.” The Repeatable Read Transaction Isolation Level does not allow this to happen because it would break the Repeatable Read rule; in other words, a read of the same data by the transaction in Session 1 could produce a different result.

SQL 2008 2012 Repeatable Read Transaction Isolation Level

Example 4: The Serializable Transaction Isolation Level

Our example will start with the Repeatable Read Transaction Isolation Level in order to show the problem that the Serializable Transaction Isolation Level prevents.

SQL 2008 2012 Serializable Transaction Isolation Level

Example 5: The Snapshot Transaction Isolation Level

You may have noticed in examples 1 – 4, above, that concurrency issues are prevented by reducing the availability of data; reads are not allowed, updates are not allowed, or inserts are not allowed. The Snapshot Transaction Isolation Level was developed to prevent many of the concurrency issues prevented by those isolation levels but while reducing the cost associated with higher isolation levels—it allows greater data availability.

It accomplishes this feat by using row versions in TempDB to create a virtual snapshot of the database before the transaction starts. It then only allows the transaction to access that virtual snapshot. This approach is called versioning-based isolation (a complete explanation of the details behind visit Understanding Row Versioning-Based Isolation Levels.

With versioning-based isolation, a transaction sees only the data in that virtual snapshot, so other transactions can still access the same data but only as long as they don’t try to modify data that have been modified by the first transaction. If they do, then those transactions are rolled back and terminated with an error message.

The Snapshot Transaction Isolation Level can only be used after a switch allowing it has been set in the database. Turning on this switch tells the database to set up the versioning environment. It is important to understand this point, because once versioning is turned on, the database has the overhead of maintaining the versioning overhead regardless of whether any transactions are using the Snapshot Isolation Level.

Here’s the example:

SQL 2008 2012 Snapshot Transaction Isolation Level

Example 6: The Read Committed Snapshot Isolation Level

Up until this point, all the isolation levels have isolated transactions from one another. Resources that are made unavailable to other transactions only become available again once the original transaction completes. The Read Committed Snapshot Isolation Level is different in this regard. It will read data that have been committed by another transaction.

The Read Committed Snapshot Isolation Level is also turned on by means of a database switch. Then, any transactions that use the Read Committed Isolation Level will work with versioning.

SQL 2008 2012 Read Committed Snapshot Isolation Level

I hope these examples help you to understand how to properly isolate transactions and statements from one another to prevent concurrency issues

Enjoy!
Peter Avila
SQL Server Instructor – Interface Technical Training
Phoenix, AZ

Videos You May Like

Agile Methodology in Project Management

0 41 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

Creating Users and Managing Passwords in Microsoft Office 365

0 53 1

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.

Detailed Forensic Investigation of Malware Infections – April 21, 2015

2 122 1

In this IT Security training video, Security expert Mike Danseglio (CISSP / CEH) will perform several malware investigations including rootkits, botnets, viruses, and browser toolbars.

Write a Comment

See what people are saying...

  1. Sharmila

    Nice explanation.Great post

  2. peter scott

    Nice explanation which covers everything in detail. A simplified explanation for those who are not enough time to read this can be found at below link

    http://etlscrapbook.blogspot.com/2017/02/transaction-isolation-levels-in-sql.html

  3. Peter Avila Peter Avila

    Sorry, Phi, I think I didn’t answer your question. It has been some time and you may have resolved the issue by now, but in case you haven’t, here’s what I’d say: It sounds like you are describing nested transactions, and I’m not sure if you’re saying that both stored procedures SET TRANSACTION ISOLATION LEVEL SNAPSHOT or if one of them uses some other isolation level. In either case, there should not be a problem. I wonder what error message you got back.

    Here’s what I suspect the problem might be: A ROLLBACK command will rollback the transaction in which the command is issued and all the transactions above it. If you are rolling back the transaction in the second proc–the one that the first one calls–you’ll get an error indicating a mismatching number of BEGIN and COMMIT statements. Let the first proc handle rollbacks. So, maybe the problem is a nested transactions issue, not an isolation level issue.

  4. Peter Avila Peter Avila

    Hello, everyone. My apologies for the delayed reply. Thanks so much for the positive comments! 😉

    Here are replies to your comments:

    Eric:

    Microsoft seems to agree with you on at least some level since READ COMMITTED is the default isolation level in SQL Server. I would always recommend considering each individual situation carefully to properly determine the isolation level you’ll need.

    Naren:

    Under Snapshot isolation levels, a transaction takes a snapshot of the data when it starts and then does all of its work using that snapshot instead of the actual database. The point of this is to allow the transaction to access data consistent with a single database state while not affecting or being affected by other transactions. That snapshot is maintained in the TempDB database using something called row versioning (go to https://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.110%29.aspx and then scroll down to “How Snapshot Isolation and Row Versioning Work”). What is in TempDB are not the uncommitted data changes that have been made—such as in step 5 of example 6 of this article that you pointed to—it’s the snapshot. So, where are the uncommitted changes? Under any isolation level, they are written to the transaction log of the database. Sometime after the transaction commits, the transaction log crosses a checkpoint and writes to the database everything that has been committed. When a transaction using one of the snapshot isolation levels makes changes using the snapshot, those changes are also written to the transaction log and committed to the actual database when the transaction ends successfully. When there are no transactions accessing a snapshot, it is released from memory.

    Neelam, Wolfgang Schmidt & Vamsi:

    Thank you so much for having brought this to my attention! It will be fixed in a future update.

    Phi:

    The query shown below will return the isolation level of the currently selected database in the current session—identified using the system niladic function @@SPID. It interrogates the Transaction_Isolation_Level column of the dynamic management view sys.dm_exec_sessions for the current session which is an integer that the query converts to the name of the isolation level.

    SELECT
    IsolationLevelName = CASE Transaction_Isolation_Level
    WHEN 0 THEN ‘Not specified’
    WHEN 1 THEN ‘Read Uncommitted’
    WHEN 2 THEN ‘Read Committed’
    WHEN 3 THEN ‘Repeatable Read’
    WHEN 4 THEN ‘Serializable’
    WHEN 5 THEN ‘Snapshot’
    END
    FROM sys.dm_exec_sessions
    WHERE session_id = @@SPID

    N.Raz:

    😉

  5. Danyal Ahmed

    excellent Post

  6. Vamsi Krishna

    very good post.

    Can u check snapshot isolation level once..seems wrong screenshot

  7. Venkat

    You’re a champion my boy….

  8. Gaurav J Jha

    Very good post

  9. Eric

    Great article on isolation and transaction. Can I conclude that we should choose “The Read Committed Snapshot Isolation Level” for the best balance between transaction safe and high availability?
    Thanks.

  10. Naren

    Great one…. But I have question example 6 and at step 5 .. Update statement on session1 executed and applied data on oreginal table or version store snap in tempdb or in both areas?

  11. Neelam

    Is point number 5 of Example 5 Correct ??

  12. SSP

    Perfect!

  13. Megha

    This is by far the best explanation of isolation levels that I have read.

    Very effective examples and simple code to understand

    Thanks

  14. Wolfgang Schmidt

    Cool explanations, thanks!

    Notices a litte error in Example 5, Step 5. The VacationHours column shows "40" but it should be "48" so that the statement "changes committed in transaction in session 2 are still not available" applies.

  15. Interface Technical Training

    Execute the SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before the BEGIN TRAN statement in all Stored Procedures.  

  16. Balaraju ch

    Very usefull and realworld and understanding examples.

  17. Phi

    Hello,

    Great post, can i ask one question ?

    One Store procedure start with isolation level snapshot , and in this transaction it call a store also use a transaction level snapshot. It fails cause snapshot mode already on, how can I solve this prolem ?

    Thanks

  18. N.Raz

    Thanks. This sure helped. Only if you have included more examples it would be awesome but okay i think even this might work a little bit too :):)

Share your thoughts...

Please fill out the comment form below to post a reply.