Why SQL Server’s ROWLOCK Hint Doesn’t Always Just Lock Rows
Video Summary
In this video, I delve into a fascinating aspect of SQL Server query hints and lock behavior, specifically focusing on the roll lock hint. I share my discovery about Windows services having a delayed start window of just two minutes, which led to some unexpected manual starts of SQL Server 2022 due to its unique configuration. Moving on from this amusing anecdote, I demonstrate why the roll lock hint is often misunderstood and can sometimes fail to deliver the expected row-level locking behavior. Through a simple query demo, I illustrate how lock escalation can override the roll lock hint, resulting in broader object-level locks instead of the intended key-level locks. This video aims to clarify common misconceptions about this hint and provide practical insights for optimizing SQL Server queries.
Full Transcript
Erik Darling here with Darling Data. So, delayed start. Recently I learned that if you have any services in Windows with a delayed start, that delayed start window is two minutes. I learned that in a sort of a weird way because for some reason SQL Server 2022 has a delayed start when no other version of SQL Server that I’ve ever installed has one. And I was wondering why I kept having to go in and start it manually when it turns out I just had to wait two minutes. So every time I start up my VM, I go smoke half a pack of cigarettes and then come back and SQL Server 2022 is there waiting for me. Just kidding. I can’t actually smoke half a pack of cigarettes in two minutes. I would be I don’t even know how that would feel. Anyway, in this video we’re going to talk about why the roll lock hint is a bit of a misunderstood misnomer in that it does not guarantee that you will only ever take roll locks. And we’re going to do that with a pretty quick demo. I don’t think this one this one hits the five minute mark. I’ll be pretty impressed with myself. I don’t know. Let’s see what happens. We’re already a solid two minutes in and I thought that for some reason I wasn’t recording but it turns out I am recording. So if you notice me look panicked and distracted in the video that’s exactly why. But anyway, we got a pretty simple query here.
What I’m going to do is just select the top 5000 rows out of the users table. I’m going to do this in a transaction again because it lets me talk to you longer. You dreamy, dreamy, constant watchers you. And then we’re going to look at SP who is active. And then we’re going to see what kind of locks this thing takes. All right. Now we’ve got an index on the users table on the reputation column so we can do all our seeking the leads sorry that leads with the reputation column so we can do all our seeking into this into that index to find the values we care about. This isn’t a demo that shows like oh look you don’t have an index to support the roll lock and you can’t take roll locks you’re a big dummy. This is something that people get flipped up on tripped up on whipped up on all the time. So without further ado let’s begin to run this thing and I want you to notice the roll lock hint right down here.
All right. And if we come over here and we run SP who is active you can tell I’ve been practicing this one. And we look at the locks that get taken we notice a bunch of key locks across both of both the clustered and the nonclustered index.
All right. So we got all the data that we needed there. All right. So just lock those things on the keys. No big deal.
In and out. Quick as can be. Groovy. Now where the roll lock hint can fall apart is of course in matters of lock escalation. Ooh la la.
Such big words. So if I delete one zero off of this. So instead of looking for greater than a hundred thousand I look for greater than or equal to ten thousand. And we run that same query.
And we look at SP who is active. And we look at the locks. We will see one big goofy object level lock on the users table. All right.
Request mode X grant count one. So our feeble attempt at only locking rows was escalated to an object level lock because we hit a lock escalation threshold. So the next time you are thinking about using the row lock hint in order for it to be honored at all by SQL Server, you need to have an index that suits the where clause of your query so that you can only take locks on rows in that index.
And you also need to make sure that you do not hit any lock escalation thresholds because if you do, SQL Server will bump you up to a full object level lock. Which is surprising to a lot of people. So, you know, a few things to make sure of.
One, suitable index. Two, you’re not going to go update 50 bajillion rows anyway. Because it’s just not going to work out the way that you hoped it would.
Anyway, I have hit nearly five and a half minutes, which I didn’t expect to do. Thank you for watching. I hope you learned something.
I hope you enjoyed yourselves. Please find it in your big, enlarged hearts to like and subscribe. Or like the video, subscribe to the channel, you know, all that stuff.
And I’m going to go record a different one now that talks about something else. So, yeah, I’m going to go do that. Thanks for watching. Oh, wow. I’m going to actually, you know what?
I’m going to ride this out until I hit the six minute mark. Because I want to spend more time with you. So, that’s enough.
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.
I’m currently migrating over 700 million rows on an isolated server from one table to another. This case is entirely the opposite of your, I’m using with (tablockx) to reduce the number of row locks as this will run the system out of locks fairly quickly. Lock escalation doesn’t actually help in this case because the row locks appear to have remained after the escalation to a table lock. (I wasn’t about to repeat the experience to prove it on a 15TB DB). Part of the problem is that a few million rows in the source already exist in the target and would cause duplicates if I disable the indexes. (Don’t ask, I get the fun ones.)
So I just take the lock on the entire target table. This is a migration on a server which has no users or transactions otherwise.
Had I needed to do this on a live server it would have been considerably different code.
I’ve run into the start issue before with other applications that started before the database was up when both were on the same machine. What I did was set the dependencies on the services properly instead and avoided the “delayed start” setting entirely. That could help but I don’t have a SQL Server 2022 set up yet to verify.
That is an entirely reasonable choice. I’ve run into a variety of situations where skipping row and page locks entirely makes things faster.