Learn T-SQL With Erik: Transactions and Blocking and Confusion
Video Summary
In this video, I delve into the world of transactions in SQL Server and explore how they can lead to blocking issues that might seem perplexing at first glance. We walk through a practical example where an update and insert operation within a transaction block a simple select query, highlighting why using `NOLOCK` hints might not always be the best solution. I also discuss the limitations of the `sp_whoisactive` stored procedure and the `BLOCKING_PROCESS_REPORT`, showing how they can sometimes mislead you about what’s actually causing the blocking. The video emphasizes the importance of considering transaction length and isolation levels to avoid unnecessary complications, urging viewers to keep transactions as short and focused as possible for better performance and reliability.
Full Transcript
Erik Darling here with Darling Data. And we’re going to, of course, have some more T-SQL laughter, fun, joy. You know, all the stuff that comes along with writing T-SQL, really, aside from, you know, the pain, confusion, annoyance, all that other, all the other stuff. We’re going to have the fun part, right? So again, all 23 hours of the beginner content out there live for you to start consuming, digesting, mulling over. And it is all available at the pre-sale price of 250 US dollars. That’s good for life. But the price is going up to 500 bucks when the advanced material drops. So, uh, buy it now. I guess, I guess the call to action there. So in this video, we’re going to talk a little bit about, um, transactions in SQL Server. And specifically, when it comes to dealing with like blocking stuff, how like, what you see by the way you see is that you’re going to be doing a lot of things. In the way you see that the block can be very, very confusing and misleading.
So, uh, I have an, I have a transaction here and within this transaction, we have two statements, right? We have an update and we have an insert. Um, and over in this window, we have a select query that’s going to try to select data. Well, I guess it worked there. Uh, look at that. Hey, it worked. We’re not running anything yet. Good job us. We have a select query that’s going to try to select some data from the users table. All right, good. So let’s come over here and let’s begin our transaction. And now let’s update, uh, this one row in the users table. And, uh, let’s pretend that that update worked very, very successfully. And now let’s, uh, let, now let’s insert a row into the users table. If we come over here and try to run this query, and again, we’re not, we’re not going to be using any no lock hints, right? We can, well, I mean, I can show you, like if we say, uh, again, you’re like with, uh, no lock, da, da, da, da.
And we run this. Now we get, uh, this back. Of course, you know, over here, we updated the age column. So let’s, let’s, let’s add the age column in so we can see exactly what hell no lock has wrought. So we can see that we have updated John Skeet to be 21 years old. Maybe this is great for John Skeet. I don’t know if John Skeet liked being 21, but John Skeet is now a 21 year old, uh, software developer. But without the no lock hint, we, uh, just get blocked forever and ever, right? This can, this, because we, this, that other query has a lock and it’s not doing anything bad to anyone aside from, you know, like it’s not doing anything illegal, right?
It’s not, we’re, we’re, we’re annoyed. We’re, we’re definitely imposed upon. We are aggravated by being blocked, but that other query, that other transaction is not doing anything that it’s not supposed to be doing. If we come over here and we run SP who is active, the results are going to be somewhat confusing because what it looks like is happening is an insert into the user’s subset table is blocking a select from the user’s table, right? And we can see that this query has now been, I’ve been talking for almost 30 seconds while this poor query has been blocked.
Now we can see, uh, if we use the get locks parameter that will enumerate all the locks. Now I don’t recommend using this if you’re in a system with a lot of blocking going on, because this can take a long time. Like if there’s a lot of blocking sessions and like this can, it’s like, it’s a cursor that does this and like XML stuff.
So don’t, don’t use get locks if you’re in a real blocking crisis. Um, but if you come over here and we look at the locks at the end, that the select query is trying to take, it’ll, it’ll say, yeah, we were trying to take a lock on the user’s table. Like we’re, we’re, we’re just in there and, but up here in the, in the locks XML for this one, we’ll see two different things.
We’ll see that we have locks on both the user’s table from that first update that ran in the transaction. And we have locks on the user’s subset table. So we can at least dig in a little bit further, but if you’re, if you’re running SP who is active without get locks, without the get locks parameter, you’re going to not have that information, right?
We, we don’t have that handy XML thing over here. All we see is this insert blocking the select. And this could be very confusing because this is a completely different table.
And how could this completely different table be blocking a select from, from this other table? It’s, it’s, it’s not a very fun situation to be in. So if we just, you know, roll this thing back, right?
We say, Hey, you know, screw it. We, we made a mistake there. Let’s get rid of that. Uh, now we can run this and we’ll see John Skeet’s age is back to null. John Skeet, he’s null years old.
He’s not, he’s not 21, but what’s even worse is the block process report. Now the block process report is considered by Microsoft to be a best effort report of what was blocking. Unfortunately for us, unfortunately for us, what we see in the block process report is the same thing that we saw in who is active.
What we have, what looks like an insert into users subset blocking a select from the users table, right? That’s all this stuff over here from users. Now, if you like in real life, if you were to like, you know, you know, run, run SP human events block viewer, my wonderful free store procedure, uh, you would look at this and you would say, Eric might be on crack.
Eric, Eric might, Eric might have done something weird in this, but I, I was not me. I did not do this. Uh, what, and, and, you know, you might start looking at the user subset table and being like, well, is, is there a, is there a foreign key to, to users?
Is there, is there a trigger? Is there a, is there some indexed view that’s, that, that holds these things? No, it’s just the block process report being annoying, right?
Because all this stuff happened in the, in the scope of a transaction and the locks on the users table from the earliest statement in the transaction are still being held. Even though we have moved on and done something else in the transaction, uh, is blocking our select query. No wonder people just put no lock everywhere.
This stuff sucks, right? But this is where a row version and isolation level would help you. Cause then your select wouldn’t get blocked and it wouldn’t read wrong data. But you know, the block process report, if we scroll way over here and we click on this and we scroll past all this stuff that got added in SQL Server 2022, uh, then, you know, we will at least be able to see, uh, some more information about what, what went on in here.
Uh, way earlier in the XML, there’s like this object ID and some other stuff. And depending on how nice the block process report is, is feeling, sometimes you’ll get, you know, uh, sometimes you’ll get an actual object ID that works. And sometimes you’ll just get some weird randomish number.
I don’t, where did this come from? I don’t know. So do either of these objects have this object ID? Nope. Certainly do not. So here we are.
Anyway, uh, the block process report, well, can, can, well, it can be very useful to show you generally what’s going on with blocking is, uh, sort of not your friend. But there’s, there’s a, there’s a larger message here. And that is that, uh, something, something that a lot of people misunderstand about transactions is lock lifetime.
So that update ran, right? We started a transaction. We ran that update.
We hung around for a minute, smoked a few cigarettes. Then we inserted some data into another table, smoked another few cigarettes, and then tried to run that select query. The whole time that we were being cool, partying, smoking cigarettes, drinking champagne, doing all that fun stuff, those locks were being held from the update. The more stuff you put in a transaction, and the longer that stuff takes, the longer those locks hold, the more of a chance you have of causing problems.
Again, like, you know, like people just use no lock everywhere. They’re like, I don’t want these problems. But this would cause problems for another, for other competing modification queries too, right?
Like, like, like other updates, inserts, deletes, all that other merges, all that other good stuff. That would get blocked as well. So think about transactions, uh, and the length of time that a transaction is open.
Sort of being like the surface area of your query. The bigger that surface area is, the more problems it can cause. The more problems that can be caused to it.
So if you’re in the habit of using transactions, don’t shove everything in the world in there. Like, it’s gonna, it’s gonna hurt you, right? Especially because you’re using SQL Server and the default isolation level is read committed.
Right? Otherwise, you read a bunch of wrong data with no lock. So, uh, when you’re, when you’re using transactions, please make sure to pay special attention to which queries you’re putting in there. Make sure that they all actually need to be logically grouped together.
Make sure that they all actually need to commit, sorry, succeed or fail altogether. Uh, tune those queries as diligently as possible. Do not put anything in there that does not need to be in there.
Do not put any extraneous calls, especially outside of the database in there. I worked with one client years ago who was, uh, in a transaction doing a bunch of stuff. And then also using XP command shell to call like an executable and like go like, like write a file and then upload that file.
And then like do some other stuff. And then all that other extraneous database stuff was real bad time. So, uh, be very, very careful when you start using explicit transactions because the lock lifetime on those is from when you take those locks, right?
When that update took locks until we hit rollback there. So be very, very careful. Be very, very diligent with your transactions.
They should be as short and sweet as possible. All right. That’s probably enough here. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something and I will see you in the next video.
We’re going to, uh, I think we’re going to talk about the serializable isolation level next. Cause boy, boy, howdy. She’s a humdinger that one. That’s serializable. Anyway, thank you for watching.
You’re very, you’re very, you’re very smart.
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.
Erik on crack sounds like a fun video. I see a great need.
Well, you never know, in this economy.