SQL Server Programming Part 14 - Transactions

welcome to this YSL tutorial in this video we're going to teach you all about using transactions in Microsoft sequel server we'll start the video with a quick look at the basic syntax of transactions so we'll explain what a transaction is how you can begin one and then either commit it or roll it back and we'll also show you how you can create named transactions once we've covered the basics we'll move on and show you how you can actually make use of transactions so I'll teach you how you can test for whether you want to commit or rollback a transaction using gift statements show you how you can use transactions with error handling code talked about how you can automatically make transactions rollback how that future works how to create nested transactions use save points and finally the most complicated part how you use transactions within the stored procedures there's quite a lot to do let's get started in sequel server a transaction is like an event which occurs whenever something changes the data in your database so for example a transaction occurs when you add new records to a table when you update existing records in the table when you delete records from the table each time one of these things happens sequel server records that event in something called a transaction log now normally when you're modifying data in a database you don't get the opportunity to control when the transactions happen to all intents and purposes when you execute a query such as this one the transactions take place immediately and the changes are saved in the underlying database so if I execute this script I'll see three success messages effectively I've inserted updated and then subsequently deleted the same film and all those changes have taken place immediately if I wanted to undo the changes I can't so this video is all about how you can control that process a little bit more carefully by explicitly beginning transactions and then choosing when those changes are committed or even giving me the chance to undo them in order to control when your transactions happen you need to tell sequel server when you begin a transaction before you attempt to modify any data so the way that you tells sequel server to begin a transaction is very straightforward you write the literal statement begin and transaction and as straightforward as that in fact if you're feeling a bit lazy you don't want have to type out the full word transaction you can even get away with just typing in begin tram now if you explicitly begin your transactions sequel server no longer automatically commits them to the database so what you also have to do is explicitly commit your transaction and you can do that in several different ways after you've made some modifications to your data what you would need to do is I say either commit or you could say commit Tran or you'd say commit transaction so any of those three methods would work so just to test out this example if I execute my scripts now I'll find that I've added a single row to my database of course that's a fairly simplistic example the real power in transactions comes from the fact that you can perform any number of actions make as many changes to data as you like between beginning a single transaction and finally committing it so there's no limit really to the number of things you can do between begin and commit transaction now as well as choosing to commit a change to the database when you've begun a transaction you could also choose to undo those changes so what we have here we've begun a transaction and inserted a record into a table and a simple select statement test of that record is in the table what we're then going to do is undo our changes and we do that with a rollback statement so you can say rollback you can say overall back and Tran or again use a rollback on transaction if I then try to perform the same select statement once my changes have been undone what I'm gonna see when I execute this code is but the first selects they meant I see the record that I've added and for the second select statement I see that it's been removed from the database again so we've seen how to begin a transaction and then either commit it or roll it back it's also worthwhile knowingly and give names to your transactions and you can meet that very simply by adding a label after the begin transaction statement so I'm going to call this one add Ironman 3 because that's what the transaction does I can then copy this name and I can attach that label as well to the commit statement or if it was the rollback statement I could have it to the rollback statement as well so if I execute this script now it is exactly the same job as though it didn't have a name it's just so they McCann make things a little bit more clear it's not such a great advantage in such a small simple example like this but in longer scripts where you have many transactions that begin and end having names for them can help you to spots where each one begins in all of our examples so far we've either always committed our transaction or we've always rolled it back now in the real world is much more likely that you will want to determine whether or not to commit or rollback transaction based on some form of condition so for this simple example we're going to add Iron Man 3 to our table again and we're then gonna check how many versions of Iron Man 3 exists in the table if that's more than one then we're going to roll back our transaction otherwise we're going to commit it now of course in the real world it would be much more sensible to check whether Iron Man 3 exists in the table thirst before we attempt to add it but this is just a quick and easy way to demonstrate how you can conditionally commit or rollback transaction so we've declared a variable which we're going to use to hold a number of Iron Men films I guess in the database and we've begun at at our transaction to add Iron Man 3 and I move you inserted that record into the table what we'd like to do then is count how many Iron Man 3 films that are in the database so we would do that by selecting I'm gonna use our variable here at Iron Men equals count star from TBL film we're right in a moment where film name equals Iron Man 3 so at that point with us store the number of Iron Man 3 films in the database the next job is to test what the number of Iron Man 3 films is we can do that using an if statement so I'm gonna check if that's Iron Man is greater than 1 what I'd like to do there's a couple of things actually I'm gonna have a beginning an end block in here because there are a couple of actions I'd like to perform first of all importantly if I find Iron Man already existed in the database I want to rollback my transaction so effectively undo adding Iron Man 3 to the database I'm on 3 there we go and also it's worthwhile telling the user or the person running this script though this has happened as well so I'm going to add a print statement so I'm going to say print Iron Man 3 with a capital M there we go I'm on three was already there if the count of Iron Man three films is not more than one then what I can do is write an else clause or add an else clause my if statement when another begin and end block and here I can check I can sorry I can commit my transaction commit Tran add Iron Man 3 and then print another message which says Iron Man 3 added to the database sirs the full script if I execute this code now once I should find that Iron Man 3 was added to the database I previously deleted all the other instances that we there that we'd added and if I execute the code one more time I'll find that Iron Man 3 was already there so I only ever get one instance of Iron Man 3 in my database no matter how many times I execute that script it's that's quick look at how you can conditionally commit or rollback a transaction rather than using an if statement to check whether or not you should commit or rollback a transaction in the real world you're probably more likely to do the same thing using error handling so in this example we're trying to insert Iron Man 3 into our film table again and then we're trying to update the film director ID of that film to be equal to a string of text that's going to fail because the film director ID field can only contain an integer so if I select and execute that code I'll end up with 1 action having succeeded inserting the record but the second one having failed what that means is I'm left with a half completed record lying around in my film table so as I'm angry with no film directors ID what might be a more sensible solution would be if either of these two actions fail roll back to the beginning of the entire procedure so that I'm not left with these half completed records before we do this it's worth noting that you don't have to write our handling code to make this system work there's a default also rollback feature in sequel server which works like this if I begin a transaction so begins ran at a.m. and then after I try to update my record commit Tran add I am if I attempt to run this code I'll see the same sort of our messages I saw earlier on so I have apparently one action succeeding one row effects it so I've inserted a record and then the second one having failed when I try to insert a string of text into an integer field however if I attempt to select Ironman 3 from this table so I should find there are two instances really if I've successfully inserted them both I'll still fine I'm only left with the one that I created in the original running of this so because one of my two actions failed between beginning and committing my transaction sequel server by default automatically rolls back the entire transaction the disadvantage of course with having this basic system is that all you end up with at the horrible standard built-in error messages with error handling we can control this process a bit more and present the user with much more useful information so that's what we're going to do so to add some error-handling to this procedure let's make a bit more space first of all what I need to do is tell sequel server that I'd like to try to perform some actions and I tell sequel server that's by adding a begin try statement after I've tried to do some things I don't need to end my try block oops button I'm suppose to be on a new line let's try that again there we go so where there's my end try and let me just in dem these lines as well to make it a little bit easier to read so I'm going to try to do all of these things between begin try and and try if any of those fail I need a catch block to tell sequel server what to do so your catch block begins immediately after your try block ends so I could say begin catch my catch block ends of course with end catch and in between these two lines I simply write all of the instructions that I write to be carried out should any of these fail so let's do a couple of things let's roll back Tran add IM and then let's print a message to the user as well print adding ironman failed check data types this would be a slightly more sensible and more useful error message in the real world of course this is purely for demonstration purposes so if i select all this code from beginning try to end catch and execute in now what I will say is hopefully more useful messages appearing so adding ironman failed check data types if i were to modify the film director ID let's make it a number let's make it an integer I can't remember what number Shane Black is let's use the number four instead which happens to be the ID for Steven Spielberg but never mind it will do for demonstration purposes and if I execute all this code one more time I'll find this time that my code has worked I hope and if I select my records from the film table we're Iron Man three exists I ought to find the original one that I left in there earlier on and a new one that has successfully been added with a new director ID number I've just realized that I've actually don't accidentally updated the IDs of all the Iron Man 3 so all the amount three films will be directed by Steven Spielberg now but hopefully that don't race that the error handling section if I deleted all of the Iron Man's from the film table in the first place and then run this code again first of all were they working with an ID number that one creates a sensible record in my film table however if I tried to set the director ID to be a stream of text and I tried to run this one again this time I get a nice sensible error message to tell me exactly what had happened so I'm left with only my original working version of that record so far all the examples we've looked at have involved just a single transaction but it is possible to have more than one transaction opened at the same time using the technique all nested transactions so it's basically how they work each time you begin a transaction the number of transactions that are open increases by one and you can actually find out how many transactions you have got open by printing the value of a global variable called etat Tran count so if I were to execute this simple script which doesn't even in fact do anything all I'm gonna see is that I get a transaction count of one what I can do inside this transaction is actually begin another one so if I begin Tran I'm gonna be an inventive name Tran - and then print @ @ Tran count and commit Tran Tran - when I execute this code now I'm gonna find that I get transaction cam one and then to the pre transaction that I've opened after I've committed a transaction that decrements the other transaction counts by one so I can print run count again in between committing transaction two in committing transaction one and execute this I'll get one to one so you can clearly see that internally sequel server keeps a record of how many transactions have been begun so every time you begin a transaction it increases the transaction can by one and each time you commit a transaction it decreases it by one what happens when you start to rollback transactions now when you're doing this with nested transactions you have to be very very careful about what you do first of all if I were to try to rollback and nested transactions of an inner transaction with a name I'll find that I'm not allowed to do so when you roll back in a transaction you're not allowed to specify the transaction by name I tried to ask you that I get an error message so let me remove the name from Maya for my inner transaction now at this point I'm going to try to execute the entire script in one go and what I'm gonna see a couple of slightly odd things first of all obviously there's an error message there and I'll explain that in just one moment but can you first of all notice the transaction count when I begin a transaction it increment increment it by 1 and then begin another transaction I've got two transaction counts whenever I roll back a transaction this isn't related to just the inner nested transaction I'm talking paths when you roll back everything is rolled back so when I come to print the transaction count here I've got 0 transactions open and hopefully that explains why the commit transaction line here number 15 has failed because if all my transactions have already been rolled back then I can't commit a transaction because there are none open so I wanted to be very very careful with without rolling back nested transactions the other problem with nested transactions and rollback is a little less easy to spot if I can quickly show you what what the issue is if I try to reverse what I'm doing here by committing the inner transaction and then rolling back the outer transaction when execute the code everything appears to work normally so I've started one transaction here my transaction count is 1 and then I've got two transactions open here transaction counters to committed the first one which decrements my transaction counts on one again and then I've rolled back the outer transaction now the problem with this is although it looks like what I've done is saved my actions in the inner transaction it's by committing yeah I've automatically updated and saved whatever I've done when I gets a rollback rollback doesn't just our own do the outer transaction it rolls back absolutely everything that happened since the start of the outer transaction so although it looks so I've saved my work here that those changes have been undone by this rollback so we've seen that when you roll back a transaction it rolls back every open transaction at the same time but you can give yourself the opportunity to rollback just part of a transaction you think something called a safe point so something to do here rather than trying to begin another transaction within the first one I'm going to save my Tran at this point I'm gonna give it a safe name I'm gonna save point something as short and simple when you save a transaction it doesn't actually begin a new one it just saves all the things you've done up to that point it's like a it's like a label that you can get back to by using a rollback statement so if I roll back my Tran and I give it the name a save point what this will do is revert back to the changes that were previously saved at this stage so when I execute this code you'll see that then the number of transactions I have opened at any one time is always just one so I've managed to rollback whatever I've done between this point in this point and then committed the overall transaction right at the end now you're most likely to see safe points to used and in fact this is where Microsoft intend for them to be used when you're using transactions with installed procedures so what we have here is a basic script which is trying to insert it again I am man 3 into our film table and we have an overall transaction that we begin at the start and then commit at the end so we insert the the film recordings of the film table and then we try to find the directors ID for their for the film by calling a stored procedure which returns an ID we then update the film table to add the recovered directors ID into there and then commits the final transaction now in the stored procedure itself which I'll I'll show you the code that was used to create it didn't quite a bit more work so a simple stored procedure which accepts a single varchar' parameter so we passing the director name shame black and then within the store procedure itself we have an ID variable which cannot learn integer before we do anything within the store procedure we set a safe point called add director for the overall transaction so when we then insert a record into the director table so we're gonna insert shame black regardless and then we try to find out whether or not that director exists more than once so this if statement checks if that doubt is named returns a number of records it is more than 1 then we know that the director already existed so we print a statement to the user and then roll back the transaction to the save point so rolling back to a safe point remember doesn't roll back every open transaction it only rolls back to this specific position so that will effectively remove shame black from the table if he already existed in it I know isn't the most sensible way to do it but again this is just purely for demonstration purposes finally what happens is that our store procedure returns the ID number from the darts table for the director we've just added and then returns that to the calling procedure so we should now be able to test out whether all of our code works is intended I've got a bit of test code here let's make sure first of all that neither Ironman 3 nor Shane black exists in their relevant tables so we can delete their any records if they all existed and they didn't he'll go back to the main procedure them and I execute this code hopefully the first time it works I'll get both Iron Man 3 and shame black added to my appropriate database tables so I go back to my test code now and I tried to select the relevant records I've got a shame black cat ID number 131 and I am man 3 with film director ID number 131 now that shame black already exists in the database what I should find however is if I try to execute this code again so the main calling procedure that although I've added Iron Man 3 again I found that the eyewriter already existed so that must mean that he was rolled back so that should mean that if I go back to my test code although I'm going to have more than one Iron Man 3 I should still only have one single shame black and he should still be the number this 131 if I execute this code to select it that's exactly what I have so there we go that's a bit of how safe points were intended to be used is when you're creating procedures which call other procedures and you want to potentially rollback just parts of an overall transaction if you've enjoyed this training video you can find many more online training resources at www.weiu.net

Channel: WiseOwlTutorials

Views: 71,798

Rating: 4.9406309 out of 5

Keywords: sql, transactions, commit, rollback, nested, wise owl

Id: is03uRYFgqc

Length: 23min 7sec (1387 seconds)

Published: Mon May 20 2013