Learn T-SQL With Erik: Reads Deadlocking Writes Under Read Committed
Video Summary
In this video, I delve into the intricacies of SQL Server isolation levels, focusing primarily on the notorious “read committed” level and its potential pitfalls. Specifically, we explore a scenario where a read query deadlocks with a write query due to their interaction with indexes. By walking through these examples, I aim to provide you with a deeper understanding of how read committed can lead to unexpected issues like deadlocks, especially when multiple nonclustered indexes are involved. I also discuss potential solutions and highlight the benefits of using optimistic isolation levels such as Read Committed Snapshot Isolation (RCSI) or snapshot isolation, which offer more robust protection against these types of problems without requiring extensive query or index modifications.
Full Transcript
Erik Darling here with Darling Data, continuing on to talk about isolation levels, mainly focusing on what a turd of an isolation level read committed is. And in this video, we’ll be focused in on seeing a read query deadlock with a write query. There will be very similar circumstances to the previous demo, just with a little bit more involved because it is a deadlock, and not just blocking. Again, if you’ve been watching my videos and you know, seeing stuff that I do here, you may have seen this demo before, but you know, we’re gonna go into a bit of detail on it. So if you’ve perhaps forgotten or never knew in the first place, or perhaps you knew about this, didn’t you? It may be good for a refresher. All the same stuff over here, 23 hours of the beginner content is out there and available for you. The course is still at the presale price of $200,000. And that price will be going up to $250,000. And that price will be going up to $500,000 after the summer, when the advanced material is complete. So with that out of the way, let’s look at this situation. Now, what we have going on here is, well, we have a select query. And this select query is going to do a couple neat things.
It’s going to start a while loop. And while the I local variable is less than 10, 100,000, we are going to assign the post ID to this one. And we are also going to thank you, zoom it. We are also going to increment the I parameter in here, we have an index hint for a little bit of demo stability here, no big no nothing, nothing too interesting. And then over in this window here, we have an update query. And the update query is going to do something rather similar, except what it is going to do is basically flip this value for vote type ID back and forth between eight and zero. So we’re saying vote type ID equals set vote type ID equals eight minus vote type ID. So this will either be eight or zero because the vote type ID for this is currently eight. Let’s see, let’s see, let’s see, do we get an estimated plan for this one we do. So if you remember the last video we did on read queries blocking right queries, it’s going to be a sort of similar situation here.
We have an index seek that finds the rows that we care about. And since SQL Server estimates that that more than 25 rows are going to leave this index seek, SQL Server chooses to use the unordered prefetch optimization here. And it does not in this time around opt to choose to use an optimized nested loops, just the unordered prefetch portion. But same deal as before, because we’re using the unordered prefetch every time a row comes out of here and goes into the loop, we come down here to go get a column out of the clustered index.
And for SQL Server will hold on to locks the entire time that we are doing the lookup. The lookup in this case is just to output the column post ID. Oh, I guess that’s the same thing as before. And again, because the prefetch optimization is in play, the locks on the clustered index will be held until this statement completes.
Now, over in the update window, this is where things get a little bit different. Now, if you recall the last one, the last demo on just the blocking, it was only the clustered index that was getting updated. This time, in order for there to be a deadlock, we also need to have a second nonclustered index get updated.
This is what causes the sort of deadlock embrace scenario rather than just a plain blocking scenario. Because now we have two queries that one is holding the object level shared lock. One is trying to update both the clustered index on the table and the nonclustered index on the table.
So we have this sort of circular dependency going on, which causes the deadlock this time around. So if we look at this, you’ll see that we are updating the primary key on the votes table. And we are also updating this other, I mean, we’re updating actually two other nonclustered indexes here.
But we only need one of these nonclustered indexes in order for this to happen. So what I’m going to do is I’m going to start running this and start running this. And then I’m going to hit SP who is active.
And we missed it that time around. There we go. We got it.
Sweet. So here is what this looks like. Now let’s kill this one off. That got a little jumbled, didn’t it? These SSMS tabs get all weird on me when one of them starts, one of them, you hit one and it starts saying executing. And then all of a sudden that tab gets bigger and switching tabs around gets somehow confusing.
But very professional. So here we have our update query right here. And it is trying to take an intent exclusive lock here.
And here we have our select query. And it is attempting to take that object level shared lock here. And they are blocking each other.
You’ll note that session ID 68 is here. And session ID 61 is here. And if we move the blocking session stuff over a little bit closer so we can frame this all up at once, you’ll see that session 61 is blocked by 68.
That’s right there. And session 68 is blocked by 61. Now, of course, the choice of deadlock here is very easy.
SQL Server will always choose the select query as the deadlock victim. That’s what the error message that we get here is. And it does that because the select query is very easy to roll back.
The select query hasn’t done any writes. There’s no log. There’s no version store. There’s no accelerated database recovery persistent version store, rather. There’s no work to do to kill this query off, aside from to say, pow, you’re dead.
But, like, this query over here just runs and does the update until I cancel it. So, like, the update never loses the deadlock battle. It’s only ever the select that loses it.
But we can also take a look at the locks that want to happen here. And you can see for the… This was for the update, right?
Yeah, the update. The update query was granted a whole bunch of locks, right? Intent exclusive. It was granted an X lock here. It was granted an X lock here.
But then there was a couple down here that… No, sorry. There’s just one over here that it is waiting on, right? And it is waiting on this intent exclusive lock on the vote type ID index. And then for the locks for the select query, this one has been granted a few different types of locks, right?
Like, all these shared and intent shared locks have happened there. But then this one shared lock on the clustered primary key, right, where the lookup is happening to, this one is being forced to wait.
So this one is waiting because of the modification query. And this one is waiting because of the select query. And neither one can make progress, right? So they are just stuck waiting for each other.
And that’s why the deadlock occurs. So again, sort of the same situation here. We could, of course, extend the non-cluster… Sorry, we could, in this case, we could extend the nonclustered index so that there is no lookup in the select query.
That would be one way of taking care of this. Another way of taking care of this would be to, of course, use the no-lock hint, which I am, you know, pretty avidly against doing.
But, you know, like I said in the last video, it’s really not no-lock or read uncommitted that’s the problem. It’s the fact that you have to deal with all this crap under read committed that’s really the problem. Read committed is the enemy.
Right. And so a better solution would be to use an optimistic row versioning isolation level, like either read committed snapshot isolation or snapshot isolation, in order to prevent this sort of deadlocking at a grander scale.
The same way you could use that to prevent the sort of blocking we saw in the last video on a grander scale, without having to go through each and every one of your block, like reader, writer, blocking and deadlocking things and saying, okay, well, can I, like, what’s the query plan?
Can I fix the query plan? Can I do this? Can I do that? Can I do the other thing? Can I change indexes? What can I do? The optimistic isolation level thing just fixes all of it in one big go. So you don’t have to go through and nitpick every single individual query.
If you just have a couple queries that this is happening for, then you can probably pretty easily fix this without going and changing any database settings, assuming you’re allowed to change indexes.
But if you have this happening across a wide variety of queries, your life gets very, very difficult when it comes time to fixing these things, because you have to go through a lot of different stuff and fix a lot of different things. And then, you know, you’re maybe not even 100% guaranteed to fix everything.
Not every type of deadlock and not every deadlock situation is entirely unavoidable. You might need to also, you know, introduce, like, you know, lock timeouts, or you might need to introduce deadlock priority, or you might need to introduce some sort of retry mechanism for your queries that end up in deadlock situations.
So there’s a lot more that goes on. Like, you can make your best effort at fixing all this stuff with the things that I’ve talked about, making changes to the queries, indexes, etc.
But at the end of the day, not every blocking and deadlocking situation is entirely avoidable, unless you get to an optimistic isolation level, which resolves things in a far, like, far more, like, far more specific way, let’s say.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video where we will continue to pick on Read Committed, because too many people think that it is too good of an isolation level.
And, uh-uh, not on my watch, pal. All right. Thank you for watching.
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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.