sql server lost update problem
108.7 هزار بار بازدید -
9 سال پیش
-
Text version of the video
Text version of the video
http://csharp-video-tutorials.blogspo...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
@aarvikitchen5572
Slides
http://csharp-video-tutorials.blogspo...
All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...
All SQL Server Slides
http://csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
kudvenkatarabic
In this video we will discuss, lost update problem in sql server with an example.
Lost update problem happens when 2 transactions read and update the same data. Let's understand this with an example.
Consider the following 2 transactions - Transaction 1 and Transaction 2. Transaction 1 starts first, and it is processing an order for 1 iPhone. It sees ItemsInStock as 10.
At this time Transaction 2 is processing another order for 2 iPhones. It also sees ItemsInStock as 10. Transaction 2 makes the sale first and updates ItemsInStock with a value of 8.
At this point Transaction 1 completes the sale and silently overwrites the update of Transaction 2. As Transaction 1 sold 1 iPhone it has updated ItemsInStock to 9, while it actually should have updated it to 7.
Example : The lost update problem example. Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Transaction 1 is processing an order for 1 iPhone, while Transaction 2 is processing an order for 2 iPhones. At the end of both the transactions ItemsInStock must be 7, but we have a value of 9. This is because Transaction 1 silently overwrites the update of Transaction 2. This is called the lost update problem.
-- Transaction 1
Begin Tran
Declare @ItemsInStock int
Select @ItemsInStock = ItemsInStock
from tblInventory where Id=1
-- Transaction takes 10 seconds
Waitfor Delay '00:00:10'
Set @ItemsInStock = @ItemsInStock - 1
Update tblInventory
Set ItemsInStock = @ItemsInStock where Id=1
Print @ItemsInStock
Commit Transaction
-- Transaction 2
Begin Tran
Declare @ItemsInStock int
Select @ItemsInStock = ItemsInStock
from tblInventory where Id=1
-- Transaction takes 1 second
Waitfor Delay '00:00:1'
Set @ItemsInStock = @ItemsInStock - 2
Update tblInventory
Set ItemsInStock = @ItemsInStock where Id=1
Print @ItemsInStock
Commit Transaction
Both Read Uncommitted and Read Committed transaction isolation levels have the lost update side effect. Repeatable Read, Snapshot, and Serializable isolation levels does not have this side effect. If you run the above Transactions using any of the higher isolation levels (Repeatable Read, Snapshot, or Serializable) you will not have lost update problem. The repeatable read isolation level uses additional locking on rows that are read by the current transaction, and prevents them from being updated or deleted elsewhere. This solves the lost update problem.
For both the above transactions, set Repeatable Read Isolation Level. Run Transaction 1 first and then a few seconds later run Transaction 2. Transaction 1 completes successfully, but Transaction 2 competes with the following error.
Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Once you rerun Transaction 2, ItemsInStock will be updated correctly as expected.
http://csharp-video-tutorials.blogspo...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
@aarvikitchen5572
Slides
http://csharp-video-tutorials.blogspo...
All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...
All SQL Server Slides
http://csharp-video-tutorials.blogspo...
All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...
All Dot Net and SQL Server Tutorials in Arabic
kudvenkatarabic
In this video we will discuss, lost update problem in sql server with an example.
Lost update problem happens when 2 transactions read and update the same data. Let's understand this with an example.
Consider the following 2 transactions - Transaction 1 and Transaction 2. Transaction 1 starts first, and it is processing an order for 1 iPhone. It sees ItemsInStock as 10.
At this time Transaction 2 is processing another order for 2 iPhones. It also sees ItemsInStock as 10. Transaction 2 makes the sale first and updates ItemsInStock with a value of 8.
At this point Transaction 1 completes the sale and silently overwrites the update of Transaction 2. As Transaction 1 sold 1 iPhone it has updated ItemsInStock to 9, while it actually should have updated it to 7.
Example : The lost update problem example. Open 2 instances of SQL Server Management studio. From the first window execute Transaction 1 code and from the second window, execute Transaction 2 code. Transaction 1 is processing an order for 1 iPhone, while Transaction 2 is processing an order for 2 iPhones. At the end of both the transactions ItemsInStock must be 7, but we have a value of 9. This is because Transaction 1 silently overwrites the update of Transaction 2. This is called the lost update problem.
-- Transaction 1
Begin Tran
Declare @ItemsInStock int
Select @ItemsInStock = ItemsInStock
from tblInventory where Id=1
-- Transaction takes 10 seconds
Waitfor Delay '00:00:10'
Set @ItemsInStock = @ItemsInStock - 1
Update tblInventory
Set ItemsInStock = @ItemsInStock where Id=1
Print @ItemsInStock
Commit Transaction
-- Transaction 2
Begin Tran
Declare @ItemsInStock int
Select @ItemsInStock = ItemsInStock
from tblInventory where Id=1
-- Transaction takes 1 second
Waitfor Delay '00:00:1'
Set @ItemsInStock = @ItemsInStock - 2
Update tblInventory
Set ItemsInStock = @ItemsInStock where Id=1
Print @ItemsInStock
Commit Transaction
Both Read Uncommitted and Read Committed transaction isolation levels have the lost update side effect. Repeatable Read, Snapshot, and Serializable isolation levels does not have this side effect. If you run the above Transactions using any of the higher isolation levels (Repeatable Read, Snapshot, or Serializable) you will not have lost update problem. The repeatable read isolation level uses additional locking on rows that are read by the current transaction, and prevents them from being updated or deleted elsewhere. This solves the lost update problem.
For both the above transactions, set Repeatable Read Isolation Level. Run Transaction 1 first and then a few seconds later run Transaction 2. Transaction 1 completes successfully, but Transaction 2 competes with the following error.
Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
Once you rerun Transaction 2, ItemsInStock will be updated correctly as expected.
9 سال پیش
در تاریخ 1394/05/25 منتشر شده
است.
108,751
بـار بازدید شده