A Little About LOCK_TIMEOUT In SQL Server
This week, I’ve got a coupon that offers an even steeper discount on my training than the one at the bottom of the post.
It brings the total cost on 24 hours of SQL Server performance tuning content down to just about $100 USD even. Good luck finding that price on those other Black Friday sales.
It’s good from December 23rd-27th, or until I remember to turn it off. Hurry along now.
Thanks for watching!
Video Summary
In this video, I dive into the often-overlooked `LOCK_TIMEOUT` command in SQL Server and dispel some common misconceptions about how it works. Erik Darling from Darling Data shares practical insights by demonstrating a query that simulates a long-running update operation on the `high question scores` table, highlighting how lock timeouts are triggered only after significant work has been done, not before the query starts executing. I also touch on alternative methods to bypass potential lock issues and discuss why optimistic isolation levels might be preferable in certain scenarios. If you’re interested in more detailed guidance or consulting services related to SQL Server performance tuning, feel free to support my channel with a membership or explore the training content available through the links provided in the video description.
Full Transcript
Erik Darling here with Darling Data. I wasn’t sure that I had my microphone on. Whoops. Anyway, in this video we’re going to talk a little bit about the lock timeout command. Though not often used by people, the people who I do see using it often have a lot of strange misconceptions about it.
Like, when it checks for a lock timeout, or what locks might cause a timeout. So, we’re going to do that today. I guess you could call this a little about lock timeout. If you are so moved and motivated by the content that I produce here on YouTube for free, you can join the 30 other people who support my channel with a low-cost $4 a month membership.
It’s a nice way for me to, I don’t know, buy an espresso. Not a Nespresso machine, but like, an espresso. I live in New York after all. Espresso ain’t cheap.
If you don’t want to buy me an espresso, you can like, you can comment, you can subscribe. And while I won’t be able to buy an espresso with any of that, I will feel a little bit less lonely. So, you know, we all win, don’t we?
If you are in need of SQL Server consulting for any of these things, or anything else beyond this, golly and gosh, I’m probably the best person you could hire to do any of it. So, stop screwing up and hiring other people.
You’ll all end up with me anyway. And as always, my rates are reasonable. So reasonable, in fact, that I, year-round, blow the doors off everyone’s Black Friday sales. You can get 24 hours of SQL Server performance tuning training content for about $150 USD if you go to that website and use that discount code, both of which are also available.
Wouldn’t you know? There’s a link in the video description. You can become a member. You can get the training. You can get me.
Ah, it’s all there for you. All you have to do is click. Upcoming events, end of year, nah. 2025, hit me. Hit me with them. I’ll be there.
And anyway, let’s talk a little bit about Lock Timeout and SQL Server. So, what I’m going to do is I’m going to run this query. And I want you to understand that I have some hints on this query because I’m working on some other demos, so I have an index hint.
So I use the clustered index. Don’t worry about that so much. And then I also have a force order and a maxed up one hint on here. But I have those there for good reasons.
And this is mostly just to simulate a long-running query that does an update. Okay? I am updating a table called high question scores. And I am updating that with the score from the post table joined to the users table where post type ID equals one to get the right data.
So I’m going to run this query without any competing locks going on. And we’re going to look at the query plan. We’re going to talk a little bit about the query plan.
And then I’m going to show you how Lock Timeout can actually get sort of messed up by queries that look like, or rather, locks that may not be involved in the thing we’re trying to lock.
The thing that almost everyone thinks about Lock Timeout is that it checks for locks. And then if there are no locks, it runs the query. But that’s not true.
Lock Timeout kicks in for your query. And if your query encounters any locks that surpass the Lock Timeout, it will kill your query. All right?
So this thing runs for about 12 seconds. And this is the shape of the query plan. All right? This stuff over here. We scan the clustered index on the users table. And then we scan the clustered index of the post table.
We do a hash join to bring those tables together. We do an aggregate to find our max. And then up here, we join the result of that to the high question score table. And then we update the high question score table here.
Now, one might think that this query would check for locks on the high question score table. And if there are any locks that would cause this query to be blocked or potentially to deadlock, it would just say, no, the lock timed out. But that’s not really the way it works.
So I’m actually going to use an update that I used in my everything you know about isolation levels is wrong series. And this update does something rather cute and clever. Let’s just make sure this is all rolled back.
I see an error message in the corner there. So I assume it is. This, this, what this update does is it adds one trivial millisecond to the very last row in the post table. Okay.
So the very last row in the post table, we’re going to begin a transaction and we’re going to, we’re not going to roll it back. And the rollback is quoted out, but notice we’re updating the post table here, right? So I’m going to run this and that’s going to finish very quickly.
We’re going to roll that back afterwards. But now like watch, watch what happens when I run this. What a lot of people expect is that this query would check for a lock for a hundred milliseconds. And if that lock and if it find, and if there’s a lock for more than a hundred milliseconds, the query doesn’t run.
That’s not at all what happens. If I run this query, it’s going to run for almost the full duration of what it, what it would have run for before to do the join, do the three joins, right? Users to posts.
And then the result of users to post to the high question score table, and then update the high question score table. This executed. You can, I have to move over a little bit so you can see it. We don’t have a query plan for this because of course, you know, we, the query failed, so we don’t get a query plan.
But this ran for nine seconds. Nine seconds is just about the amount of time that we had in like the, the join between users and posts and other stuff, right? Like that was about nine seconds in there.
So this, this query actually didn’t check for locks first. It started running in countered locks and then the lock timeout hit. So because we’re like, and it’s not happening up here.
The only other thing going on on this server is this, where we’re updating the, we were updating the post table, the very last row. So SQL Server scanned all of the users table, did whatever it had to do to prepare the hash join, started scanning the post table, got to the very last row in the post table, and then said, uh oh, I’m locked. So we had the, that whole thing happen before the lock timeout kicked in.
And this lock is not on the table that we’re trying to update. This lock is on the post table, right? So you can kind of figure out now why I had the query plan look the way it does, right?
Because we want to make sure that we do a bunch of work to show you that this query actually runs for a good bit of time before the lock timeout kicks in and kills the query. So, uh, if we roll this back, uh, then everything will be fine and this query will be free to run and do its thing. This is going to run for another 13 seconds, but I do think it’s important for people to understand where they’re going to use things like this, that they understand where in the query the lock timeout intervenes.
It is not before the query executes. It is the query does not check for locks first. The query starts running processes as much as much as it can.
And then at the very end, uh, or rather, uh, like as it’s, as it’s running, if it encounters any locks, that’s when lock timeout kicks in. So lock timeout kicked in and the execution plan way down in here. Right?
So we did a lot of other work before SQL Server was like, Oh, I can’t do anything else. So be real careful when you’re using lock timeout, make sure that, uh, you understand what locks will cause your query to hit a lock timeout. It might not be looking for locks on the table that you’re modifying or even like, uh, I mean, I guess it would happen if you were just, if you were doing a select, right?
I think this is a particularly good illustration of a query that would have to take these kinds of locks up here. That doesn’t have to take anything except normal sort of read committed locks in here and doing its thing. Now, of course you could probably get past this if you are the type of, you know, potato brain, foul mouth dim that uses no lock hints.
Um, you could even get past this if you were using read past hints, right? Read past would be a one way to bypass any lock data. That would be another way that you might do this though.
You might end up missing some valuable data that is locked. Um, uh, so read past, not always a hot choice, no lock, never a hot choice. And of course, uh, our, our lovely optimistic isolation levels like, uh, read committed snapshot and snapshot isolation would avoid this scenario entirely.
Of course, if you were the type of big brain, constant light bulb overhead, uh, real bright bulb person who used optimistic isolation levels, you probably wouldn’t be the type of person who’s worried about setting lock timeouts anyway. Would you? You wouldn’t, wouldn’t happen.
Nope. You would just be free to do your thing. Be free to be you and me. Anyway, that’s a little about lock timeout. I hope you enjoyed yourselves.
I hope you learned something. Uh, thank you for watching. And, uh, I will see you in the next video, which I don’t know, I don’t know what it’s going to be about because, uh, I haven’t gotten there yet. But, um, I think I have some, some, some written blog posts that I would like to have in video form because, uh, videos get a lot of views and blog posts I don’t, I don’t think anyone can read anymore.
So, uh, I’m going to, I’m going to spoon feed you video content. And, the event. Uh, that’s a good thing in this video, which we have.
adulation声 with power sound of Ruby S rebels.
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.