Hello everyone and today we’re going to take a look at transactions so as I said in the last tutorial these are really good to use when you’re updating live data in case you make a mistake and also in scenarios where you need to make sure that everything in the code sequence is successful or roll back to the starting.
Point or safe point so I’m just going to use the same.
Code from the last tutorial because I’ve already written it and it’s handy and I’ll just show you how to check if there are any open transactions within your session now you’ll just write select app turn count so if I run that we can see that it’s currently 0 and ideally we want to keep it that way all.
How this works with a rollback first of all and I’ve just nicked one of the deletes that I showed you in the last tutorial and but.
You don’t have to doodlee you can put update or you can put lots of things in between these two sections here and we’re just writing begin Tran what we want it to do and then roll back at the end there so if I run this here without the rollback we can see.
That that has deleted number 2 and if I will select try.
And count we can see here we now have an open transaction so I’ll just roll it back and we can see there that it actually hasn’t deleted anything that so that’s good umm now ideally you’ll run this all in one statement to not leave any transactions open.
Ever because that will create a lock on the table so I’ll probably do a tutorial actually unlocking and blocking.
In the future but just to show you this if I run this half again here now and then in another session if I just try and select from that table we can see here now that this is not returning and that’s because it’s locked here if I roll that back we see there that that will then return and so it’s never good to leave and transactions open.
There at all and you can also name transactions if you want to and like if you’ve got a few.
Of these and like nested and it makes it easier to read or something you can call it like Fred here if you want to there and I’m naturally aware of any benefit or a reason to name in it apart from.
Readability and if you guys do know of anything than me uh please let me know in the.
Comments that would be great and.
But otherwise here you can name it there or not name it it’s up to you so to commit a transaction we’ve just changed the role back there to commit and if we win that we.
Can see here now that our customer ID two has actually been deleted now so you can nest transactions and but you do need to be aware of certain features I guess you’d call it and when doing this because in my opinion they do not behave as.
I would have initially expected and so if we look over here we can see that I’ve got a and begin Tran here we’ve got a delete then we’ve got.
Another begin Tran and another delete there and I’ll show you the Tran count here in between what do you get so that you can see what’s happening now lesson number one here is that a rollback after any number of begin.
Trans will roll back all of them so we’ll see when we run this in.
A moment and that this rollback statement will roll back both of these transactions so by the time we get to here we’re going to get an error because there’s no open transaction and for it to actually roll back so we can see that there and if we go to the results window here and we can see here we.
Wouldn’t begin to an so we’ve got one open.
Transaction we select the data and we’ve got our three records here we open another transaction which create comments are on count two and we then check what we’ve got after deleting customer.
One and we’ve just got three and four and then we do meet customer.
ID 4 so that we’ve got just number three and then after we roll back and select our apt ran count we’ve now got no open.
Transactions there and then this roll back here fails and then we can see from the select here that actually none of our customers have been deleted because we did.
The contrary and lesson number two for this is that a commit will only.
Commit when it’s the outermost commit so the inner ones will actually not do anything if you have a rollback afterwards so we can see this here if I change this in a rollback to a commit and leave the outer as it will back and execute that we can see here that it saves that it’s done it here we’ve got no errors and and we can see that our tram count goes from one to two and then.
Back down to one after this commit so it has reduced our Tran count yet because we have an outer rollback here we can see there that even though the committed run successfully it did not commit the delete and so we’ve still got.
The same number of customers there as we started with so if I do want it old to commit I have to change the outer one to commit and we can see here that we now only have one customer left so I’ll just delete data here and repopulate it and then let’s just have a quick look at.
Save points here so we can set save points during a transaction if we want to be able to.
Roll back to certain points and what you can see in this window here so I’ve got my.
Transaction and with a rollback and then I’ve also got a save point here which are.
Called t1 so we can see here that.
We’ve got our four customers we are deleting customer number one and then setting a save point we can see here that customer ID 1 has now gone and then we delete customer ID number 4 we can see here that customer.
ID 1 and 4 have now gone and then we roll back to transaction 1 so we can see here that this delete of customer 4 is going to have gone which it has but customer 1 has still been deleted because it’s before the save point and but we are actually rolling it all back there so we’ll still have all four customers and if I.
Change this to a commit we can see here that it rolls back the delete of number 4 like we’ve told it to up to here but that does delete customer number 1 so now we just have customers 2 3 and 4 left yes that’s pretty much it I hope that is clear and made sense.