Why Read Committed Queries Can Still Return Bad Results In SQL Server

Why Read Committed Queries Can Still Return Bad Results In SQL Server

---

Thanks for watching!

Video Summary

In this video, I delve into some fascinating insights about the read committed isolation level in SQL Server, which I wish I had known much earlier in my career. Specifically, I explore how this isolation level can lead to inconsistent query results and even violate unique constraints, all while running under what seems like a simple “read committed” setting. By walking through practical demos, I highlight these quirks using tables and stored procedures that mimic real-world scenarios. Through these examples, you’ll see firsthand how queries can get blocked on certain rows, leading to incomplete or misleading results when the transaction finally commits. Additionally, I explain why snapshot isolation levels are generally a better fit for most workloads, emphasizing the importance of choosing the right isolation level based on your specific needs.

Full Transcript

Erik Darling here with Darling Data, slightly off screen when I do this, with my big hands. And today’s video… Bum, bum, bum, bum, bum, bum, bum. I got a drum solo on that one. Today’s video, I’m going to teach you about a couple things that I learned. Admittedly, I wish I had learned them much earlier in my SQL Server career because they would have answered a lot of questions that I learned a lot of questions that I learned a lot of questions that I had about weird stuff that I saw in query results. Not query execution plans, query results. I don’t know how many of you actually care, but I started off my SQL Server career at a market research company. And my first SQL Server test was like sort of automated loading Excel files of data into SQL Server for an auto dialer. Well, actually, not an auto dialer. You had to physically dial the numbers. But just like a list of people who companies wanted to contact to see how satisfied they were with things or if they’d be interested in this new product, usually a credit card. And from there, I ended up writing reports and stuff. And people would run reports. They’d be like, data in these reports is wrong. They’d be like, don’t know how. They’d also be like, this report ran for kind of a long time. And what it turned out to be a lot of the time was that the databases in question were using read committed, the pessimistic isolation level. And like project managers, you know, demanding squirrel brains if they are, would be running reports constantly like while people were dialing and getting like respondent results in. So not only were like report queries getting blocked, but they were getting blocked in weird places that would make results look weird, wrong, incorrect. They’d be like, this doesn’t tally up to that. And this doesn’t tally up over here. We’re not confident in here. Our confidence interval is very low. And it wasn’t until I had moved on from there to other DBA developer type jobs where the applications were just, I didn’t use NOLOCK. I would never. But I ran into other applications that use NOLOCK quite heavily, which had its own set of problems. But at least like, you know, no one was just like, my query’s blocked. Which, you know, I guess, I guess you take what you can get. But, you know, people would still complain about like result inconsistency. And I’d be like, well, the application uses NOLOCK. Talk them out of it, right?

Like call up support and be like, stop the damn NOLOCKs. Stop the count. So what I’m going to show you today is a couple funny things that can happen under read committed, the pessimistic isolation level that I learned from, I forget if I already said this. I learned from reading blog posts by a lovely fellow named Craig Friedman, who, I don’t know, I think in a weird way, like, I don’t know. I don’t want to make him feel old if he ever sees this, but he’s like the grandfather of SQL Server blogs.

Because he works at Microsoft and he worked closely on, worked, worked, worked, I’m not sure what he currently does. But worked, at least as far as I know, worked, maybe still works closely on SQL Server and would write a lot about it because he knew a lot about it. And sharing is caring.

So let’s look at a couple funny things that I learned from Mr. Friedman about read committed, the pessimistic isolation level, that I have turned into sort of my own brand of demo. All right. So the first thing we’re going to do is create a table, well, a couple of tables.

First, we’re going to drop if exists a couple of tables. We’re going to get rid of a couple of these things. Then we’re going to create a table called consultants that has a primary key on consultant ID, a first name and a last name.

And we’re going to insert one row for me, Mr. Erik Darling. That’s me, Darling Data. And then we’re going to create a table called clients that has an invoice ID, a consultant ID with a foreign key that references the consultant table.

Consultants table. Even though there’s one, it’s not more than one. You know, hope burns eternal.

Perhaps someday I will grow and hire and contribute meaningfully to this American economy. For now, I mostly just retire to bartender’s rent. So we’re going to create this table.

We’re going to put two rows in there. And as, you know, typical consultants do, we have reached nearly the integer max for this invoice amount. So this is how much these invoices are worth, which is clearly why I hang out in an Adidas t-shirt all day, because I make this much money in invoices.

Why I still have to record these videos. With all my largesse around, I choose to record YouTube videos that a few hundred people appreciate. So we’re going to create this table, put a couple rows in there.

And just to show you what this looks like initially, these are the query results. So we hit control and one. There we go.

Zoom it is responsive. This is what we have. Two rows for me, Erik Darling. Two invoice IDs. My consultant ID, because it’s in both tables. So you see all the columns from both tables in there.

And an invoice amount. And I didn’t spell anything wrong. That’s great. All right. So let’s get out of here. Zoom it is, will become responsive again.

There we go. Okay. Booper reel.

And now what we’re going to do is make sure that we have the right thing in here. We do. We have the right query in there. We are highly skilled, trained professionals here at Darling Data. And if I run this query, we’re going to see the same results that we just saw.

So this is all good here. Now, let’s say that we have a store procedure or a batch of crap stored some other way that runs. It begins a transaction.

It does an update. And, you know, it’s hard to replicate concurrency as one person. So begin train is a casual exercise. And, like, store is, like, two people at two computers trying to do two things at the same time.

All right. So begin train is a good way to say, hey, I started doing something. And you just started doing something exactly the same time. Even though it’s not exactly precisely the same time because I’ve been babbling for a little bit.

But if you come over here and run this query now. And, again, this is read committed to pessimistic isolation level. This query is going to get blocked. And it’s going to get blocked because the second invoice in this table is locked because we’re updating it.

Now, the query in the other window got blocked on this invoice ID. It’s already read the first row, the first invoice ID. All right.

So now let’s say that something else happened. And Erik Darling, wedding bells rang. And Erik Darling married the data and became Mr. Darling data. All right.

So, you know, don’t congratulate me yet. We’re still working out the prenup. But let’s just say this happened. All right. I’m now a Mr. Erik Darling data. And run that update.

And if we look at the results after the update for a query that’s not going to be blocked, we’re going to see correct results. All right.

So we’re going to see that this nice client over here decided to give me a tip. All right. They gave me an extra dollar. All right. So this ends in 07. This ends in 06. And Erik Darling’s recent marriage to the data has gone through.

We worked out the prenup. Everything’s good. All right. So that’s over here. Now, if we hit commit on this, all right, and I’m going to hit this twice just to make sure, right?

So that says we actually committed it. And this says, oh, you’re all out of transactions now. Your ATM card is declined. And we come look over here. We’re going to see a conflicting vision of the world.

It is going to look like Erik Darling might be trifling a little bit. It might be pretending to be single with some clients and might be pretending to be married with other clients.

What happened? Are you a cheater, Erik Darling? There’s no too boring for that. So this is one type of inconsistency that can happen under read committed, the pessimistic isolation level.

Your query can read some rows, get blocked, not read some other rows. And then when your query is done, it’s going to look a bit half finished, isn’t it? Right?

Now, I suppose something like this could also happen under no lock, right? If you read uncommitted, the pessimistic isolation level, you could catch that transaction in mid-flight and maybe not see everything that you’re supposed to see or see too much or see too little.

But this is just read committed. This is what SQL Server databases operate under all day, every day. This is not like read committed snapshot isolation, which you have to enable. Also, this wouldn’t happen under read committed snapshot isolation, the optimistic isolation level, because you would have just read the last known good version of the row.

So both rows, in this case, would have just said darling, which is probably at least a little bit less suspicious than having an Erik Darling and Erik Darling slash data. If you saw this, you might be like, one of those people did wrong data entry.

Damn your eyes. So that’s one thing that can happen. Another thing that can happen is that it can look like, and this could happen with a single table too.

And there’s like, I could do a demo of that. But I think what’s more interesting is when read committed the pessimistic isolation level, it can look like SQL Server did not honor a unique constraint.

So if we look at this table definition here, let me unhighlight this. That looks terribly ugly. I hate when I do that.

If you look at this table here, butthead is a unique column. It has a unique constraint on it. Right? So we’re not allowed to have duplicate values in butthead. Only one value in butthead at a time.

So let’s do this. And let’s load three rows into Beavis and butthead that would not violate that unique constraint. And just to kind of show you a little bit, just to sort of validate that this is a truly unique constraint, let’s try to insert this row.

And we’ll get an error there. Right? So we’re not allowed to have a duplicate value here. Right?

Our unique key constraint. So this fails. Right? So let’s make a little note in here. Oh, no. Oh, no. This failed. What are we going to do?

All right? That’s not really the point of the demo. The point of the demo is if we stick this query over in this window and we run this, we’re going to see three complete rows in here. We got a ha, a he, and a ha.

Very funny. All right. So what we’re going to do is we’re going to come over here, or we’re going to come down here, rather.

We’re going to begin a transaction and we’re going to run that. And then we’re going to come back over here and we’re going to run this. And then we’re going to play an old whoopsie-daisy on these other two rows. So we’re going to set butthead to he for row one.

And we’re going to set butthead to ha for row three. All right? So if we come look at this, all is right with the world.

We got a he, a ho, and a ha in butthead. All unique values. Our unique key is alive and well. The world is at peace.

There’s no more hunger, no more crime, no more want for anything. And then we’re going to do what we did before, and we’re going to commit this transaction fully here.

And we’ll just make sure that really, really committed. And now we’re going to come look at the results in this window. And we are going to see that butthead somehow has two huss. How did butthead get two huss in there?

It’s a unique constraint. The answer is, just did a little switcheroo around. But because this query under read committed, read this row, found a ha, got blocked here, saw whatever this was before, and waited until that transaction committed, we saw the table at two different points in time.

Now, this goes back to something that I have to keep explaining to a lot of people about read committed, the pessimistic isolation level, and that it is not a point in time snapshot of your data.

And you can gauge that mentally by the fact that it does not have snapshot in the name. Snapshot isolation has snapshot in the name. Check.

Read committed snapshot isolation has snapshot in the name. Check. Read committed, no snapshot. X. No snapshot. You read data, your queries, shrug through data, get blocked, and they can miss state, they can miss rows, they can double count rows, and all sorts of other wacky stuff.

Right? Lots of bad things can happen under read committed that a lot of people aren’t aware of. So whenever I’m talking to someone about using an optimistic isolation level, and I have to explain these things, that read committed is not the ultimate promise of pure data that it seems to be.

Read committed is not this bastion of correctness that everyone thinks it is. It is not a snapshot of your data at a point in time. It is the most recent version of your data is when your query read those rows. Now, granted, that’s what you need sometimes.

Right? If you want to make sure that you read the most recent version of this row, get blocked and then go read it. Just be aware that while you’re waiting for this, rows can get deleted here, rows can get updated here, rows can get updated here, rows can get deleted here.

Well, everything moving all around this query can happen while it’s blocked here. And then when this thing finishes, it’ll go try to read the rest of the data. And the results you get may be stale here, right?

And the results you get may be out of date here as well, because, or like, sorry, the data you read over here might be out of date. The data you read over here, the stuff you might have missed if your query was looking for data at a point in time.

So moral of the story is snapshot isolation levels, optimistic isolation levels are generally a better fit for most workloads. Getting blocked should be reserved for special occasions.

And even if you need your queries to get blocked, read committed is not a very good guarantee of data consistency for a point in time. Right?

So something like repeatable read or serializable, depending on, like, the requirements of the query, might be a necessary isolation level to prevent these kind of anomalies. So with that being said, I hope you enjoyed yourselves.

I hope you learned something. If you like this video, they’re available to you for a limited time for free. Don’t tell anyone it’s free.

There’s a thumbs up button that you can push that will increase my happiness. Also, there’s a subscribe button, which, for an even shorter amount of time, will make us best friends.

But, you know, I’m already a married man. I’m already Erik Darling data, so don’t get frisky. All right? And if you subscribe to my channel, you’ll get notifications when I drop these wonderful gems of learned wisdom upon your freckled brow.

So, I think I’ve said so 15 times in trying to end this thing. But anyway, thank you for watching. I will see you again in another, probably tomorrow.

Next week, I am on vacation, so there will not be any recording. But thankfully, due to the magic of WordPress blog scheduling, you will see regular blogging from me all week.

So, let’s all pause and think the spirits that came before us for inventing WordPress blog scheduling. Maybe it might still be alive.

I’m not sure. I don’t keep track of WordPress development that closely, because they use MySQL behind the scenes, and I’ll be damned if I’m ever going to care about that one.

It’s a walking, super fun site of a database. But anyway, I’m going to go work on the next set of demos to show you. So, stay tuned.

Keep your pants on. All that other stuff. Okay. We’re good. Thank you for watching.

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.