Yep.
My friend Forrest ran into a similarly fun problem, too.
Thanks for watching!
Video Summary
In this video, I delve into why the default Read Committed isolation level in SQL Server databases is often a suboptimal choice. Using a practical example, I demonstrate how using Read Committed Snapshot Isolation (RCSI) can provide more consistent results compared to the traditional Read Committed setting. By creating two simple tables with identical data and running a query that involves nested loops joins, I illustrate how RCSI ensures that we consistently get the same result set even when the underlying data is being updated. This example highlights why RCSI should be preferred over Read Committed for most scenarios, as it offers better consistency without the drawbacks associated with the inferior isolation level.
Full Transcript
Erik Darling here with Erik Darling Data, as usual. And today we’re here to talk about why read committed to default isolation level for most SQL Server databases is garbage. Well, this is just one of many reasons. There have been other reasons that I’ve talked about before in other blog posts and videos why it’s crappy, but this is one that I stumbled on while working on something for SPBli. So let’s first over in the first responder kit. And I was a bit surprised. I was even a bit surprised by this one. So let’s just get right into it. I’m going to create two tables and you may find this demo unrealistic, but it’s good enough for me to give you an overview of what I was running into. So we have two tables, tables one and table two. And in both of those tables, all I have is the numbers one through 10. So if you go down here and run these queries, you can verify that. Table one has the numbers one through 10 in it and table two has the numbers one through 10 in it. Good enough. Over in this window, I’m going to show you a couple of things before we, before we proceed with the query itself. Now, first I have read committed snapshot isolation turned on for this database that I’m in here, right? So if I go and look at sys.databases, I can see that my database lovingly named crap has read committed snapshot isolation enabled. But the query down here is the query.
Here has a couple hints on it. One is to read from table two with read committed lock. This is going to force the query to use the read committed isolation level. Just using set transaction isolation level read committed up at the top of the query is semantically different from using this locking hint for a few different reasons. I also have a force order hint down here. The reason I have a force order hint down here is to make sure that table two ends up on the inner side of a nested loops joint. I think I need to turn on query plans to do that. To show you that. And if I do, then you’ll see the shape of the query plan is about like this. So for every row that comes out of table one, we’re going to go into a nested loops join and then we’re going to hit table two down here. Okay. And that’s about that. So if I run this query, we’re going to see that all total for all of these columns is 55 over and over and over again. That’s because they’re not changing. If I come over to this window and I start running this update and I come back over here, we’re going to start seeing some interesting stuff happen. Namely that we’re going to be getting different values back in this column. So 65, 75, 85, 95, 105, 115. Weird, right? Read committed, reading all that good committed data. And this will happen repeatedly.
Every time we run this, we’ll get a different bunch of numbers back from all total over here. So we can see 45, 55, 75, 85, 95. What the hell is going on with this? I thought we were reading committed data here. What happened to us? This is bad news, right? This is terrible. So let’s kill this. And let’s talk about why read committed snapshot isolation is the greatest isolation level of all time. So let’s get rid of that. Let’s run this. And since no values are changing, this isn’t the biggest deal in the world. I just wanted to make sure I got things right.
Now let’s reset things over here. Let’s start these tables from scratch. We have IDs one through 10 and both tables two and tables one. So good for us there. And let’s run this query once more to make sure that everything’s golden. And now let’s come over here and run this update, right? Run that update in the loop. And every time we run this now, all of our numbers will be nice and lined up and in order and exactly the way that we want them. In other words, all the same.
Why did you change colors on me? How dare you? But we get consistent results back using read committed snapshot isolation. As soon as we stop using the inferior should be deprecated, destroyed, and forgotten for all time read committed isolation level, we do a lot better with result consistency, which I hear is important in databases. I hear there’s some rules that sound drug related that are supposed to make results in databases consistent. I forget exactly what it is. I don’t know. I don’t want to get busted by the DEA over on this channel talking about database rules and stuff. I don’t want to have the federales come after me. But this is just another reason why read committed is a garbage isolation level.
You know, apart from the fact that you can have readers block writers and readers deadlock with writers under read committed. And, you know, you can have all sorts of weird inconsistent results with it. This is just another example of inconsistent results with the read committed isolation level.
So, um, please don’t use it. Please use a real isolation level like snapshot isolation or read committed snapshot isolation. You know, isolation levels that give you correct results or something.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.