SQL Server’s sp_getapplock Is Pretty Cool

Magicool

---

Thanks for watching!

Video Summary

In this video, I dive into a fascinating SQL Server feature called SP Get App Lock, which allows for session-level locking on imaginary resources rather than physical objects like tables or indexes. This can be particularly useful for serializing access to specific sections of code without blocking other operations on the database. By using this method, I demonstrate how two stored procedures can run concurrently but still ensure that only one can modify a shared resource at any given time, thanks to an exclusive lock on an imaginary resource named “Locko.” This example showcases how SP Get App Lock can enhance overall system concurrency and provide a flexible way to manage access in complex applications.

Full Transcript

Howdy folks, Erik Darling here with Erik Darling Data. And I just realized that my VM is slightly off kilter, so I’m going to fix that before I continue recording. There we go. Yeah, I think that’s better. Alright, yeah, now that’s all framed up. Cool. Anyway, hopefully the rest of the video will be flawless. So this video is sponsored by a red pen. Thank you, red pen. What I want to talk about today… Oh, it’s also sponsored by this pair of scissors. Thank you, scissors. What I want to talk to you about today is something kind of neat that I’ve been messing with. And it’s been around for a while. It’s not even anything remotely new. But it’s called SP Get App Lock. And it’s a system store procedure that allows you to take a lock on kind of like an imaginary resource so that you can serialize access to that resource. Now, I know that a lot of people, when they think about like locking and serializing access, they might think about table locks, right? Like not table locks, but like locking a table or locking an index in some way. Or like doing a begin train and locking something that way. This is similar, but different because you’re not locking like a table or index.

or anything else weird. You’re just saying, I don’t want anything else to be able to use this section of code while I’m using it. So I’ve had a store procedure on this window. This is called SP App Lock 1. And what SP App Lock 1 does is since I asked on Twitter and everyone seemed hip to exact abort, I’m turning that on. And what this does is it… Oh, let me knock this out so it’s a little bit easier to read. So what this does is it calls SP Get App Lock and it locks a resource that I called Locko. Locko is not a table, not an index, not a view, not a thing. It’s just an imaginary resource. I’m taking a session level lock and the lock mode that I’m taking is exclusive. Now, I could take an exclusive or an update lock here in order to serialize access to that lock, to that imaginary resource. So I’m just doing exclusive because I don’t know. I’m feeling exclusive this evening.

And after I take this lock, I’m going to update a table called LockMe. And I’m going to set an ID column equal to 2. Then I’m going to wait for 10 seconds and then I’m going to release the lock. Over in this window, I am doing nearly the same thing.

SP App Lock 2 will run. It will… I don’t know why that’s red. That’s weird. Anyway. SP App Lock will run. Use Get App Lock. Try to take a lock on Locko at the session level. Again, an exclusive lock. And this one’s going to set the ID in the LockMe table to 3 rather than 2.

Then this one is going to wait for 10 seconds and then it’s going to release the lock. This one also releases the lock at the end. I forgot if I mentioned that or not. Anyway, these are already created. So I’m going to get rid of this and get rid of this.

And then I’m going to show you a little bit more setup. So I have a table called LockMe. And LockMe has nothing to do with LockO. LockMe is just a table that both of those door procedures are going to try to update.

Right now, I have the ID 1 in that table. Just the ID 1. I’m not playing any tricks with isolation levels here. The isolation level for this session that I’m running everything in is Read Committed.

You can see that right down here. My isolation level is Read Committed. I’m not using No Lock. I’m not using Read Uncommitted. I’m not doing anything crazy like that.

What I want to show you is two things. One, that you can use this to take locks on a section of code rather than on physical objects in the database. And what kind of locks that takes.

And how that can help you improve overall system concurrency. So I have to do this pretty quickly. I’m going to run sp.getAppLock1. Run get2.

And then I’m going to come over here. And I’m going to run sp.whoisActive. And a select. The select query now returns ID 2. Because that first door procedure set the ID to 2.

ID was 1 before. Now it’s 2. So the first thing we see here is that that table got updated. The update finished and it wasn’t locked.

Where it’s interesting is when we look at sp.whoisActive. And we have the wait4 from the first door procedure. And we have this other query called xpUserLock.

sp.getAppLock is a wrapper for xpUserLock. If we look at the wait info for those two columns, we can see that we’re about 4.5 seconds into the wait4.

Remember, there’s a 10-second wait4 in both store procedures. That’s the one from the first one running. And the second store procedure is waiting on an exclusive lock. But the exclusive lock that it’s waiting on is not on the table.

The exclusive lock that we’ve granted to the first store procedure is on locko. And locko is not a real thing. It’s imaginary.

But we’ve locked it so that nothing else can get in and run. This thing has taken an exclusive lock and it’s been granted. All right? You can see that right there.

Pretty cool. If we look at the lockXML for the query that’s waiting, in other words, the xpUserLock query that’s waiting, and we go down here, we can see that this one is attempting to lock locko.

Locko is just trying to take an exclusive lock on this. We can see the exclusive request mode, but this one is being…

Oops. Try that again. This one is being forced to wait. So we have waiting to get an exclusive lock. In other words, what we’ve done is we’ve run a store procedure, completed a modification to the table.

So that table is no longer locked. We were able to select ID2 from the lock. And then we ran another store procedure that wanted to do almost the same thing.

But it wasn’t waiting on the lock on that object. It was waiting on a lock on our imaginary locko resource. If we come back to this window, now that nothing’s running, these both finished, and we run this, and we select from lock me, now we’re going to get ID3 back.

So eventually, this second store procedure that was looking to set ID to 3 did run, and it did release everything. I could go back and forth showing you that this would happen in a different order in the same way if I did it with SP App Lock, if I executed SP App Lock 2 first and then SP App Lock 1, if I change the lock mode to update and all this other stuff.

I would encourage you to go read a bit of the documentation about SP App Lock to learn more. Anyway, that is about all the time I have before I go to bed because it’s late.

but I wanted to get this recorded just in case an asteroid hits tonight or something. Again, this video was sponsored by a red pen and a pair of scissors.

So thank you to our generous sponsors. Anyway, I hope you learned something. I hope you enjoyed the video and I will see you next time or whatever unless an asteroid hits. I hope you enjoyed it.

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.