The Difference Between Read Committed And Read Committed Snapshot Isolation In SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the differences between read-committed isolation and read-committed snapshot isolation (RCSI) in SQL Server, specifically addressing how RCSI mitigates some of the issues that can arise under the pessimistic isolation level. I demonstrate these concepts using a simple example with tables named consultants and clients from my CRAP database, showing how read queries behave differently when using RCSI compared to traditional read-committed mode. By walking through the process step-by-step, I explain why optimistic isolation levels like RCSI can be more aligned with developer expectations in many scenarios, while also highlighting potential trade-offs and considerations for different workloads.
Full Transcript
Erik Darling here with Darling Data. And spring is all around us here in the Northern Hemisphere, but for me more specifically, spring is all in my nose. So if I sound a little weird today, that’s why. And spring being in my nose is not a Deadpool-esque drug reference, it is an allusion to my allergies, which is alliteration. So last week I went on vacation, but before I did, that was a rhyme too. Wow, I’m nailing it today. Last week I went on vacation, but before I did, I recorded a video about different read phenomena that can happen under read-committed, the pessimistic isolation level that can make query results look weird. And in that video, I made several sweeping proclamations that this sort of thing wouldn’t happen with an optimistic isolation level like read-committed snapshot isolation.
Then of course, while I’m away, exploring business opportunities across Europe, I get questions in my email like, How would it be different? Why would that be different? Please tell me. So I’m recording a video to answer all of those questions today.
So the first thing that we’re going to do is we’re going to alter the CRAP database. I do not distribute the CRAP database. It is not an open source project. If you would like the CRAP database, you are free to create a database called that, and you are free to fill it with whatever you’d like, all the joy in life, your hopes, your dreams, whatever they may be. And then we’re going to redo a couple steps from the last video where we get rid of any tables that might exist that might cause strange things that would make the demo not work.
So I aim to be idempotent. It’s a tough word sometimes. And so what we’re going to do is create a table called consultants.
And even though there is only one consultant in there, it’s called consultants because I think naming tables as plural is kind of the right thing to do. You have consultants in a table. Each row is a consultant. Many consultants are plural.
So for now, there’s just one of me. And we’re going to stick old Erik Darling in there. And then we’re going to create a table called clients just like last time. And just like last time, the clients table will have a lovely primary key and will feature a magnificent foreign key that references the consultant ID in the consultants table.
Good stuff there. All right. Cool. So let’s insert a couple rows into clients because we have Erik Darling, the consultant at Darling Data, has many clients.
Right. So it makes sense to call that clients, even though there’s only two here. I promise there’s been more in a few. And then if we look at the data that we currently have in clients, then well, sorry, consultants and clients joined together, everything looks good.
Right. Consultant ID, first name, last name, invoice ID. The consultant ID again, a bit repetitive, but that’s what you get with select star. And of course, the invoice amount, which is an amount that I would love to invoice in American dollars to someone someday. Perhaps if the government is looking for some SQL Server help, we could talk.
Print some more money for me so that whatever number that is, is worthless. Worth five dollars in real life. So over here and let’s take this.
Let’s stick this over here. I think, oh, look, I already did. I’m so, I’m so smart. I see ahead. I see into the future. I see all things. All right.
So that returns right results. Now, in the last video, what happened was I said, begin tran and didn’t update. And then over here in this window, I ran this query and this query got blocked. This query got blocked until I committed the transaction in the other window and then it returned inconsistent results.
Since read committed snapshot isolation is now working with the version store, we are getting a snapshot of this data from prior to the update happening. So we still see all the right stuff in here because if you look over what this update is doing, we are incrementing that invoice amount by $1. I got a dollar tip on that huge invoice.
Someone was just like, yeah, Eric, darling, you’re worth that extra buck before we overflow the big value for SQL Server. Top you off. So in the last video, this query got blocked and this query, it doesn’t.
This query runs and returns the values as they existed prior to the update running. And the same thing will happen if I run this update, right, and I changed my last name to Darling Data because Erik Darling married to the data game, right? So we run this.
This will still not be blocked, but it still won’t reflect the change within that transaction, right? That’s still out there in the open. Nothing going on there. If I run this query from within the transaction, well, then I can see all the changes that happened because this query is working within the transaction that made the changes.
And then finally, if I commit this transaction, let’s make sure we’re extra committed to that transaction. And we come over here. Now this query will finally see the changes.
So the difference, of course, is that your read queries don’t get blocked, but your read queries might be seeing older versions of how data existed before changes started happening to it. So that can be great for some people. That can be great for 90 something percent of the people who have a workload in SQL Server because it’s great for most people who have workloads on other database platforms that made better choices of default isolation levels like Oracle, Postgres, and probably DB2 if anyone can find DB2 out there.
So that’s how they’re different. And we can repeat the same thing with the other query that I showed you where it looked like a unique constraint had been violated in SQL Server. Where it looked like we had duplicate values in unique constraint.
In this case, we won’t see any weird results. So we’ll run these first three updates right here. One, two, three.
And we’ll just refresh your soggy memories. There is a unique constraint on Butthead. And so if we were to try to insert a fourth row, it would fail because we would violate that unique constraint. And that was sort of the gist of the last demo where I was like, hey, if enough changes happen and things swap around, your query results could make it look like that column returned non-unique results.
And that would be confusing and awful and terrifying. You would question SQL Server and you’d call up Microsoft and be like, Microsoft, SQL Server has a memory leak. And it’s broken.
You better catch it for whatever kids do these days when they make prank calls. So let’s grab this query just to prove out our point once again. Let’s paste that in there.
And if we run this select, we get exactly what we should. Because no changes have happened with the table. We get Beavis incorrectly saying huh and Butthead incorrectly saying other things. Well, I guess the one at the bottom is right.
But the first two, absolutely wrong. Non-canonical Beavis and Butthead laughs. All of Flutter here in this demo. But now, let’s begin trend and update. And just like last time, we are not going to see any blocking here.
But we’re going to see the data the same way that it was before the update started. And if we run a couple more updates, we are still going to not be blocked. But we’re still not going to see any changes.
Now, if I were committing these changes along the way, of course, we would see them over here. But because these changes haven’t been committed yet, we’re still seeing that snapshot of the data from before the changes. And just like in the prior demo, if I select from this, and here we can see the changes, right?
Then if I commit and then extra commit that transaction over here, those changes will finally show over here. But the important thing is that this select will never look like it returned a violation of our unique constraint on the Butthead column.
So, when I talk about workloads that would benefit from RCSI, this is a big one. Not get, not your read queries, not getting blocked, and returning correct results from prior to modification starting, is usually what people want.
Using no lock hints, being able to see transactions in flight is not good, right? And seeing dirty data, it’s dirty reads. The concern here, of course, is reading stale data, right?
Because if your select queries do require getting blocked, and then seeing changes after the blocking, read committed might be the right isolation level for you.
But with the huge caveats that came with stuff in the last video, about how if there are multiple queries in a transaction that make changes, if your query got blocked after like, like let’s say that there are 10 of them, if your query got blocked on like the fifth one, and then five more things happened, your query would have seen like five things that changed, five things that didn’t change, and then five things that did change, and that can be really screwy for results too.
So, again, no isolation level is generally 100,000 million percent perfect for every workload. However, I do find that the way optimistic isolation levels is far closer to developer expectations than the read committed reality that they’re faced with, and of course the no lock or read uncommitted shortcuts that they take when blocking happens, and they’re like, why God, why have you forsaken me here, letting my queries get blocked like this.
So, that’s the difference between read committed, the pessimistic isolation level, and read committed snapshot isolation, the optimistic isolation level. I hope that this makes things more clear, and I hope that this helps you with anything that you might want to test in your own environment to make sure that if you are going to start using an optimistic isolation level, that you use the right one.
Because remember you have snapshot isolation over here, which requires queries to ask for it, and you have read committed snapshot isolation over here, which affects all of the read queries that come into your database. So, if you find that there are queries that would operate incorrectly under read committed snapshot isolation, you could choose snapshot isolation and have certain queries that don’t require that to opt in to using an optimistic isolation level, or you could add read committed lock hints to queries that do require not reading from a version of the data, so that those queries would obey the locking semantics of read committed.
But, just remember, read committed leaves a lot to be desired as far as guarantees go. The guarantees for read committed, the pessimistic isolation level, are very, very weak. If you truly have queries that need to operate off of the freshest data without having, like, changes and other things happening all around them mess them up, you might be looking at needing to use repeatable read or serializable as an isolation level for absolute correctness.
Because under concurrency, read committed the pessimistic isolation level wipes out pretty hard a lot of the times. Does not, well, I would say it doesn’t live up to expectations, but the reality is, it’s really just behaving as it’s documented and your expectations were wrong.
Read committed is just kind of a crappy isolation level. Even in the crap database, it’s a crappy isolation level. So, before I need to blow my nose or sneeze or anything else that would be untoward to do on camera, I’m gonna end this video.
Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope you missed me terribly while I was away exploring business opportunities in Europe. If you like this video, the thumbs up button is a great way to show that.
I also like comments that say, Yay, good job, Eric. And I also like subscribers, because the more subscribers I have, the more people I reach in teaching people how to use SQL Server correctly, which is nice, because very few people do that.
And I’d like to see more of it, so that one day I can do something else with my time. I don’t know. Start a channel about how to behave properly in a bar.
How to not annoy bouncers. I don’t know. How to not annoy bartenders. There’s lots of things. Lots of things that I could do. Lots of things I could do with my time.
But here I am, talking about SQL Server. So, that’s enough of that. Again, thank you for watching, and I will see you in another video, another time, another place, another you, another me.
Who knows? Goodbye. Goodbye. . .
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.