A Little About sp_getapplock And Error Handling In SQL Server
Video Summary
In this video, I delve into the nuances of error handling and connection scoping when using `sp_getapplock` in SQL Server. Specifically, I explore how choosing between session or transaction as the lock owner can significantly impact your approach to error management. By walking through practical examples, I highlight the importance of proper error handling techniques, especially when dealing with locks that might not be released automatically upon an error. I also discuss the limitations of `try-catch` blocks in SQL Server and explain why sometimes they may fail to catch certain types of errors, leading to potential issues like lock leaks.
Full Transcript
Erik Darling here with Darling Data for Business Enterprise. Put some more descriptors on my company name. Maybe that’d fool people into buying more stuff from me. Oh, I’m kidding. Y’all buy plenty of stuff from me. You should still buy more, but I’ve already bought a decent chunk anyway. So this video, I want to talk a little bit about something that came up in recent conversation with a group of dear friends, and that is error handling and sort of connection scoping with sp-get-app-block. Because sp-get-app-block is a very, very underused feature of SQL Server that can solve a lot of problems for you, but needs some sort of special handling.
So when you use sp-get-app-block, you have two options. You can use it like this, and I’ve shrunk myself down because there’s a bit of text on the screen to begin with. I may embiggen myself when there’s fewer texts on the screen. So when you use sp-get-app-block, you can have the lock owner either be a transaction or you can have it be a session.
There are good uses for both, and there are good reasons for both. I think most commonly I see people use, and by people, I mean mostly me, use session because you either might not be operating in the context of a transaction, because when you use transaction, you have to be in a transaction. You have to create a big begin transaction.
So I end up using session mostly, either because A, there’s no transaction to begin with, or B, there might be multiple transactions. I don’t mean nested transactions. I don’t mean begin tran, blah, blah, blah, begin tran, blah, blah, blah.
I mean, like, you might have different groups of queries nested into different transactions, and you might, like, for you would have to, like, recall sp-get-app-block for each one of them. Now, there are totally valid use cases for that, but they’re pretty rare, and they’re probably not worth covering in any great detail here. So I usually don’t use sp-get-app-block like that, but it does have an upside, and that is that when you use transaction, then the app-block either commits or rolls back with you.
When you use session, that doesn’t happen, and that can change how you do error handling with sp-get-app-block, because if you don’t handle errors correctly when you use session as the lock owner, you could have stuff get stuck for a really long time. So let’s look at another thing that’s interesting, and that is that there are errors in the world in SQL Server. Well, not the whole world, the SQL Server’s world, that are unaffected by try-catch, right?
So these are errors that you can hit where you will not get to a try-catch block. You will just either return the error message up at a higher place, or your session will be dead and you won’t hit the try-catch block. So that’s cool, too, right?
So the first one is warnings or informational messages that have a severity of 10 or lower. We typically don’t worry about those too, too much, do we? The other one is errors that have a severity of 20 or higher that stop the SQL Server database engine.
Be careful of those severity 20 plus errors. And then also things, I’m going to zoom down a little bit further there, attentions. So like if you have a client, like if you have a query timeout of like 30 seconds and you get a query attention, blah, blah, blah, blah, then that also won’t get you to a try-catch block.
Also, if you use a kill command, you will not get to a try-catch block. There are some other, again, sort of like higher level errors that will prevent you getting to a try-catch block. Compile errors, statement recompilation, object name resolution, that’s what all that stuff says up there.
And so those are also things that will not, will like cause an error, but not the kind of error that a try-catch block will try to catch. Or maybe it will try and it’ll just drop the ball. I don’t know.
Could go either way on that, right? No, we’ll have to work on, we’ll have to have SQL Server shag some fly balls this spring. All right. So we have this table, cutely named lockme, and we’re just going to start that thing from scratch. And we are going to stick one row into lockme.
And it’s going to have a value of zero, which is the only number that exists in every language. Not true. Demonstrably not true.
Not everyone calls it zero. Anyway. You know what? There’s a lot of dead space on the screen. Let’s get big darling back here. That big darling.
There we go. That enormous noggin taking up your screen. There we go. Punch the white space. So the first thing I’m going to do is show you what happens when, now we’re not going to be using the spgetapplock that we did up there.
We’re going to use spgetapplock and the lock owner is going to be session, or session. Either way, right?
And we’re going to get a lock. We’re going to update the table lockme. And then we’re going to simulate some crap happening for like 10 seconds. And then we’re going to throw an error.
All right. And then after we, after that error occurs, we’re going to do this. And we’re going to, well, we’re not, we’re actually not going to do this. The store procedure is going to go kaput.
Right? And notice there’s no error handling here. There’s no try catch block. Right? There’s just a commit transaction, which is the stupidest way to do anything in SQL Server. If you’re going to start a transaction, you should definitely have a catch block because who knows what will screw up in there.
And you might have stuff that you need to clean up if something goes wrong. All right. And the second store procedure is going to do nearly the exact same thing in the same stupid way with no error handling. Right?
So let’s create both of these just to make sure that, well, just to make sure, because I have two other versions of these a little bit further down and I need to show you what happens in different scenarios. So we’re going to, we’re going to rather than like quote in and out a bunch of additional code, we’re just going to create them as new procedures, each one.
So in a couple of new windows over here, I have this. And you’ll notice that I have a specific release app block call in both of these because we’re going to need it. And then over in this window, I have SP who is active.
All right. So what we’re going to do is run sp get app block here. And well, sp get app block one here and sp get app block two here. And when we get, and let’s look at the, let’s look at the who is active.
We have some blocking going on. All right. And what’s cute right now is that we know that we had a 10 second wait for in there, but this, this lock wait is just going up and up and up, right?
We’re like 20 seconds. Now this, this one, the second query is still in SP get app block two getting blocked. Right.
And this first one has thrown an error that says divide by zero error encountered. Now it says the query completed with errors, but it’s still holding onto locks. All right.
So we need to run release get app block here, which will allow this one to run. Right. And if we run that again, we’ll say, yeah, that doesn’t exist. But if we run this one now, or rather we, this one is able to run now. Right.
Look at what, what’s happening is, well, nothing. It, it, it finally, it finally ran and failed like a millisecond before I got to who is active. So whatever we hit this. Right.
And now we have a divide by zero error. And we have, we, we’re still going to have an open lock. Right. So we actually have to run this here to, to release the app block that that thing took. Okay.
Not an ideal situation, not an ideal situation at all. So let’s go down a little bit further and let’s look at some error handling. Let’s look at the same store, two store procedures, but now there’s some error handling, but we don’t have the right kind of error handling.
So if you remember what the documentation said, if we used transaction here, then the rollback would take care of it. But if we don’t, we don’t have it, we don’t use a trend. We don’t use transaction here.
Then like we’ve still won’t clean that up just with a rollback. Okay. And like the, this store procedure is just a redo of sp get app block one. And there’s one right below it that does the same thing with a sp get app block two. So we’re going to do this.
We’re going to create these procedures. And we’re going to run sp get app block one here and sp get app block two here. And we’re going to see the same thing happen, right?
That wait for is going to take up to about 10 seconds. And at the 10 second mark, it’s going to throw an error and not be there anymore. And now we’re just stuck again.
Right. Because the rollback didn’t roll back the app block. So even with error handling that does a rollback, like checks up to the transaction and says, duh, get out of here.
Roll yourself back. You bum. Dirty bum. Pay your bar tab. This thing still gets stuck because we don’t actually do anything here. So same deal.
If we go away, single prompt, if we release this app block, and now we have no app blocks left to have and to hold. This one will get to about 10 seconds from whenever we killed that off. It’ll get to that divide by zero error.
And then we’ll still be stuck with a crappy app block. So let’s release this one. And let’s go look at what you should be doing if you’re using sp get app block.
Right. Which is you need to put your release lock. Here.
And here. All right. So let’s go run this. And create both of these.
And now, when we run this. And we run this. This should get to about the 10 second mark.
And remember, they’re still going to be blocking. Right. And we go look over here. They’re still blocking. But now, that ran. But in the try catch block, we should have released the lock. And now, when this thing has gotten to about 10 seconds.
Well, it’s funny because now with the error handling. Because we have the try catch block. It actually says, query executed successfully.
Because we handled the error. We handled the error. We actually didn’t surface the error or do anything. If you saw the query executed successfully in the last run, you wondered why. That’s why.
I should have stopped and said it then. Sorry about that. But anyway. Because we put the error handling in there. And now we have instead. Well, as well.
In addition to. We have release app lock here. And we have release app lock in the catch block. These things will have released their locks in the error handling. And we won’t have an app lock to release here.
All right. So if you’re using. SP get app lock for anything.
Or you want to use it for anything. You think it is a cool new bright shiny. And you’re all this. Even though it’s like old as hell. You see it’s a cool new bright shiny. And you want to use this SQL Server feature.
Just be mindful of a few things. Stuff that we talked about up at the top. Which is that. You have two options.
For the lock owner. You can do session. Which does not. Get rolled back. Then you have transaction. Which does get rolled back.
But you need like. Begin tran commit. Like it needs to be in a transaction. In order to use it. You can’t just use it without. The begin tran commit facilities. So.
That’s my advice. Be careful out there. Error handling is a. Difficult. An often tedious task. In SQL Server.
And remember. That there is a whole bunch of stuff. Both of these links will be in the. In the show notes. But it’s not a show. It’s just a video. Uh. Both of these links will be in the. The YouTube.
Additional information. But just keep in mind. Uh. Just be very careful with try catch. Because even with try catch. There are certain things. That won’t get you to a try catch block. Then you might need to be extra mindful. Of things.
Going on around. Your sp. Get app blocking. That might. Prevent. Your try catch block. Trying to catch anything. Alright.
Cool. Uh. That about does it here. Thank you for watching. Uh. I missed you terribly. Uh. I hope. I hope you.
Enjoyed yourselves. I hope you learned something. Uh. If you. If you found a. Find this video useful at all. Uh. Feel free to. Please give it a thumbs up. I beg of you. Uh. If I.
If I could get on my hands and knees. And you could still see me on camera. I would. I would get on my hands and knees. And beg you to like this video. I would also. Get on my hands and knees. And beg you to subscribe to this channel. Think of the children.
Uh. And. I will see you. In another video. In another time. In another place. So. Cool. Uh. Both. Big Eric.
And. Little Eric. Thank you for watching. Alright. Have a good.
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.