All About SQL Server Stored Procedures: Isolation Levels
Video Summary
In this video, I delve into one of SQL Server’s most misunderstood concepts: isolation levels. As always, I aim to clear up common misconceptions and provide practical insights based on my extensive experience working with these settings. I start by explaining why read committed snapshot isolation (RCSI) is often the best default setting for most workloads, offering consistent results without the blocking issues that can arise from the default read committed isolation level. Throughout the video, I demonstrate how different queries behave under various isolation levels using a simple example in the `isolation level test` table. By showing you real-world scenarios and their outcomes, I help you understand when to use or avoid certain isolation levels based on your specific needs and workloads.
Full Transcript
Erik Darling here with Erik Darling Data, subsidiary of Batsmaroo, Inc. And in today’s video, we are going to discuss one of my favorite, least favorite topics when it comes to SQL Server store procedures, and that is isolation levels. The reason why it’s my favorite topic is because I am right about them. The reason why it’s my least favorite topic is because everyone else is wrong I spend a lot of my life trying to impose correctness on people where they are currently incorrect and struggling with just how incorrect they have been for their entire lives when it comes to isolation levels. Now, one thing that’s important to say up front is that everything I’m talking about here, of course, is specific to SQL Server. Nothing that I see here becauseэт Take it ways that there. It’s a good to remember to escape terrllen’t a little uあと. ecstall that a little bit of a little so that SQL Server for using a bit. ESPTest server stuff. But before we talk too much about isolation levels, we need to talk about our current financial arrangement. Mostly, I believe you should give me money. If you would like to become a paid member of this channel and support these videos, you can do that using the link in the video description. And for as little as $4 a month, you can maybe get me a better haircut eventually. If you like this content, but you’ve spent all your money on hair products, perms, bleaches, whatever it is you people do with your hair out there, you can do all sorts of free stuff like like and comment and subscribe, which also won’t get me a better haircut. But I don’t know, it’ll maybe blow my hair back. That would be cool. If you want to ask me a question privately that I will answer publicly, you can go to that link, which is also noted in the video description, and you can submit a question. And I will answer it here on a YouTube video to the best of my ability.
I am primarily focused on SQL Server performance questions. But I get a nice smattering of other stuff in there too. So you know, whatever works. If you need more help than a YouTube Q&A or video can assist you with when it comes to the performance and general well-being of your SQL Server, I do all of these things for money at a reasonable rate. I promise you, you will not be disappointed.
I guarantee your satisfaction. Another thing that I will guarantee your satisfaction with mostly is all of my training content. I have 24 hours of it available. And with that discount code, the final price tag is about 150 USD. And you have that for the rest of your life. There is no subscription required. But of course, if you want to keep giving me money, I will not argue with you.
That is one thing that I have learned not to do is when someone wants to give you money, you don’t say no. Strangers, whoever it is, say yes. Say yes to the dress money. Anyway, SQL Saturday, New York City is coming up May the 10th, taking place at the Microsoft offices in Times Square. There is a performance pre-con by the lovely and talented Andreas Volter taking place on May the 9th. That is a full day session. And you will see me on both days, assuming that my health and well-being maintains through May the 9th. So far, so good. Knock on that wood. But yeah, it would be lovely to see you there. We can give an appropriate amount of affection to each other, whether it’s a handshake or high five or fist bump or elbow bump or whatever you’re into. Whatever it is. Just no tongue stuff, because that would be cheating. Anyway, let’s get on with the party here and let’s talk about isolation levels. Now, I’ve talked about isolation levels a lot on this channel because there is a lot to talk about with them. There’s a full playlist called Everything You Know What Isolation Levels is Wrong, where I talk about many things at great length. If you would like that, you can go watch that for free. You know, finish watching this thing first. This is a nice video, but you can do that.
For most workloads that I see and work within SQL Server, read committed snapshot isolation at the database level is generally the way that things run best. With no interference, the majority of read queries and write queries will stop blocking and deadlocking and fighting with each other.
And you get more consistent results than would provide if you were to take the coward’s way out and just throw no lock hints everywhere. Because it gives you what most developers would expect from a database. Read queries won’t block write queries. That can happen under read committed, the default non-row versioning version of the default in SQL Server. And read queries, of course, get blocked by write queries under that one. And of course, read queries and write queries will deadlock with each other. Another nice thing that you get out of read committed snapshot isolation is consistent results. Like I said before, if you take the coward’s way out and you use no lock hints, you can get very inconsistent results. We’re going to look at a lot of this stuff in the demo portion of this video. You only get one. This is really important. You only get one of those things from read committed, the default isolation level. But you get all of them with read committed snapshot isolation. Read committed, the default isolation level, does not make any very strict guarantees. And of course, your read queries and write queries get into all sorts of terrible blocking and deadlocking entanglements. So the thing that I want to talk about, you know, I’m going to get a lot of today is sort of like, when you’re writing queries that read data, you should have a pretty clear idea in your head, how you would what results you would expect from those, like, like, what results you would expect those queries to return, when they’re running in concert, with modification queries, right, you need to understand, like, what is an acceptable level of fault tolerance for this query to return results quickly. And what results do you want to get back? Like, what results are you okay with this query? Get it, right? That’s an important thing. So I’m going to use the crap database for this one. I’m not going to use Stack Overflow, because a lot of the times when I make a database settings change like this one, I forget to reset it. And then I go to do something else. And I’m like, SQL Server changed. So we’re not going to do that. I’ve got a store procedure that just drops and recreates a table and populates it with some data. I’m just going to use that for convenience down here. Right? So we can we can run this, and it will reset this table called isolation level test. And it’s going to put some rows in there with some numbers, right? So not a big deal. It’s not anything that we need to care about too, too much.
But what I want to show you is different ways that different queries behave under different isolation levels, because that’s important stuff. So over in this window, I’ve got this I’ve got this quoted out for actually, you know, we don’t need this one. We’re not going to not going to get too far into that. But actually, you know what, I do want that for another thing. So we’re going to leave that there. Actually, I changed my mind. What I want to show you first is just that like, this select query running with no lock, nothing else is happening. So I can run this a million times, it’s always going to return return 84,000. Where you have to start asking questions about no lock is like, at what point in queries doing stuff, do you care about this, like seeing like, like uncommitted transactions, right? So like, if we come over here, and I start this transaction to update data, where ID equals nine, the no lock query isn’t going to get blocked, but it’s going to see that transaction uncommitted, right? So this this this number changed from 84,000 to 75,000.
Because we set some amount to zero for this one, then if I delete like all in the same transaction, if I delete ID 15 from the table, and I rerun this, this can immediately see the effect of that delete from the uncommitted transaction. And then if I run this final insert here, and I stick a row in there, like, again, this transaction still hasn’t committed, we still have this, like, this is still in progress, no lock can see, like all of that stuff, like up to like up to when that commit happens, right? So like, that commit finally happens. And like, I can now run this and I get 60,001 over and over and over again, that might not be what you want to happen. Like if you if you think that the correct results are, like, you should only see the end result of this unit of work, no lock slash read uncommitted are not for you, right? If this unit of work means like this signals that like you have completed work on the table in a way where now SQL Server can see the results. No lock is not a good choice, because you can see all the intermediate results from within this transaction.
That’s generally not a good thing. That’s generally not a good thing. If there is any like, like, if anything is going to happen in the world that’s important, based on what the like, like the query results are, not a good choice with no lock, right? So let’s reset the table. And now let’s look at read committed, right? So this database has read committed snapshot, read committed snapshot isolation enabled for it. So if I want SQL Server to obey the semantics of the default read committed isolation level, I need to add this read committed lock hint to both of these queries. Now this go like both of these are going to return the same results of 84,000. Right? So that’s fine. But let’s start, let’s start this transaction over again. And let’s run this update. Now, if I run this, we’re going to get blocked here. Right? So this query is blocked. And now this this delete, I can run this in this transaction. And this this insert, I can run this in this transaction. And now when I commit this transaction.
These queries are both of these queries, these queries are both going to return 60,001, which is like, you know, if you wanted that whole thing to be treated as that unit of work, cool, right? Because you got the final result after that unit of work completed in both of these queries sock. Where things get weird with read committed, though, is when things start happening in like different orders, where maybe like, like, you get blocked at one point, but then data changes in the in the in the table from data you’ve already read or data you haven’t read yet. So what I’m going to do this time is a little bit different. I’m going to begin the transaction. But instead of instead of running the update first, I’m going to run this. Right? So I’m going to up I’m going to delete I there. And I’m going to hit F5 here. And this query gets blocked. Now rather than getting blocked on ID nine, now this thing is blocked on ID 15.
So if I update ID 16. So if I update ID nine here, and then I insert ID 16 here, and then I commit this, these two queries are going to return slightly different results, because this query had already read data from the data from the table and said, I have these results. Right? So like it read that data calculated result from that data got blocked and then data changed from data that already read. So read committed has like, again, very weak guarantees and probably not something you want to rely on. Of course, now that everything is done, I can run these and they both get the same results. Right? So let’s, let’s make sure that that’s fully committed. And let’s reset the table. Now, where like other isolation levels, like serializable and repeatable read, you would get sort of similar results as read committed lock, except if you change data that these queries had already read, like if I did, if I could, if I do the update first, and then change the other stuff, both repeatable read and serializable will get blocked on ID nine, where the update happens, and then read results after everything changed. So it was like the initial run through a read committed lock.
Where that would be different is if I tried to change data that either one of those had already read, then you would probably get a deadlock that those two isolation levels are much more strict. But they’re also much more likely to cause deadlocks when you start like intermixing reads and writes because you have reads now all of a sudden holding locks. Read committed doesn’t hold read locks anywhere takes them and releases them very quickly. That’s why you can get data jumping around in your table, even when like even under read committed, right? So that’s that’s that. Now, if I take out both of these, both of these hints, right, both of these locking hints, and I run this, we’re back to 84,000, because I reset the table, where this is important now, and actually don’t need the second query for this one. Right? If I do this, right, I’m getting 84,000 back from this query, this query right now, because I changed the database default to read committed snapshot isolation. Now I’m using an optimistic row versioning isolation level. So what will happen is if I come over here, and I do like I could do repeat any of these in any order, and it would still be the same, but I’m just going to show you in like the order that I’ve done most of the other demos in. If I do the begin, tran and update, this query won’t get blocked, but it won’t be seeing data changes from that unit of work.
So the question you have to ask yourself is, are you okay with this data returning results from how this data looked prior to this unit of work happening, right? Because I ran the delete, and I ran the update, and we’re still getting 84,000 back. Right? So this is still returning the same number as we were getting when I put data fresh into the table. So the question you have to ask yourself is, when this happens, do I want my query to get blocked and wait for that unit of work to happen?
Or do I want my query to return results as you saw before that unit of work began? Now if I come over here and commit this, right, this is fully committed now. If I come and run this, now I’m going to get the updated result from 84,000 to 60,000. So the question to ask yourself is, are you okay with queries seeing data that’s in flight within this unit of work?
If you’re not, if you need data processing to finish before you want to report changes from that unit of work, NOLOC and read uncommitted are not for you. If you want to, if you want your queries to wait and return results from after this unit of work is complete, then you would want, or rather, then you would want a locking isolation level. Like meaning you would need read committed lock, which is like read committed the default database isolation level for SQL Server.
Depending on if you’re okay, and like, but again, very weak guarantees there, data can move all around in the table when that happens. So like, like those read locks get taken very briefly and then released. So if you’re okay with like data in the table changing all around your query, read committed might be okay for you.
If you’re not, you would probably need either repeatable read or serializable, but you’re going to see a lot more deadlocks and a lot more locking activity with those. Because those read locks like are held onto, right? SQL Server doesn’t just immediately release those the way it does with read committed locks.
If you’re okay with your query seeing the, like the last known good set of like data in the table until that unit of work completes, but your read queries finish immediately, like someone just sitting there like refreshing. And then this unit of work finishes and they get the updated result. Well, then that’s where optimistic isolation levels are okay for you, right?
That’s where you would want to choose is read committed snapshot isolation. If you want it to be the workload as a whole or snapshot isolation, if you want to choose specific queries to behave that way. So rather than thinking about, you know, like thinking sort of theoretically about which isolation level is okay for you across the board, think about the way you want most of your queries to function.
And then think about like when you’re writing a specific query, like what you’re okay with that query being able to see as other units of work, other queries, in other words, in the workload are doing their units of work, right? So like you need to think more about like expected outcomes and what’s a correct result and what’s not a correct result than specifically like which isolation level works everywhere because no isolation level works everywhere. Some isolation levels work great.
Like what I’m saying is optimistic isolation levels work great across the majority of queries and workloads that I see. There’s only some very specific parts of that work, those like only specific queries, only specific parts of that workload that have stricter needs where an optimistic isolation level might not be the best choice. So think about that stuff when you are thinking about what is appropriate for your query to be running under.
Most of the time it is not going to be, excuse me, no lock or read uncommitted. Most of the time it is going to be either what you would see in effect from an optimistic isolation level or what you would get at the end result of read committed, repeatable read or serializable. So anyway, I hope you enjoyed yourselves.
I hope you learned something. I hope that you will watch my full playlist. Everything you know about isolation levels is wrong because I go into a lot more depth on a lot of these different topics, much more than I can fit into this one video. But it’s a good place to start learning more.
Start or progress upon your long journey to being correct about isolation levels. It took me a while too, but eventually with enough trial, tribulation, practice, reading and thrashing my hands against the post, I eventually got things I think mostly figured out.
So we have that going for us. Anyway, where was I? Thank you for watching. Oh, I enjoyed this and I watched the other thing. Yeah, cool.
All right. Well, that wraps this one up. I’m going to get demos and everything prepped for the next video, which hopefully you will stay tuned for. And with all that, once again, thank you for watching. Goodbye.
And I hope you’re happy. Bye. Bye. Bye. Thank you.
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.