The Art Of The SQL Server Stored Procedure: Isolation Levels

Dread Pirate

---

I’ve talked about isolation levels a bit lately because I need you all to understand that no isolation level is perfect, and that most everyone is completely wrong about how they really work.

For a very high percentage of workloads, Read Committed Snapshot isolation is the best choice. Why?

Because most developers would expect:

• Read queries to not block with write queries
• Read queries to not deadlock with write queries
• Read queries to return correct results from committed transactions

You only get *one* of those things from Read Committed, but you get all of those things from Read Committed Snapshot Isolation.

Sure, there’s a thing you don’t get from it, but if you want that thing, you have to put up with read queries blocking and deadlocking with write queries.

That thing is: Reading only the most current version of row values, which is the only meaningful guarantee that Read Committed will give you.

If you’ve been battling stupid locking problems for a long time, you’ve probably got NOLOCK hints everywhere, which means you’re not getting that anyway.

You’re getting back garbage.

So hear me out: If you’ve got some queries that require the most current version of row values to work correctly, you have the READCOMMITTEDLOCK table hint to save you.

What Read Committed Doesn’t Get You

---

To illustrate the concurrency issues that can arise under Read Committed, here are some slides I made for a thing:

If any of these surprise you, you’re not allowed to argue with me about Read Committed Snapshot Isolation.

Read Committed Snapshot Isolation And Dumb Queries

---

Queries that are susceptible to race conditions with optimistic isolation levels are queries that are written in stupid ways.

One example is a query like this:

BEGIN TRANSACTION;
     DECLARE
         @name nvarchar(40) = 'Erik';
   
    UPDATE dp
      SET
          dp.name = @name,
          dp.is_free = 0
    OUTPUT
        Inserted.*
    FROM dbo.DinnerPlans AS dp
    WHERE EXISTS
    (
        SELECT
            1/0
        FROM dbo.DinnerPlans AS dp2
        WHERE dp.id = dp2.id
        AND   dp2.is_free = 1
    );
COMMIT;

It will behave differently under optimistic vs. pessimistic isolation levels. Let’s pretend that two people try to book the very last seat at the very last time.

• Under Read Committed, the update to DinnerPlans would block things so that the read in the exists subquery would wait for it to complete, and find no rows.
• Under Read Committed Snapshot Isolation, the update to DinnerPlans would generate a row version, and the read in the exists subquery would read that version where it would find a row.

This is, of course, a very stupid query. If you’re just using direct updates, you won’t have problems:

UPDATE
    dp
  SET
    dp.name = N'Erik',
    dp.is_free = 0
FROM dbo.DinnerPlans AS dp
WHERE is_free = 1;

For Every Occasion

---

There are likely times when each and every isolation level is appropriate, or even required, for parts of a workload to function correctly.

Just like settings for parallelism, max server memory, and many other things in SQL Server, it’s your job to set them as appropriate guardrails for the workload as a whole.

Most workloads work better using Read Committed Snapshot Isolation. If there are specific queries in your workload with different needs, you have many wonderful options to fix them.

In some cases the READCOMMITTEDLOCK hint may be the minimum effective dose. You may also read this post and realize that you need a stronger isolation level with better guarantees, like Repeatable Read or Serializable.

Many people are surprised that Repeatable Read only takes its special locks on rows as it’s reading them, and that changes ahead of where the reads have occurred can still occur, and inserts can still occur between rows that have been read.

Like I said before, no isolation level is perfect, and many developers are surprised by the details of each one.

Most people think that Read Committed works the way Serializable does, where the set of rows you’ve read and are yet to read are somehow a golden copy of the data. That is not true.

The misunderstandings usually arise from a lack of testing for expected results under high concurrency.

Tools like SQL Query Stress and ostress can be invaluable for making sure you’re getting what you want from whatever isolation level you’re using.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.