All About SQL Server Stored Procedures: Nested and Autonomous Transactions
Video Summary
In this video, I delve into the intricacies of nested transactions in SQL Server, addressing a request from viewers who wanted to understand how these work and when they might be useful. I explain that while SQL Server behaves differently compared to other database engines like Oracle, it still doesn’t support partial commits within nested transactions as one might expect. Instead, all changes are rolled back if any part of the transaction is rolled back. To illustrate this concept, I create a simple stored procedure and demonstrate how transactions work in practice, showing that even though you can begin and commit multiple transactions, rolling back an outer transaction will undo everything inside it. This video aims to clarify common misconceptions about nested transactions and provide practical insights for SQL Server users.
Full Transcript
Erik Darling here with my second take recording this video. I hit a new professional low where I actually recorded about half of it and then somehow ended up hitting the stop record button and didn’t realize it and just kept talking. And then when I was done, I went to hit stop record and I was like, oh, yeah, that’s fun. So anyway, in this video, I’m going to hit stop record. In this video, we are going to get back into talking about store procedures. Now, I talked about transactions a couple of times, a couple of ways, but one of them in this series, another one was just sort of a funny short video about someone saying while ADAT trancount is greater than zero, roll back, which is just amusing to me. I have gotten some feedback that people would want to know exactly how nested transactions work and how you might be able to actually get a nested transaction to nest because SQL Server behaves a lot differently than a lot of other database engines when it comes to nesting transactions.
I think probably the prime example is Oracle, which does allow for partial commits in nested transactions, where SQL Server, well, you get, y’all don’t get rolled back when you roll back the transaction. We are not going to talk about save points because screw that. And yeah, that’s about it there. Anyway, if you would like to support this channel, if you sign up for a million dollars, I’ll talk about save points. If you barring that, not getting into it. You can use the video, the link in the video description to become a paying member of the channel for as few as $4 a month. You too can support a starving SQL Server consultant.
Then maybe I can stop answering questions about why my face looks skinny lately. If you like the channel, but maybe not enough to put a ring on it, you can like, you can comment, you can subscribe, and you can ask me questions privately that I will answer publicly here on my Office Hours episodes. It is not a podcast. Most vociferously, not a podcast.
If you need help with SQL Server, I am, they don’t call me Eric Reasonable Rates Darling for nothing. My rates are reasonable, and I am, again, editorially presented with, by Beer Gut Magazine, with being the best SQL Server consultant in the world outside of New Zealand. So, I don’t really see what you have to lose there.
If you need SQL Server training, and you don’t want to pay, like, two grand, you can get online for about $150. It’s about 24 hours of content, beginner, intermediate, and expert, maybe even some beyond expert stuff. And you get that for life. You do not have to subscribe to that.
You just sign up, and you’re in. It’s just, that’s it. You’re officially a dues-paying member, and you’re allowed in the clubhouse whenever you’d like. SQL Saturday, New York City 2025, is taking place on May the 10th, with a performance pre-con by Andreas Volter on May the 9th. You are cordially invited to both, and you are also cordially invited to bring me gifts.
You can bring me presents, preferably in the form of low-value currency. So, no hundreds and fifties, just stuff that’s easy to spend at the bodega. So, keep that in mind.
But with that out of the way, let’s talk about nested transactions. Now, SQL Server does have the concept of autonomous transactions. Now, I had that window open already. Good for me.
Now, you might look at the data on this blog post, and you might think, golly and gosh, that’s old. There must be a better way. Guess what? It’s not. You still have to do all this stuff.
Now, this is from back when SQL Server, rather when Microsoft, used to have good SQL Server blogs. If you’ve read a SQL Server blog post lately, you might notice that they’re not good. And they used to, way back when, look at this, back to 2006.
What were you doing in 2006? What was I doing in 2006? Being young, having fun, life worth living and all that stuff.
But Microsoft used to have good SQL Server bloggers. So, I would suggest reading this content before Microsoft makes it disappear. Because one thing Microsoft is famous for is making good stuff disappear and replacing it with crap.
So, please, you know, support your local internet archive or something. Because this stuff ain’t forever anymore. But this post walks through the concept of autonomous transactions, how to make them work by using a loopback linked server.
If you come down here, you’ll see some of this stuff. That is a really aggressive use statement up there. I don’t know if I agree with that.
But you’ll see where they create a linked server called loopback that just connects to your server, right, which is add at server name. And then you’ve got to do some stuff. And then you can get transactions that do partially commit doing that.
You can’t do it really any other way. Unless you want to write absolutely bonkers stuff using table variables and save points and other things. Where, like, it’s so obtuse and edge casey that I don’t want to write that code because I feel like I would get something wrong with it.
And one of, like, three people in the world who would know when that code is wrong would make fun of me. So, we’re not going to do that. So, I will hopefully remember to put that in the video description or get yelled at either way.
But the way that a lot of people think nested transactions work because, like, when you look at nested transactions, it makes sense for them to work like this. But they don’t. And this does work in other databases like Oracle.
I think there’s even a mention of that in the Microsoft post. At least I saw the word Oracle and there weren’t, like, devil horns on it. So, I assume they said something okay about it. But the way that you would expect it to work is, like, begin a transaction called T1.
Do some stuff. Begin a transaction called T2. Do some more stuff. Commit transaction T2 and just have this be, like, out of the picture, right? Like, you saved your changes from this.
You’re done. And then roll back. And then, like, if you wanted to roll back T1, T2 would be left alone. But that doesn’t happen. SQL Server does not do this. SQL Server rolls back everything.
There are no partial commits, right? At least none that stick around in Survivor rollback. So, the way that I want to show you that, like, that concept in SQL Server is I’m going to create a simple table and a simple store procedure. And this store procedure is just going to do, well, I mean, I guess essentially three things.
It’s going to begin a transaction and commit a transaction. And in between that, it’s going to do two inserts into this table just based off whatever values I pass in. And then just to not get a primary key violation, I’m going to add one there.
Outside of the store procedure, I’m going to hopefully remember to begin a transaction called T2, run the store procedure, run a select to show you what values ended up in there, then roll back T2 and show you what values are in the table afterwards. So, if we do this and we run this, we get exactly, we get, like, a query demo proving exactly what I just told you.
Well, while T2 is an open transaction, we can see these two rows have committed to the transaction table. We have both rows that the store procedure inserted in there and committed, right? Like, this thing did begin transaction commit.
We didn’t stop at all. Like, the entire procedure executed. And then later, when we rolled back transaction T2, that undid the T1 transaction inside of the procedure. So, you can’t do that.
Like I said, you can get some of the aspects of an autonomous transaction if you use table variables and write really complex code. I don’t recommend it. You will spend more time dealing with weird issues than you will be happy that you wrote it.
So, maybe don’t do that. Like, save yourself some pain. I mean, you know, like, if you want to get autonomous transaction behavior in SQL Server with as little pain as possible, and I say as little pain as possible because you’re still dealing with linked servers and that’s absolutely no fun, then the instructions in the post that will be, remember, in the video description will walk you through that and how to do that.
So, that is the least painful way, at least, that I’ve come across. I’ve seen various people try to get the autonomous transaction thing working with table variables and save points and all this other stuff, but I’ve never seen a happy person try to do that.
So, I want you to be happy out there. I want you to be happy, bright, sunshiny people who have great weekends and don’t try to do overly ambitious, borderline stupid things with their databases because we all know how that ends up.
Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.
And I will see you in another video, another time, another place, another you, another me. It’ll be beautiful, though. Well, hopefully we’ll remember each other. Anyway, thank you for watching.
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.