Nested Transactions in T-SQL

Nested Transactions in T-SQL



we sort of opened up this chapter with our discussion of transactions towards the end of that second video in the chapter I unintentionally gave you a peek into what we would call nested transactions it was just a mistake real easy to do and I want to talk to you about the fallacy of nested transactions this is a an area that a lot of movies to sequel server programming fall prey to search engine information let's just call it that where somebody will answer something in a forum or in a blog and it's actually misinformation yet maybe it's being presented as the right way here's the thing there is not really a concept of nested transactions okay so when we get into the idea of the atomicity aspect of the acid properties you remember the last video we talked about the acid properties here we get into atomicity atomicity specifically says a transaction is an all-or-nothing event you have let's just take for example a couple of transactions here and if you start envisioning how these will work we have transaction one that begins and if we had nesting transactions or if we if sequel server implemented nesting transactions like you might think so transaction 1 starts and is open up here transaction 2 starts transaction 2 commits and then transaction 1 on the same thing here ok Sharan 1 rolls back what's going to happen to Tran – well if you huh if you follow the atomicity that says a transaction is an the visible unit that means that for example you cannot commit transaction to without committing transaction one if we further expanded this and brought in transaction three here and we said starts and transaction three commits what's going to happen in a case like this at the end of this what's going to happen to transactions two and three will this one commit or will this one have rolled back or well I'll leave that because I don't want to cover that just yet we're about to get into that you can't have it both ways you cannot have sub transactions you cannot commit partial transactions that's what this would be doing wouldn't it here's the thing a rollback will roll back all open transactions now there's a big difference between the rollback and a commit a commit will only pseudo commit any nested transactions it will not actually commit those it's simply decrements a tat Tran count you remember we have the Select at Tran camp which will tell us how many open transactions we're currently having like right now I have no open transactions if I say begin Tran and I run this again I will have one so I'll just put a little comments out next to them if I then say begin Tran and remember the nesting has no effect right I mean that's just for readability then we would have two so I can't say begin it's got to be bigger than Tran they begin as just a control of flow so here we have zero now we have one open transaction now we theoretically have two when I issue a commit it will only sudo commit the nested transaction let me come back up here and say committing nested or inner transactions is only applies to the last executed begin tram it doesn't commit all of the transactions write a rollback commit will roll back everything you can have a Tran count of 547 have 547 open transactions and rollback will roll them all that a commit only would decrement it to 546 so you'd have to commit 546 times to actually commit the transaction for example here when transaction one starts at at Tran count equal one here at at Tran can't equal to now we're back to one you see you committed only the most recent begin transaction down here you've started another transaction and that trend count is now increased incremented back up to two and when you commit it its downgraded to one and when you roll it back now you are at zero so this is a little confusing there is no real concept of nesting of transactions in the next video we're going to talk about what save points are and that's going to give you some some closure on I'm assuming some of your questions that you have but just remember the acid property of transactions you cannot partially commit this without committing the entire thing it's not possible okay so a rollback applies to any transaction but commits will just simply decrement the Tran count so notice that we are right here have Tran count open two because we ran two nested begin trans if I say commit Tran and run this again how many are we going to have it's only going to apply to this one so it's simply going to decrement it by one now if I do this again now we've got two transactions open if I roll back the Tran how many will be left zero just like we said so let's talk about save points in the next video you

One thought on “Nested Transactions in T-SQL

Leave a Reply

Your email address will not be published. Required fields are marked *