Inconsistent Error Handling By SQL Server
Thanks for watching!
Video Summary
In this video, I delve into the fascinating and often perplexing world of inconsistent error handling in SQL Server. Specifically, I explore how certain errors can cause entire batches to roll back while others only affect individual statements within a transaction—demonstrating this through a series of examples with a table called `transaction_test`. By walking you through these scenarios, I aim to highlight the importance of setting `XACT_ABORT` on for stored procedures and scripts that handle multiple modification queries. This practice ensures more predictable behavior and helps avoid unexpected rollbacks due to conversion errors or other inconsistencies.
Full Transcript
Erik Darling here with Darling Data. And, boy, we’re having a great day here at Darling Data Studios, getting all sorts of important work done. And I’m going to showcase some of that very important work to you today in this video about inconsistent error handling in SQL Server. I don’t mean your inconsistent error handling. I know you. I know you handle errors inconsistently anyway. We’re not talking about that. We’re talking about how SQL Server handles certain errors inconsistently and how that can affect what rolls back in a batch of queries. It’s going to be fun, I promise. You’re not going to lose your mind anyway. It’s going to be a great time. So, before we get into all that, let’s do the normal spiel and routine here because this is, gosh, this is just my favorite part of every video. Getting to sell myself a little bit, one piece at a time.
You can subscribe to a very low-cost membership to support my continuing to record these videos. I’ve got to pay for the electricity somehow, I guess, right? There’s a link to sign up for the memberships down in the video description. You can get one for like $4 a month. In the future, there will be more stuff for subscribers. But right now, I’m dealing with a lot of pre-con material and writing and all this other stuff and I just haven’t had time to build that up yet.
But, if you get in early, you’ll get more stuff because that’s how much I care about you. All of this content is, of course, free. So, if you don’t have the $4 a month or maybe, I don’t know, you just hate me that much and you won’t like spite watch these videos and you just want to like, I don’t know, keep making me waste electricity, You can do other things to show me how much you hate me, like subscribe to the channel so that you can get spite notifications every time I publish a video.
You can give me a spite thumbs down if you want. I mean, go ahead. You’ll be like, you know, the only one. And you can leave me spiteful comments if you also are feeling particularly outrageous on that day. If you need help with SQL Server, and spite or not, you realize that I’m pretty good at it, I can do any one of these things and more.
And as always, my rates are reasonable. So, you should hire me for these things because I’m better than everyone else at them. If you need low cost, very high quality training for SQL Server, I have things from beginner to intermediate to expert. There’s 24 hours of it and you can get it for 75% off for life, which means about $150.
And you can, you know, depending on how long you live, that could be a great deal. If you want to see me live and in person, if you want to see my, my, my human presence, my corporeal form, appear on a stage in front of a laptop and talk about all things having to do with SQL Server performance, you can catch both Kendra Little and I for two days of performance tuning magic and miracles and witchcraft and all sorts of other.
We’re not going to kill a goat, don’t worry. But that’s November 4th and 5th at Pass Data Summit in Seattle. If there’s an event near you that is in need of a pre-con speaker and you think, hey, I bet Erik Darling would like to come here and pre-con this, this, this thing.
Let me know what that is, because there’s a pretty good chance that it’ll show up. Who knows? Right? That’s the worst that could happen.
And with that out of the way, let us continue our party extravaganza with SQL Server and its inconsistencies. All right. So here’s the setup. I have a table called transaction test.
And I’m going to start just by clearing the whole thing out. And I’m going to put in three rows with just default values. There’s a check, there are a couple of things on this table. There’s a clustered primary key on the table, which wonderful.
We should, we should have those for our, you know, transactional tables. Look at this great cluster of index. There’s also a check constraint on this table. It just checks to make sure whatever data goes in there is greater than or equal to get date.
Okay. So that’s, that’s the only thing that we care about in this one. So if we look at what’s in that table now, we will see that there are three rows with this dead giveaway date to let you know that I’m indeed a dork recording on a Saturday. God help.
So, and that’s about it there, right? Nothing too weird, wild, crazy out of the ordinary. Here’s what I’m going to do. I’m going to set no count. Well, actually, I’m going to set no count on and I’m going to set exact abort off, which is a worst practice.
Usually for anything like this, you would want to set exact abort on to avoid exactly this kind of strange oddity, this insane confusion. But I’m going to, I’m turning it off to show you what will happen if you do things in the worst possible way. You want to do things in the best possible way, which is to turn exact abort on if you’re going to do stuff like this.
If you’re not going to do stuff like this, I don’t care what you do. You can, I don’t know, do whatever you want. So we’re going to begin a transaction and we are going to make sure that set exact abort is off or not on, right?
We’re going to go like the is not trusted foreign key, right? Set exact abort is not on. And then we’re going to update this table, which is totally fine.
That’s going to work. It’s in the transaction. We’re going to select from the table and that’s going to look right because we have incremented this to the first when rent is due and why you should subscribe and hire me and buy training because rent is due people. Rent is due.
And now we’re going to delete row number two from the table and that all goes fine. And this is going to look right. And that’s also going to look right now too, right? That row number two is gone.
We have gotten, we have deleted row number two. And now we’re going to do this. And this is going to fail because we are violating that check constraint that we put on the table, right? We can see all of this stuff happened here.
And the important thing that I want you to pay attention to is at the end of all that red text, there’s a little line here that says the statement has been terminated. Right? That statement was terminated because of the check constraint and keep that in mind for later.
So now what the table looks like is exactly what it looked like before. Row number three is, well, the row number two is gone. Row number one is a day ahead, but nothing happened to row number three because that update failed.
All right. We’re going to commit that. And then before we go back up to the top of the script, we’re going to do a little switcheroo here. And we’re going to instead on the second time through, rather than just rather than violate that check constraint, we’re going to have a conversion error.
Right? So we’re going to try to set the date column to something that is absolutely not a date. Right?
Okay. So let’s start over again. Let’s rebuild our table. Right? And actually, before we do that, just to make sure that we don’t have anything open that’s going to cause anything weird, we’re going to make sure that transaction is extra committed. Because, Lord help, if I mess up on the second time through because I didn’t make sure that that was done.
Bush League. You’ll probably see that in someone else’s training, not in mine, though. So I’ve basically recreated the table.
I put all the same stuff in there. And we’re going to follow the script in the same way. We’re going to set no countdown and we’re going to keep XactiBort off. Again, a worst practice.
An absolute worst practice. You don’t do this in your queries. You’ll be mad later if you don’t. And then we’re going to begin a transaction. And then we’re going to just double check to make sure XactiBort definitely not on.
Definitely a bad idea. Definitely not a good idea to have XactiBort off. XactiBort not on.
Right? So we’re going to go through. We’re going to step through. We’re going to update that. And we’re going to say, hey, what’s in there? And that’s correct now, right? So that’s 901. So that got moved forward.
Great, great, great. We’re going to delete row number two. And that’s going to be absolutely fantastic. And we’re going to select now and look at this. And we’re going to say, yep, row number two is gone.
We have passed all these tests. But now we’re going to do this. And what we have here is SQL Server just says, conversion failed when converting date and or time from character string. Now remember, when we violated the check constraint, there was a little piece of blackish, grayish, not red text under there that said, statement has been terminated.
We don’t have that on this one, do we? SQL Server doesn’t say anything additional here. It just says there was an error.
Sorry. Yeah, that’s crazy. But now look at what happens when we run this. All three rows are there and all of them are back to their original data.
So this one never changed because the conversion failed. This one got deleted, but is now back. And this one no longer has the date of 0901.
This one has a date of 831. So the update that pushed that forward a day also got rolled back. Now, to me, before we do that, the reason why this error message is in here is because if I try to run either one of these, this one will say, the commit transaction request has no corresponding begin transaction.
So that means our transaction got killed and completely rolled back. So like, of course, I can’t roll it back either, right? So this one here says the rollback transaction request has no corresponding begin transaction, which is why I have this sort of slash thing in here.
In real life, you would never see the commit slash rollback. It would just say one or the other like I just showed you. But that’s why that’s there in shorthand because that’s what you get from either one of those.
So just to sort of reiterate, all the rows got rolled back to their original state because we hit a conversion error instead of just like some other kind of error. Now, the full list of errors that will cause a batch to completely roll back if you hit them is not really documented anywhere. The closest that I’ve found is in Erland’s article.
So, where was that hiding? There we go. There’s not, it’s kind of hard to find your way around in these things sometimes. So, there are some things that Erland has brought up here.
And one of them is most conversion errors, which is this line right here. I don’t know what most means. I don’t know if there’s a conversion error that would not cause that to happen.
But I do see that most conversion errors would cause that to happen. Which is pretty wild. I also think that this one is pretty wild.
Arithmetic domain error is like squirt on a negative number. Why would you do that? It’s mean. So, this is sort of like the closest I can find to like an actual list.
There might be a longer list out there somewhere, probably in like SQL Server source code, that isn’t available to human beings. But we can get a partial list of that stuff here.
But anyway, the reason why this came up is because I was trying to do a simple demo that showed, you know, exactly kind of what I was trying to show when the check constraint got violated. Except I, you know, without, sort of without thinking much about it, I was just like, oh, I’ll just do the, this is not a date excel.
It’ll be funny. Everyone will laugh. But it turns out, I outfoxed myself on that one. And I thought I was crazy. And I was like, why is this thing doing this?
And of course, I had to revisit Good Sir Erland’s article. And that section was in there. And that at least partially explained my problem. It’s just something that I totally kind of forgot about after, you know, looking at that article a billion times.
So it can happen to anyone. But the important thing here is not that I’m forgetful or not that I did something silly and foolish and now I decided to maybe pass something along to you that will help you down the line in the future if you’re ever doing this stuff.
My, my, the, the really, the, the big takeaway from this video is that if you are going to begin a transaction and you are going to run multiple modification queries in that transaction and you are going to do that in some facility like a script or a store procedure or really anything, like anywhere you’re going to run this sort of thing.
Please set exact abort on. Do not use exact abort off. If you set exact abort off, you’re at this sort of undocumented women fancy of stuff like that conversion error causing the whole thing to roll back, but other errors causing only the one thing to not roll, not roll, not commit, right?
So the other things stayed, right? The other changes were there. Just that one thing failed when it was the check constraint violation.
When it was a conversion error, whoo, it was awful. It was, it rolled the whole batch back and killed the transaction. So different errors will, are handled differently in SQL Server with a lot of inconsistency.
And the only way you can protect yourself from that is to say set exact abort on, on, on, not on, on. There we go. I found the right key. So if you want to protect yourself from foolishness, idiocy, inconsistencies, and all other slings and arrows, barbs and, I don’t know, spears and nunchucks and other things that would, that would really hurt in SQL Server, you should, you should always set exact abort on for stored procedures that you care about.
So, I don’t know. That’s that. And since it’s Saturday, and so are we, I’m going to finish this recording and I’m going to go do something with my family, because, um, I, I, I, I suppose, I suppose they, they might want to see me today.
I suppose you might not be the only people who want to see me today. Or you might, you might just be spitefully watching this and mashing your teeth and clobbering your fists together and… I don’t know.
I really, I don’t, I don’t understand your motivations, to be honest. Anyway, uh, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. I hope that, uh, you will do all the things that give me money.
Uh, and, uh, what’s the other one? Uh, well, yeah, I, I suppose thank you is in order since you did, you did make it to this point. So, uh, if you, if you are still watching, thank you for watching.
And I hope, I hope you’re having a great weekend. Or you had a great weekend when this weekend was. Next weekend, I’m going to be in Dallas. But this video is not going to get published until way after that.
Because I, I, I make a lot of content. So, anyway. Off to the mines. Goodbye.
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.