![]() The moment I execute that I immediately get that row.īut then when I try to get the row, Id equal to 1 that is being updated by the other transaction. I’m selecting a row where the product ID is equal to 2 look at this. And when I say select * from TblProducts where id=2. ![]() That’s why other users by default will not be able to see the data.It’s just waiting for the transaction to complete. Why? Because there is a transaction that is still being processed. So now when I say select * from TblProductswhen I execute query, it is executing query but it will never get access to that table. Now before updating he wants to select the data and see records. So he issued let’s say, another transaction, or let’s just say he is trying to update the quantity. Now, let’s say there is another person, who connected to the SQL server and he is also trying to update the same table. We haven’t committed this transaction or rolled it back, so this transaction is still being processed. Now A Transaction is now being processed on the TblProducts table. To begin the transaction when I execute the above query. Update TblProducts set Quantity = 150 where id = 1 And now let’s make this update statement part of the transaction. Now, let’s say I want to update the TblProducts, let’s say quantity available=150 where product ID =1. ![]() let’s see how the transaction achieves isolation. They both of them,should work as a single unit of work and they shouldn’t interfere with each other. Then transaction A shouldn’t interfere with transaction B and similarly Transaction B shouldn’t interfere with transaction A. So if there are two transactions, transaction A and transaction B. What we mean by this, the transaction must affect data without interfering with other concurrent transactions or being interfered with by them. So that is what consistency says all data in the transaction is left in a logically consistent state. Otherwise, we cannot account for those 10 Books. You know, a transaction should undo the first statement and put it back to 100. So, for example, if I am selling 10 Books, the first update statements reduce that quantity from the Book quantity available.Īnd then let’s say when we are about to execute the insert statement in the TblProductSale table, the power has gone or something has happened in those circumstances. The transaction should also be consistent, which means all data attached to the transaction is left in a logically consistent state.įor example, if quantity available numbers are decrement from TblProducts, then there has to be an associated entry into the TblProductSale. what is the consistency of a transaction? So either both of them should succeed or none of them, that’s what is the Atomicity of a transaction. So that the database is in a state that is similar to before we have begin the transaction. If one of them fails, the data that’s modified by the other states should be undone and rolled back. So the Atomicity of a transaction states that both of these statements should be treated as one unit. And these two statements are wrapped inside a transaction. The first statement updates TblProducts and the next statement inserts a row into the TblProductSale table. Where Id = 1 Insert into TblProductSale values( 3, 1, 10,GETDATE()) Update TblProducts set Quantity = (Quantity - 10) So that’s what below transaction is doing here. Update TblProducts and insert a row into TblProductSale. And then we also have to make an entry into TblProductSale.īasically when we sell a product, two things happening. ![]() So we have to update quantity available to 100-10=90. The first thing that I have to do here is to check, what’s the quantity available for Books? And then from that quantity available, I will have to deduct the 10 Books that I am selling. Let’s say, for example, I’m selling 10 Books. So when we sell a product two think should happen first, we should check the quantity available. Now, whenever we sell a product an entry should also be made into the TblProductSale table. TblProducts is like an inventory table that contains information about the products and the quantity available. Script Date: 9:52:33 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |