Losing Out
I spend a lot of time talking about this with clients, because so many of them face dire blocking and deadlocking issues across a variety of queries under the Read Committed isolation level, that can be easily solved by switching to Read Committed Snapshot Isolation.
There seems to be quite a bit of misconception about isolation levels, particularly optimistic ones in SQL Server. In the most unfortunate of circumstances, optimistic isolation levels are bundled in with Read Uncommitted/NOLOCK as being able to read dirty data.
To make the differences clear, here’s how things shake out across a few of SQL Server’s isolation levels I see most commonly.
Note that I’m not including Repeatable Read, Serializable, or Snapshot Isolation. I’ll talk about why as we go through things a bit here.
While it is tempting to use the Read Uncommitted isolation level to avoid some common blocking and deadlocking scenarios, that isolation level comes with a steep price: your queries are subject to every kind of dirty read imaginable.
By the time you realize that your queries have produced bad data, it’s far too late.
That brings us to Read Committed, which is the default isolation level in SQL Server, except in Azure SQL DB. In that cloud version of SQL Server, Read Committed Snapshot Isolation is the default isolation level.
Read Committed seems like a decent compromise between not returning awful, incorrect data, until you realize that your read queries can block, be blocked by, and deadlock with modification queries. This will typically happen in queries that take object level shared locks, which queries using Read Uncommitted/NOLOCK will not do. Nor will queries operating under an optimistic isolation level, like Read Committed Snapshot Isolation, or Snapshot Isolation.
I cover both of those scenarios in these posts:
- When Read Queries Block Write Queries In SQL Server
- Why Read Queries Deadlock With Write Queries In SQL Server
Read Committed Snapshot Isolation is typically the better of the three options, especially if you prefer correct data being returned to clients.
The final point I’ll make in this section is that writer on writer blocking and deadlocking can occur in every isolation level, even many cases under Snapshot Isolation.
You May Notice
Since I’ve hopefully scared you out of using Read Uncommitted/NOLOCK for your queries, let’s talk about the remaining competitors.
Read Committed and Read Committed Snapshot Isolation have similar traits as to what level of data integrity they guarantee.
The tradeoff comes with a change to the behavior of read queries in the face of data modifications. While readers won’t block or deadlock with modification queries under Read Committed Snapshot Isolation, it’s important to understand how they avoid that while still returning data with some guarantees of correctness.
There’s No Such Thing As A Free Feature©, as they say.
Since I promised to talk about why I’m not talking about certain isolation levels in this post, let’s do that before we look at Read Committed and Read Committed Snapshot isolation level differences.
- Snapshot Isolation: Even though this is my favorite isolation level, it’s usually too hard to apply. Queries have to ask for it specifically. Most of the time, you want every query to benefit with minimal changes.
- Repeatable Read: Is sort of like a weaker version of Serializable. I often struggle to explain why it even exists. It’s like all of the blocking with fewer of the guarantees.
- Serializable: I very rarely run into scenarios where this is the ideal isolation level. Many times, I find developers using it without knowing via their ORM of choice.
There you. have it. I’m not saying you should never use any of these. They exist for good academic reasons, but practical applications are slim, particularly for Repeatable Read and Serializable.
Let’s create a simple table to muck about with:
CREATE TABLE
dbo.isolation_level
(
id int NOT NULL PRIMARY KEY IDENTITY,
isolation_level varchar(40)
);
INSERT
dbo.isolation_level
(
isolation_level
)
VALUES
('Read Committed'),
('Read Committed Snapshot Isolation');
I’m also going to set my database to use Read Committed Snapshot Isolation, but request Read Committed when the select query runs.
ALTER DATABASE
Crap
SET
READ_COMMITTED_SNAPSHOT ON
WITH
ROLLBACK IMMEDIATE;
Read Committed
Very few people use this isolation level fully in practice. I know you. I see you in the naked light of day.
Your database is set to use this isolation level, but your queries all ask for Read Uncommitted/NOLOCK. Which means… You made bad choices.
Not as bad as the choice Microsoft made in picking Read Committed as the default isolation level, but there we have it. Time to own it.
We’re going to use an equally simple couple queries to demonstrate the differences.
--window one
BEGIN TRAN;
UPDATE il
SET il.isolation_level = 'Read Committed'
FROM dbo.isolation_level AS il
WHERE il.id = 2;
ROLLBACK;
And then:
--window two
SELECT
c = COUNT_BIG(*)
FROM dbo.isolation_level AS il WITH(READCOMMITTEDLOCK)
WHERE il.isolation_level = 'Read Committed';
While the update is running, our select will get blocked.
There go those shared locks, causing problems.
But when the update is finally committed, our query will count 2 rows matching our search on the isolation level column.
If that’s what you absolutely need, then you should use Read Committed, or another more strict isolation level, like Repeatable Read or Serializable.
For most people, if you’re okay with NOLOCK, you’re better off with Read Committed Snapshot Isolation.
Read Committed Snapshot Isolation
If we remove the READCOMMITTEDLOCK hint from the select query and allow it to use versioned rows via Read Committed Snapshot Isolation set at the database level, something different happens.
There’s no blocking to show you. The select finishes instantly. There’s not really a good screenshot of that.
SQL Server takes the version of the row that it knew was good when the update started — the one with Read Committed Snapshot Isolation as the value — and sends that version of the row to the version store.
Again, it is a known, good, committed value for that row. No dirty read nonsense here.
But that means the query returns a result of 1, since only one row qualifies for the where clause filter when we go looking for stuff in the version store.
If you have queries that should rely on reading only committed data from completed modification queries, you can hint them with READCOMMITTEDLOCK to maintain such behavior.
In the same sense, you could use the SNAPSHOT isolation level and only hint certain queries to use it, either by using SET TRANSACTION ISOLATION LEVEL, or adjusting your query’s connection strings to request it.
But that’s a lot harder! You have to go around potentially changing a lot of code to ask for it, or separating queries that you want to use a different isolation level into a different connection string group. I realize that at this point, some of you may be confused here by Microsoft’s awkward naming choices. Snapshot Isolation, and Read Committed Snapshot Isolation, are indeed two different optimistic options for isolation levels.
They are not entirely interchangeable, though either one is generally a better choice than the default isolation level, Read Committed.
Even with the overhead of generating row versions, I’ll take correct results quickly.
Thanks for reading (optimistically)!
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.