Transactions in sql server and ACID Tests Part 58

so welcome to presume technologies I am Venky this is part 58 of signal server in this session we'll learn about transaction as a test before continuing with the session I strongly recommend to watch part 57 of this video series we know that a transaction is a group of database commands that are treated as a single unit a successful transaction must pass the acid test that is a transaction must be atomic consistent isolated and durable let's see what we mean by each one of these a transaction must be atomic meaning all statements in the transaction either completed successfully or they were all rolled back the tasks that the set of operation represents a say they're accomplished or not but in any case not left half done let's see what we mean by this with an example I have two tables here TBL product and TBL product sales TBL product is like an inventory table which contains the information about the products and the quantity available now whenever we sell a product an entry should also be made into TBL product sales table so when we sell a product two things should happen first we should check the quantity available let's say for example I am selling 10 laptops the first thing that I have to do here is to check ok what's the quantity available for laptops 90 and then from that quantity available I will have to deduct the 10 laptops that I'm selling so we have to update this quantity available - 90 - 10 80 and then we also have to make an entry into this TBL product sales table product ID is 1 and the lab and and the quantity I am selling is 10 ok so basically when we sell you know a product two things happening update TBL product table insert a row into TBL product sales table so that's what this transaction is doing here the first statement updates TBL product table and the next statement inserts a row into TBL product sales table and these two statements are wrapped inside a transaction so the Tomasz city of a transaction states that both of these statements should be treated as one unit either both of them succeed if one of them fails the data that's modified by the other statement should be you know undone and rolled back so that the database is in a state that is similar to you know before we have begin the transaction so either both of them should succeed or none of them okay that's what is the atomic city of a transaction the transaction should also be consistent which means all data attached by the transaction is left in a logically consistent State for example if stock available numbers are decremented from TBL product table then there has to be an Associated entry in TBL product sales table the inventory can't just disappear so for example if I am selling 10 laptops the first update statements reduces that quantity from laptops quantity available here and then let's say when we are about to execute the insert statement into t-bill products sales table the Power has gone or something has happened in that circumstances you know a transaction should undo the first statement and put it back to 90 otherwise we cannot account for those 10 laptops so that's what consistency says all data touched by the transaction is left in a logically consistent State the transaction should also be isolated what we mean by this the transaction must affect data without interfering with other concurrent transactions or being interfered with by them okay so if there are two transactions transaction a and transaction be you know transaction a shouldn't interfere with transaction B and similarly transaction B shouldn't interfere with transaction a both of them you know should work as a single unit of work and they shouldn't interfere with each other okay let's see how transaction achieves isolation I have this table here TBL product now let's say you know this is one of the transactions where I am updating the stable TBL product set let's say quantity available is equal to 350 where product ID is equal to 1 and now let's make this update statement part of the transaction so begin transaction so now when I execute this look at this a transaction is now being processed on TBL product table we haven't committed this transaction or rolled it back so this transaction is still being processed now let's say there is another person you know who connected to the sequel server and he is also trying to update the same table so update TBL so he issued let's say another transaction or let's just say he's trying to update the quantity now before updating he wants to select the data and see so he is issuing a select statement so now when I say select star from TBL product when I execute this look at this it says executing query but it it will never get access to that table why because there is a transaction that is you know still being processed it's not either it's not completed yet that's why other users by default will not be able to see you know the data that is being touched by the other transactions so here there is one transaction which is processing you know a record from this table if you look at this there are two records in this table currently we are updating the first record which means this transaction is still being processed we haven't committed a roll that back so now when I say select star from TBL product it it says query executing query but it's not showing it's just waiting for the transaction to complete on the other hand let's cancel that and when I say select star from TBL product where product ID is equal to two I am selecting a row where the product ID is equal to two look at this the moment I execute that I mediately get that draw but then when I try to get the row with ID is equal to one that is being updated by the other transaction in the other connection window look at this it says executing query so why am I not able to see that because that true is being locked by the database because another transaction is currently being processed so this transaction is not interfering with this transaction here so the transactions must be isolated so how does the transactions achieve this isolation you know basically using locking mechanism we will talk about transaction isolation levels in in the next session okay so this isolation this actually prevents transactions from making changes to data based on uncommitted information for examples changes you know to a record that are subsequently rolled back most databases use locking to maintain transaction isolation okay so here I haven't committed any data add you know this transaction is still being processed I haven't committed any data yet that's why this user is not able to access that record now if this user were able to you know access that record that product ID is equal to one then he might see uncommitted data because this transaction is still being processed I didn't commit the data I didn't make the changes permanent to the database so this user might see uncommitted data and he might make his decisions based on that uncommitted data which I'm you know I'm a later rollback so in which case his decision might be wrong so when I rolled back that he might see only the rollback data that's why by default you know if a transaction has locked a row you know other users will not be able to see that so a transaction should also be isolated and a transaction should be durable and this is pretty simple to understand once it change is made to a database it's permanent if a system error or power failure occurs before a set of come manses complete those commands are undone and the data is restored to its original state once the system begins running again so let's say for example we are executing a long-running transaction and let's say half of the data is modified and then all of a sudden there is a power failure so when that happens when the power comes back the database should be in such a position it has to roll back and undo the changes that it has already done and leave the database in a consistent and durable state that's what durable means all right if you want to receive email alerts when new videos are uploaded please subscribe to my youtube channel at youtube.com for / kud V&K 80 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: 158,760

Rating: 4.930295 out of 5

Keywords: Transactions, ACID Tests, sql server 2005, sql server 2008, rdbms acid properties, database acid properties with example, dbms acid properties, database acid properties, database acid examples, sql acid properties, transaction acid properties with example, transaction acid properties in dbms

Id: VLc4ewu6lUI

Length: 9min 49sec (589 seconds)

Published: Sun Oct 07 2012