Transactions in sql server Part 57

welcome to presume technologies I am venket this is part 57 of signal server in this session we'll learn about transactions in sequel server before continuing with the session I strongly recommend to watch part 56 of this video series so what's a transaction a transaction is a group of commands that change the data stored in a database a transaction is treated as a single unit a transaction ensures that either all of the commands succeed or none of them if one of the commands in the transaction fails all of the commands fail and any data that was modified in the database is then ruled back in this way transactions maintain the integrity of data in a database let's look at an example I have this table TBL product which has got information about laptops and desktops at the moment the quantity of laptops available is 100 now let's say I want to change the quantity to 200 and to do that you know we are issuing this update statement update TBL product set quantity available is equal to 200 where product ID is equal to 1 now when I execute the statement you know you can see that one row effected when we select the data back you should see the quantity available is 200 now this update statement is not part of any transaction so as soon as you execute this update statement the change is made permanent to the database immediately on the other hand you can make this update statement belong to a transaction by including begin transaction so now let's try to change the quantity to 300 so now this update statement is part of the transaction when we execute these two statements together look at this it says one row affected now from within the same vendor within the same connection when I select the data back from the table look at this it shows 300 but then let's try to make another connection to the sequel server so this is a different connection now when I try to select the data from the same table look at what's going to happen it says executing query and then if you look at the product table it has got only two rows to select that rows from that table it should take less than a second but if you look at this it's almost more than ten seconds now it is still executing that's because you have initiated this transaction but you did not either commit this or roll this back because of which other users will not be able to see that uncommitted data by default and that's because sequel servers default isolation level is read committed meaning read only committed data but here if you look at this there is some uncommitted data in this TBL product table that's why I'm not able to see the data from that table so let's cancel that and if I want to see the data what I can do is basically set the transaction isolation level to read uncommitted data okay so let's do that set transaction isolation level to read uncommitted so let's execute this command completed now if I execute the select star from TBL product I am able to see the read you know uncommitted data now when I roll this transaction back execute this command completed successfully now look at this when I execute select star from TBL product before we execute select star from TBL product this is the previous data this is that uncommitted 300 now when I execute this query look at this the change is undone so now whenever you're updating sir delete statements whenever these are part of a transaction now the change is not made immediately permanent to the database until you commit that transaction so if you want to make the change permanent you come with the transaction if you want to undo the change then you roll back the transaction so for example let's say at the minute you know the quantity available for laptops is 200 let's try to change it to 300 as part of this transaction and then to make that change permanent I basically have to say commit transaction at this point the change is made permanent to the database now transaction processing follows very simple steps you begin a transaction using begin transaction and then you process the database commands you might have one or more sequel server commands you know inserts updates and deletes and then you check for any errors if there are errors you roll the transaction back otherwise you commit the transaction let's look at an example I have two tables here TBL physical address and TBL mailing address now in reality let's say we are shopping online most of the e-commerce sites have mailing address and you know billing address and most of us will have the same mailing and billing address along the same lines here if we look at this we have physical and a mailing address as look at this employee 1 0 1 he has the same mailing and physical address now let if you look at the London you know the city spelling London is misspelled here and obviously I want to correct this to correct this I have a stored procedure here and if you look at the stored procedure it's a pretty simple one all we are doing here is we are updating mailing address table and physical address table and we are setting the city to correct spelling okay and if you look at these to update statements these are wrapped between begin transaction and commit transaction blocks and this block is further wrapped between begin try and end try and then following the try block is the catch block where we are ruling the transaction back we have spoken about handling errors using try catch blocks in the previous session of this video series if you are new to error handling please check that video first okay so obviously when we execute the stored procedure what's going to happen it's going to execute the first update statement and then it's going to execute the second update statement and if there are no errors the transaction will be committed if there are errors whenever you are executing this first or second update statement what's going to happen the control will immediately be transferred to the cache block and within the cache block we are rolling the transaction back okay now since these are two simple update statements which you know both of these statements should succeed and correct the city spelling to London let's look at this so I have these two tables physical address and mailing address so if you look at this at the minute the city spelling is incorrect and we have the same stored procedure that we have seen on the slide which updates the mailing address and the physical address tables and then in addition we also have print transaction committed just to print a message you know to prove that it committed the transaction so let's create this stored procedure now and now before we exit execute let's quickly check the data and TBL physical address and mailing address you know the spelling is incorrect so let's execute the stored procedure so one row affected one row affected so the first one row affected is when we updated the mailing address the second one is when we updated physical address and then we have that transaction committed message printed by this print statement which means the transaction has been committed so now when we check the physical and mailing address you should see the spelling is now corrected now let's do let's try to introduce an error intentionally if you look at the second update it's the same stored procedure except that the second update statement has gone you know here we are setting the city to London London okay basically what we are trying to do here is if you look at the column length the city column length in TBL physical address table so if you look at the city column length it's ten characters but now I am trying to update the physical address to London London and London Space London is definitely more than ten characters so obviously when this update statement is being executed we will get an error now let's change the mailing address to London one now when we change the stored procedure let's change this so alter procedure now when we execute the stored procedure what's going to happen it comes here it updates the mailing address okay to London one which is fine and then when it comes to updating the physical address what's going to happen okay the city column let this stand but but the value is more than ten so this is going to throw an exception so when an exception occurs what's going to happen the control immediately goes to the catch block and the catch block is ruling back the transaction so when we roll the transaction back what's going to happen whatever change that was made by this first update statement is also going to be undone so this update statement changed it to London one but then it will be rolled back so it will remind London and then it will finally print transaction rolled back so let's see that in action let's execute this you see that one row affected then it says zero zero rows affected so it first updated city to London one and then when it was trying to do this we got that zero rows affected so because there was an error when we were trying to execute this immediately the control will go to the cache block rollback transaction and you should see print you know transaction rollback as a result of which if you now select the data from these two tables you would see as if we didn't update anything because the transaction is rolled back so both of these statements are treated as a single unit because the transaction ensures either both of them succeed or none of them and in this way transactions maintain the integrity of the database now we we discussed that a transaction is a group of database commands now what database commands are part of a transaction is determined by an asset test meaning the commands that belong in a transaction have to be atomic consistent isolated and durable what we mean by this acid test we will look at in the next video session on this slide you can find resources for asp.net c-sharp and sequel server interview questions that's it for today thank you for listening have a great day

Channel: kudvenkat

Views: 248,389

Rating: 4.8936172 out of 5

Keywords: transactions in sql server, sql server 2005, sql server 2008, sql rollback transaction on error, sql transaction commit rollback example, sql update rollback commit, sql rollback example, sql commit rollback

Id: shkt9Z5Gz-U

Length: 11min 18sec (678 seconds)

Published: Sun Oct 07 2012