A Little About Locking Hints In Transactions
Video Summary
In this video, I delve into the fascinating world of locking hints in SQL Server transactions, sharing insights from a recent client project where we explored how to handle concurrency issues effectively. We start by creating a simple table and inserting five rows, then dive into demonstrating various locking hints such as `upd lock`, `row lock`, `repeatable read`, and `read past`. I explain why these hints are crucial for maintaining data integrity while allowing concurrent processes to run smoothly without stepping on each other’s toes. The video showcases how different isolation levels and locking hints can affect transactions, both within and outside the transaction scope, providing practical examples that highlight their importance in real-world scenarios.
Full Transcript
Erik Darling here with Darling Data, and we have a fun video. Today we’re going to talk about locking hints in transactions. I was working with a client recently, and we were talking about various ways to handle concurrency with transactions that might have to deal with each other in kind of funny ways. And when I started showing them locking hints, they were like, but wait a minute. If we start a transaction and we have a select query with these locking hints, isn’t it going to block the thing we’re trying to do afterwards? And it was like, no, it’s magic. And I’m going to show you that same magic here today. Down in the video description, you can find all sorts of helpful links where you can hire me for consulting, buy my training, support this channel, ask me offers hours questions, and of course, there are all sorts of things that you can do aside from that, aside from monetary exchanges, like subscribe and tell a friend, and then more people show up, and I don’t know, maybe one of them. We’ll do one of them will do one of the other things. And that’d be grand. That’d be wonderful. Leaving the house. Going various places. At least they’re closer than Seattle. Data 2 in Nashville, March 6th and 7th of 2026. And of course, Data Saturday, Chicago, March 13th and 14th of 2026. I’ll be doing pre-cons at both of those, so buy your tickets now.
Make me feel like a cool person who sold all sorts of seats and stuff. And I can say, hey, look what a good job I did marketing this thing. You can buy tickets somewhere around those links that are in the PowerPoint thing there. Datatune.conf and datasaturdaychicago.com. So I assume those are valid working links because I copied them from a very trustworthy web browser, internet browser. So anyway, let’s go talk about locking hints and stuff here. So what I’m going to do is I’m going to create a simple table and I’m going to stick five rows in it.
And what I’m going to do now is talk a little bit about transactions and whatnot. So because SQL Server does not have a select for update clause, when we want to, say, select a row that we’re going to do stuff with, that we don’t want anyone else to do stuff with, we have to take some additional steps. I’m beginning a transaction, declare, like having something to hold that value is good. So I’m declaring an integer up there called ID, right? And it’s null to begin with. And I’m going to grab row five. In real life, you would probably have some other logic here, like, you know, like, like, grab a row that needs work done. But for me, ID equals five works just fine.
And then after that select, there will be an update. What I want to do is just kind of show you like, like, a that like within this transaction, everything works fine. And what kind of queries outside of this transaction would be maybe affected by it. So let’s start just by beginning the transaction and setting the ID. So we’re just going to run to the select on this one, right? We haven’t touched the update. That’s kind of annoying. What I hate about this is that like, even though I’ve like done begin transaction, declare, select, if I try to run, if I’ve like paused now. And so if I try to run the update, it’s going to be like, well, you have to declare the variable ID. And I’m like, but I did. And I have a transaction open. Why don’t you remember that?
Anyway, we come over to this window. The types of queries that like that is designed to protect against would be competing queries. Like, so like if this is like a process that you’re writing, where like only one person should or one thing, it doesn’t have to be a person, like one thread or one worker should only be allowed to work on something at a time. The code outside of that process should like can access things normally. So like, I don’t even need a no lock in here. If I just say select from that table, I get all the rows back because the select with the upd lock, that hasn’t taken a lock that competes with a select just trying to read data. Right. So if I were trying to update row five, I of course would not be able to update row five.
But oh, sneaky weeky, I could update row four, right? If I do this and say, hey, go update row. Oh, wait, I have to cancel that first. It was it was being blocked. That’s why I just kept running without saying it did anything. But if I update this for row four, row four can go do something right. So that’s that’s nice. But if I were to try to run another select with an upd lock, then this this select would be blocked because the upd lock is like, wait a minute, I’m trying to upd lock.
You can’t upd lock to right. So like, you know, like if I had a where clause on this for where ID equals four, then let’s see. Let’s do a little experiment together. I didn’t plan on this. Just just occurred to me while I was doing all this. So if I say row four, well, that that that can go and do something right.
So but like the point is that if I were to try to take, say, row five now, right, because row five is the one I’m updating in the other window. If I were to try to do that now, SQL Server is like, wait a minute. Row five has an upd lock on it. You can’t upd lock that row two or you can, but you have to wait.
Now, coming back over here just for a moment, the hints that I have supplied to this query are upd lock, row lock, repeatable read and read past. Now, for a lot of processes, you know, the idea of serializable sounds great, right? Because, you know, serializable is quite strict and would prevent all sorts of strange phenomena.
But if you want this process to run nice and sort of like concurrently, the read past hint is often useful because it like you like basically you want people to be able to like just skip over anything that is currently locked. Right. It’s not like no lock where you can you’ll read the locked row. Read past is just like that locked row is none of my business. I’m just going to move on. I’m going to keep going here.
So like if we come over here and we were trying to say all this stuff with a serializable hint, we would get an error that says you can only specify the read past lock in a read committed or repeatable read isolation low. So that doesn’t do us any good. So but this query here, this will allow us to read everything but row five.
Right. Like if this were a no lock hint, we would get row five back with like I mean, I mean, didn’t change anything. So it would just be whatever data is in row five. It’s just a select query at this point. But so like we just we’re just like row five. You have a lock. We’re not bothering with you. Right.
You are not consuming any of our effort or energy here now. So like really processes like this. And what I want to show you next is just like within the transaction, those locking hints are fine.
But also like like for processes like this, you really have like, you know, to consider, you know, like like I mean, concurrency is obviously the most important thing, because if you want to make something like this multi threaded, you know, you want to have multiple processes, be able to take something and work on it.
Like, you know, design a queue type situation or design like a worker tables type situation. Then these types of locking hints become really important because a you want like every process to be able to go get work freely when it’s ready for work. But B, you don’t want it double working or overworking something that is a either like already in process or something that’s already been processed.
So like those are the things you want to like make sure you bake into a process like this. If I just hit commit on this transaction just to get rid of stuff, I’ll reload the table just so we start clean here. And then I’m going to run this whole thing.
But now with the update. Right. So now the update we have the begin transaction, declare an ID, select the thing we want where ID equals five and then go update stuff down below. So like this all runs fine. So like within this transaction, the locking hints that we’ve supplied with the select don’t don’t like mess up the update.
Like we don’t block ourselves in our own transaction. That would be crazy. It would be insane. Right. Just be bonkers. But we’re still like, you know, still holding stuff out here.
So like now if I try to run this query, now this query does get blocked because that update is doing stuff. If I want this query to do anything useful, I, of course, have to say with no lock. And now I can see the dirty, the dirty read down here where I have added one day to this.
Right. So these are all 1110, but this one’s 1111. Because if you remember, the update down here is just adding one day to that column. All right. Cool.
So this would also, you know, of course, block other updates to row five, row four. You know, we still get in there. But if we were to try to say like, hey, go talk about row, go mess with row five. This would get blocked. Right.
This just drags and drags because row five is currently locked in that transaction. Likewise, you know, you know, trying to select row five with an up D lock hint would also be be prevented. Right. Row four, we can still get at.
Oh, no, I hit insert. I’m never going to recover. But row four, we can still get at. Of course, this is still going to throw an error because you can’t mix serializable and read past. And this thing, of course, would get just not get blocked because we have repeatable read and read past.
But again, we’re just skipping over row five because row five has a lock on it. And we are just like ignoring. We’re ignoring that.
We’re ignoring any locked rows via the read past hint. So, you know, kind of a short video to talk about how process like processes like this generally work. Generally, you know, how to make them nice and concurrent if you want to multi thread them and how to make sure that those threads stay sort of within their own lane and don’t mess each other up.
But yeah, you know, just sort of a little primer for maybe some more videos down the line. Anyway, that’s what I wanted to talk about. And I’m done talking about it.
So now I’m going to go do something else. Thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in tomorrow’s video where we are going to talk about optimizer rules that we don’t have. But well, we’re going to talk about one optimizer rule that we’re getting soon that I’ve talked about before.
But, you know, I want to talk about optimizer rules that I wish we had like that one. All right. Anyway, 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.