All About SQL Server Stored Procedures: Transactions
Video Summary
In this video, I dive into the world of transactions within SQL Server stored procedures, exploring their use and limitations. Erik Darling from Darling Data shares his insights, emphasizing that while transactions are crucial for error handling and ensuring data integrity, they come with significant locking ramifications and don’t offer the snapshot isolation many might expect under read committed. He also delves into practical considerations like nested transactions, save points, and the importance of appropriate locking hints to maintain data consistency. Throughout the video, Erik highlights common pitfalls and provides tips on when and how to effectively use transactions in your stored procedures for optimal performance and reliability.
Full Transcript
Erik Darling here with Darling Data. Look how happy we all are. Look how thrilled everyone is to be here. It’s a grand day, isn’t it? Today’s video we are going to continue talking about our good friend, our Lord and Savior, the store procedure. In this video we’re going to talk about the use of transactions in store procedures, what they’re good for, what they’re not good for. And, uh, sort of what people think that a transaction may guarantee that it definitely does not guarantee. So, we have that to look forward to. But of course, before we dive into those titillating, exhilarating topics, uh, let’s talk a little bit about, uh, your money and me. If you would like to support this channel and the wonderful content that I’ve create, uh, I create, uh, you can sign up for a membership down there in the video description. It’s the link that says become a member. Uh, and you can for as little as $4 a month support a starving consultant. Uh, if you enjoy this channel, but you just for some reason cannot scrape together $4 a month, uh, you can like, you can comment, you could subscribe. And if you would like to participate in the, the grandness that is me answering off the screen, you can click on the office hours, uh, you can click on the office hours questions. Uh, this link is also, I’m going to pretend that I’m grabbing it and throwing it down in the video description. This link is available also in the video description. You can click on that and submit your question. And if you’re, if you’re at all curious about if you maybe made some typos or you need to, you want to fix something, you can edit your question after you submit it and make sure and do some like proofreading on it. I would highly suggest, that you all do that because some of the questions that come in are a bit malformed. Um, if you need help beyond, uh, what is available here on YouTube or, uh, what, what you can do on your own, uh, I am, I am available to, to help as a consultant. I am a SQL Server consulting partner and, uh, I can do all sorts of wonderful things for you and your server health checks, performance analysis, hands-on tuning, uh, responding to performance emergencies, uh, and training your developers so that the performance emergencies stop happening. Wonderful set of gifts I have it. And I wish to give to you in exchange for money at a very reasonable rate. Uh, if you would like some training on SQL Server, just to, you know, maybe watch some videos, get a little bit better, a little smarter on your own. You can get all 24 plus hours of mine, uh, for about 150 USD.
Once you apply the coupon code at checkout, it will, it will magically reduce the cost of your training. And that training will be available to you for life. Lucky you. Uh, SQL Saturday, 2025 coming at you live and in person on May the 10th, 2025 in New York city. SQL Saturday. It’s a Saturday, the 10th. Uh, and of course, uh, May the 9th, we have a full day performance tuning pre-con with the lovely, talented, fabulous, and the radius of all to.
So you’ll have a wonderful time with that. And with that out of the way, let’s, uh, let’s talk about these transaction things here. No, we need to go to SSMS for this. Now, uh, transaction and, uh, error handling, uh, usually go together sort of hand in glove. The, um, the ebb and flow of the last few videos have been around, um, error and situation handling, uh, batching, uh, retrying, uh, logic, uh, and, um, stuff like that. Right. So all the stuff that kind of, and debugging all, all stuff that kind of ties in together when you’re trying to make a really robust solution for something in SQL Server.
Um, it doesn’t matter. So like a lot of store procedures are like, not just like running a query, they’re like building a process to do something in SQL Server. So you end up, so what you end up with is like these store procedures that have to like do a lot of stuff that maybe store procedures, uh, don’t always have to do. Right. Like again, like retrying stuff, um, self-healing, uh, debugging, uh, making sure that, you know, if something messes up, you’re able to get your data back into a normal state.
So like all of these things tie in together, uh, when it comes to building a good, uh, process in SQL Server. Uh, the main thing that you need really need to understand about, um, about transactions in SQL Server are the locking ramifications and the things that they don’t guarantee. So like the first thing to understand is that there is absolutely no respect for nested transactions, right?
If you, it doesn’t matter how many transactions you nest, uh, like let’s just say you like, for some reason you nested five transactions. If you ran rollback once, all five would rollback, right? If you, uh, have five nested transactions and you need to commit them, uh, you do have to commit five, like committing only decrements the transaction count by one.
You have to do a one, two, three, four, five commits, but rollback, they all rollback. Right. Uh, so the SQL Server does not do nested transactions in a meaningful way.
Uh, the second thing is that the default isolation level in SQL Server, um, read committed, uh, does not give you a snapshot point in time view of your data. Uh, and it does not, like aside from applying locking hints, like with a select query or like doing something else that locks people out of, uh, whatever data you’re touching, uh, or using something like sp get app lock to lock people out of the code that you’re, that you’re using to serialize access to that. Like you can’t really like with recommitted, you don’t get a snapshot view of the data.
So you have to really think carefully about, uh, like how data flows in your transaction, right? Like one query to the next when there’s, if you’re just doing a transaction with a single query in it, um, there’s not a lot of point because SQL Server already has, um, auto commit transaction. So any like insert update or delete is already happening in the context of a transaction.
Anyway, you really, it’s really having multiple queries and an explicit begin, try and do something that like gives you any ability to like, you know, acidify, uh, a group of queries. Uh, the other thing is that, um, not everything really needs to be in a transaction. Like again, single statements don’t really benefit at all from it.
Uh, the lack of nested transactions has been well documented over the years. And, you know, we do have save points there. It’s not really the same thing, uh, for the sort of remaining stuff in here.
Uh, let’s talk a little bit more about like what happens during a transaction specifically. Um, now blocking is probably the biggest thing that, uh, transactions will, will cause on your server. So like, unless your transactions are really, really fast, uh, you, sort of just like increase your chances of having like locking and deadlocking problems.
You also, um, like you have multiple queries in a transaction. It can make the block process and deadlock XML reports really confusing, right? Like, cause you could, you see like multiple queries in them.
Uh, if there’s like triggers involved or anything else weird, like you just make, you just make it a lot harder to sort of unravel exactly what happened in them. Um, so like be, just be careful with that.
Uh, remember that under read committed, the default crappy isolation level in SQL Server, read queries can block and be blocked by modification queries. And, um, read queries can also deadlock with modification queries.
Um, specific plan shapes and stuff will do that. So like make, make sure that when you’re using a transaction, a it’s rather make sure that when you’re using explicit transactions, make sure that a, that it’s absolutely necessary for you to do it because you know, like either from an error handling or like consistency perspective, um, stuff like that.
Because without it, because if you, if you don’t need it, don’t do it to yourself. Because if any one of those queries, like, let’s say like you have like, like two updates run and they’re really fast, right? And then you have a delete run, but that deletes take what the delete takes like five seconds.
Like some, like, let’s say you don’t have a good supporting index for, for that delete. The locks that those two updates that were really fast took are going to hang on. Until that delete finishes.
So you don’t just have two quick updates that like do something and get out. You have two quick updates that are now beholden to this delete that takes five seconds, right? So you just, you just like increase the surface area of your query in duration.
And the bigger that surface area gets, the worse, the worse your problems get. Because like, you know, like, like, as this query overlaps with other queries trying to do stuff, like, like all those surface areas line up in a way that either like exacerbate locking problems or lead to deadlocks just because like you had some updates over here and delete five seconds here.
And then something else wants to do something over here. And like, you just end up clashing with like the updates or something. Like those locks hold on and start interacting with other queries.
And you end up with a, like a problem, not just for like one of the updates, like all three queries in that transaction now have a problem. Because when they deadlock, it’ll roll back. So just be really careful with that.
Make sure that you use appropriate locking hints to make sure that you preserve a preserved data integrity. If you’re like reading data to modify it later and make sure that you are handling errors, rollbacks. I mean, I guess, and commits appropriately here and make sure that you have tuned everything to happen in that user transaction as much as possible.
Because everything you do between like begin transaction and commit or like whatever rollback happens, those locks have to hold on until that thing makes a decision, right? Like you can’t like just say like when they’re trying to be like, and release the locks now.
Like it just doesn’t happen that way. So just be really careful with all that stuff. Now, you also have to be really careful to understand how isolation levels work in the context of transactions.
Not just read committed. I mean, I know most of you out there are just so desperate to get away from read committed that you’re using no lock or read uncommitted. But if you’re using optimistic isolation levels, whether it’s read committed, snapshot isolation, or snapshot isolation, both of those behave somewhat differently in the context of transactions too.
I cover that in my video series, Everything You Know About Isolation Levels Is Wrong. There’s a section on read committed snapshot. There’s a video about read committed snapshot isolation versus snapshot isolation, where if you have questions about that, you should go watch that one because there’s a lot of good information in there.
But like let’s say you’re doing something like this, right? Where, you know, granted, not my favorite thing in the world, declaring a local variable and setting that local variable equal to a value here.
In this query, we’re looking for the ID of a user in the users table who has, like, reputation has not gone above one but has a post, right? Let’s say they have a post.
They’ve asked a question, but that question has a score of zero, right? No one voted on it. Poor thing. You poor dear. And we want to, like, we want to give you a mercy vote. Give you a pity vote.
You might, you know, of course, you might bail out if, like, no one meets that qualification. So, like, let’s say user to promote is still null. You might just say, no, we’re out of here, right? We’re done.
Return. Stop doing work. But, you know, then you go and do this, and you go and find, like, a post to update based on who that user was, and you update, and you give that, you bump that score up to plus one, right?
Just add a one to that score. Under read committed, unless you take out locking hints, like, here and here and here, you could end up with some weird stuff happening.
Leaving aside the obvious stuff like local variables, which no self-respecting performance tune or whatever do, right? Like this thing in here. You get whoever that first user is, you only get, like, their most recently committed data first, right?
Like, and, like, again, you’re not locking that data so that it can’t change between when you get that data and when you do something with that data. And also, you don’t do anything to ensure that, like, nothing changes between when you get that data and, like, when you do something with the data, right?
So, like, data can change around you up here because there’s no locking happening in the select query, right? SQL Server does not have a select for update. You have to add in locking hints in the from clause, you know, like, updlock serializable, serializable, whatever you need to happen to prevent changes to that data.
And that leaves you wide open to, like, data changing around you, right? Like, data can just change under concurrency. With that, what you could end up with, you can end up with something as wild as, like, that user could delete their profile, right?
They could say, I hate this site. No one ever upvotes my questions or answers. It’s terrible. Everyone just makes fun of me and downvotes me. They could just get sick of stack over from me, like, I’m out of here. Someone could go and, like, go vote on their question.
After you find, they’re like, ah, like, someone comes along and they’re like, wow, this is a great question. Upvote. Now, all of a sudden, that question has an upvote and it no longer would qualify for what our query is doing.
They could also go and delete their question, right? They could say, ah, you know what? I understand why no one upvoted this. Now that I read it, I realize I was being stupid. I opt myself.
I was being a foolish. I was being a silly little ninny. Ah, delete this question, right? So, like, nothing protects that data after you’ve read it and before you update it and do anything with it.
So, like, the transaction doesn’t do that unless you take out specific locking hints. Read committed for, like, the billionth time is not a snapshot consistent view of your data during a transaction.
If you were using snapshot isolation or read committed snapshot, or rather, if you’re using snapshot isolation, it would be. If you were using read committed snapshot isolation, it would be a lot closer to it. But, like, read committed, the default pessimistic isolation level, doesn’t give you that.
All read committed guarantees is that the moment you read data, it was, that’s the way it was committed in the table. And it’s very, very flimsy if you think about it or if you watch my series on isolation levels where I explain all of this stuff in great detail.
So, if you want those changes, oh, sorry, if you want to avoid those changes, you would need to add in the updlock and serializable hints to your select query and, like, the exist subquery and, like, basically any select before you do, like, modifications.
Even if you were to do all that work in a single query rather than two queries, you can run into the exact same issues without locking hints to protect the data that you’re reading, right? It’s, like, read committed locks do not hold on, right?
They just, they get, they happen very quickly and then they let go, right? So, just be very careful with that stuff. You know, so, like, everything in SQL Server, like, whether you specify a cursor or not, kind of under the covers is cursor-driven anyway.
But once those read cursors find the rows that they care about, anything goes, like, until, like, exclusive locks start being taken. Like, anything else can come along and be like, well, like, I got it now, sucker, right? So, some more pseudocode.
This is a pattern that I’ve noticed in more than a few clients now is they do something like this, right, where we’re going to do an insert into a table, right? We’re going to find some user stuff and we’re going to cross-apply some stuff.
And then what we want to do is avoid redoing data. Like, we don’t want to reinsert any rows, right? So, like, this is sort of like a broken up merge, right?
Sort of, like, don’t get me started on merge for various reasons. But let’s just say that, like, if you had a merge that was doing an insert slash update action or any merge statement with multiple actions, you still need, you need serializable hints on stuff.
But, like, let’s just say you’re, like, you’re doing this where you’re going to insert any new data, right? Any posts that don’t exist, you’re going to insert those. And then, I don’t know how that parentheses ended up there, but whatever. And then for any new data, you’re going to just do an update, right?
All sorts of weird stuff can happen in here because you are doing reads from multiple tables where, you know, again, under read committed, even in a transaction, data can change all around you.
And you can run into all sorts of strange stuff, especially, you know, if you’re not locking data out here, right? You’re not preventing changes to this stuff. You can end up with weird race condition stuff in queries like this.
This update is fairly safe, except we are just doing a read from here, right? So there’s a lot for you to think about. The idea of the code is to insert any new users into the high question score table.
And, you know, let’s just say that there’s another query that might come along and update the high score for existing users. The problem becomes fairly obvious, right?
Especially under concurrency. There’s absolutely no reason to roll back new users inserted into the table just because the update query fails for some reason. Let’s say that the badges table was recently modified to accommodate some new longer badge name, but the high question scores table wasn’t.
We got a truncation error. That thing fails. Something weird happens, right? But that truncation error shouldn’t invalidate the new users that got inserted. Likewise, let’s just say that if our invisible score updating query is not going to be a query produced a bigger integer than, like, you know, like, that’s an integer column, let’s say some, like, really awesome question came along and all of a sudden you had a, like, query that was, like, or rather a question that had, like, 2 billion points, like, 2.2 billion points that would be bigger than an integer.
You’re now in big in territory. You would also get an error there. So you just have to be very careful with all this stuff. Like, transactions really only encapsulate queries where correctness, like, data correctness would be affected by one of them failing.
Do I have the right affected in there? Let’s just do that. Just so we don’t have to figure anything out on the fly.
Just something would happen to it. You have to be very careful when you’re using transactions to, A, like, you know, make sure that you need them. B, make sure that they are as fast as possible. C, make sure that you’re doing the right thing as far as rolling back, error handling, all that stuff.
And also make sure that, like, the queries that you’re grouping together, like, go together to form, like, the Voltron of result correctness for the whole set of them. If you don’t need queries to ride or die together, they don’t need to be in a transaction together.
So, be very careful with transactions. Like I said, you can end up with just exacerbating any locking and deadlocking problems that you have in your system.
And they can also make the blocking and deadlock XML reports very, very confusing to look at and read through and figure out what happened. Various other things like triggers and barring keys can make life even more difficult, but that, like, that stuff is, like, also, like, intratransactional stuff where you, like, you know, triggers are, like, an implicit transaction, barring keys.
Those, like, and, like, especially with cascading actions, those promote to serializable. You just end up with lots of weird problems. So, be very careful when you’re using explicit transactions because if you start just, like, mindlessly wrapping, like, entire store procedures or, like, queries that just don’t need to be in a transaction together in them, you can end up with some really painful problems.
So, thank you for watching. I hope you enjoyed yourselves. I hope you learned something. And I will see you in the next video, which will be just as pertinent to store procedures as the rest of these have been.
So, I look forward to speaking more about that. Anyway, I’m out of here. 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.