Learn T-SQL With Erik: Query Behavior Under Read Committed Row Versioning
Video Summary
In this video, I delve into the nuances of optimistic isolation levels and row versioning in SQL Server, specifically focusing on how certain query structures can lead to unexpected results when using an optimistic isolation level like `READ COMMITTED SNAPSHOT`. By walking through a practical example involving seat assignments at a dinner table, I illustrate how queries that span multiple phases (select and update) can be problematic. This video is part of a comprehensive beginner content series from Darling Data, where all 23 hours of material are currently available for pre-sale at an exclusive price of $250. With advanced content set to launch after the summer, this offer will double in value, making it a fantastic opportunity to invest in your SQL Server knowledge now and into the future.
Full Transcript
Erik Darling here with Darling Data. And we are going to talk about, in this video, where an optimistic isolation level, a row versioning isolation level, may not be perfect because of the way you have written a query. All right. Now, there are all sorts of things in code that may attempt to enforce integrity of some sort. It may be referenced referential, it may be business rules. It might be in a trigger, it might be in a procedure, it might be in a function, who knows where you’ve hidden these things. And that code may not work the same way under a row versioning isolation level that it would under a locking isolation level, unless you add in locking query hints. Now, before we do that, all 23 hours of this fantastic beginner content is available to you to start watching right this instant. You push button kids. You don’t know how easy you have it. All for 250 bucks currently on the presale price. That’s good for the rest of your life. And that will double in value, at least, when the advanced material goes up after the summer. And you’ll wish that you had spent less now. So you should do that now. Anyway, let’s talk about this stuff. So I am going to create a table and fill it with a table and fill it with a little bit of data. And this is what the data in our table looks like. All right. So I’ve got some lucky fans in here. And what we notice immediately about this table is that there is a there is a seat at this table. Right? There’s an empty seat at this table, we have five lucky fans who are going to eat dinner with someone, but who will they eat dinner with? That’s the question.
Now, when I said, because of the way you’ve written the query, we need to talk about that. So in right now, in my Stack Overflow database, I have read committed the read committed snapshot isolation level turned off, it is disabled, you can see the off button right there. And this is the query that will be running. What we’re going to do is we’re going to declare a variable up here to have a name, right? Because we have to we have to give that seat to somebody in this case, it is going to be Eric. Right? Sounds good to me. I like dinner. And what we’re going to do is we are going to run a query to select the top one seat that is free.
Right? So we had that one free seat. And that that free that seat had is free equals one and had a null for the name, we don’t need to check that though, all we need to see that see is that the seat is free. And then based on this free seat, we are going to update the table. And we are going to set is free to zero and assign that seat to a lucky contestant. And what I’m going to do is essentially output the rows that get inserted. So it’s a little bit easier to see exactly what happened when the query ran.
And then, of course, that that CTE, sorry, that CTE gets joined to the table based on the ID that we find here. So under the normal circumstances, or rather under read committed locking, assuming that you don’t have no lock hints everywhere, because if you have no lock hints on these queries, you’re in, you’re not, you’re going to see weird results no matter what. But assuming that you are using read committed locking and you’re depending on that locking to, to give you the behavior that you want your query to have.
If we begin a transaction and we run this update, we will see that this, this lovely Eric right here acquired seat number one. And we have set his name here and we have said that you are, that seat is no longer free, which means that if we come over here and we try to do the same thing, right? We do this stuff and we try to run the same query.
This query is going to get blocked, but importantly, most importantly here, this query is going to get blocked here. This is where this query is going to get blocked because that other query has an exclusive lock to update the, update the, uh, the dinner plans table. It’s, it’s going to get blocked here.
The select gets blocked. So if I try to run this, it’s going to be blocked and we can, we can tell it’s blocked because it’s just sitting there spinning and nothing good is happening, right? We’re not getting any, uh, not getting anything out of this at all.
We are, we apparently have some sort of police emergency outside. Thank you. Thanks for keeping us safe. Uh, but we are completely blocked up here.
So if I come over and I commit this transaction, this will, uh, this will go, this will be committed now. The, the change that we made here and this query will find no free seats, right? So this thing right here, we got no rows back from the output clause.
And you might think, Hey, this is great. Read committed locking. Amazing. You know, this query was blocked for a while while we were talking about stuff and the, the, the, the, the federalities were outside doing something, who knows what.
Uh, but like this, this gives you, I guess, what would be a correct results. We did, we, we did not lose the update that the first query ran because this, and this query did not overwrite it because this query did not find a free row when this select query finished running. As soon as that lock released and this query was allowed to run, it was like, there’s no free seats.
Wah. Ah, that’s a bummer. This backwards Eric can’t possibly have dinner now. So let’s reset those tables. Right?
So we have set name back to null and we have set is free back to one. And that’s all for seat number one. Uh, and now we are going to come over here and we are going to set read committed snapshot on. This might take a second based on, cause we have the, the, the rollback immediate thing happening, which is always, always the best way to run your, uh, your changing database read committed state.
And, uh, let’s do this again. Right? Let’s, uh, let’s re, let’s rerun this.
So we are going to, uh, run this whole thing and this is going to complete immediately. Right? So, uh, we see that, uh, we see that forwards facing Eric has gotten this seat and this seat is no longer free. Now, since now that we’ve done this, what’s going to change is that when this update ran or like, well, I mean, this update is currently running.
We have not committed this update yet. Uh, this update is generating a version of this row. Right?
The row, like the row that we found here, this update has generated a version of this row where is free equals one. And the name, the name is still null. So if we come over here and run this, it’s still going to get blocked, but it’s going to get blocked in a different place now.
So before, when we ran this, this part of the query, this select got blocked. The select got blocked because of the update lock, but now this part of the query, this select can run and read the version of the row that got created when that update happened. What’s blocked here now is this update trying to change data in the table, actually trying to change that same row.
So if we come back over here and we commit this transaction and we come back over here, we’ll see that now backwards Eric has overwritten forward Eric. Okay. So because of this query got blocked in a different place, the select didn’t get blocked.
The update got blocked. The select found a row in the row versions and said, oh, cool. Look what I found. It’s a free seat.
And then updated the table to give that free seat to someone, even though that seat was taken fair and square by forwards facing Eric. So that’s not good. Right.
So this is not a situation that we want to run into. Now, to make things perfectly clear here, though, if we had just written the query like a sort of normal human being, that situation would not arise because there wouldn’t be two phases to the query. There wouldn’t be the select phase to locate a row to change and then an update to change that row.
If we just ran an update top one to set whatever seat to Eric and said is for equal zero, we wouldn’t have had that because the update would have done would have just blocked the other update and we wouldn’t lose it. If you have code that relies on locking for that sort of behavior and you want to switch to an optimistic or rather a row versioning isolation level, you may need to look at something different. You might need to do something.
You might need to change your queries. So if we come back over here and we reset this, well, this will go back to square one and we still have read committed snapshot isolation enabled and we are still running under the read committed isolation level. Right.
We set this here so that the query is free to use the row versions when it when it runs. And let’s do the same thing now. So let’s run this up to the commit transaction. Do that and now let’s come over here and just run the whole thing.
This query gets blocked again, but now it’s the select that gets blocked again and not just the update. So if we come over here and commit this transaction and we come back over here, this query will go back to not having found a row because now this select would have been blocked rather than just the update getting blocked because we added this read committed lock into it. The read committed lock in makes this select obey the locking semantics of read committed locking and not use row versions to go find this stuff.
This thing got blocked looking at the table. So if you if you are looking at switching to using a row version isolation level and you have maybe code that looks like this or you have code in triggers or other places that might like, you know, enforce some sort of integrity or business rules or something. You might need to start looking at that code to figure out if you need to add those read committed locks lock hints to it.
If all of your queries already have a bunch of no lock hints on them, you don’t need to do that. But you do need to get rid of the no lock hints. So your queries start using the row versions and stop doing dirty reads.
All right. I feel like this was a complete video. I feel like we have we have we have lived up to expectations here. All right.
We have we have completed our intended course of action. So thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video. Next. We are actually done talking about transactions and isolation levels after this, at least for the beginner content.
After this, we have a bunch of programmability stuff to talk about. So, of course, there’s there’s a lot in there. So, oh boy, more videos for you.
All right. You’re welcome. Anyway, that’s good here. 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.