Back to: SQL Server Tutorial For Beginners and Professionals
Lost Update Concurrency Problem in SQL Server with Examples
In this article, I am going to discuss the Lost Update Concurrency Problem in SQL Server with Examples. Please read our previous article before proceeding to this article where we discussed the Dirty Read Concurrency Problem in SQL Server with Examples. At the end of this article, you will understand what Lost Update Concurrency Problem is, when this problem occurs and how to overcome this Lost Update Concurrency Problem in SQL Server with Examples.
What is the Default Transaction Isolation Level in SQL Server?
The default Transaction Isolation level in SQL Server is Read committed. That means, if we do not specify any Transaction Isolation Level in SQL Server then by default it is Read Committed Transaction Isolation Level. With the Read Committed Transaction Isolation Level, we will get all sorts of Concurrency Problems (Lost Update, Non-Reaptable Read, and Phantom Read) except the Dirty Read Concurrency Problem.
What is Lost Update Concurrency Problem in SQL Server?
The Lost Update Concurrency Problem happens in SQL Server when two or more transactions are allowed to read and update the same data.
Understanding Lost Update Concurrency Problem
Let’s understand the Lost Update Concurrency Problem in SQL Server with an example. We are going to use the following Products table to understand this concept.
Please use the below SQL Script to create and populate the Products table with the required sample data.
-- Create Products table
CREATE TABLE Products
(
Id INT PRIMARY KEY,
Name VARCHAR(100),
Quantity INT
)
Go
-- Insert test data into Products table
INSERT INTO Products values (1001, 'Mobile', 10)
INSERT INTO Products values (1002, 'Tablet', 20)
INSERT INTO Products values (1003, 'Laptop', 30)
Example: Lost Update Concurrency Problem in SQL Server
Please have a look at the below image to understand the Lost Update Concurrency Problem in SQL Server. Here, we are creating two transactions, and both the transaction working with the same data.
As you can see in the above image, we have 2 transactions i.e. Transaction 1 and Transaction 2. Transaction 1 starts first, and it is processing an order for 1 Mobile and it reads the Quantity available for the product Mobile as 10 from the database. At the same time, Transaction 2 started and it is processing an order for 2 Mobiles. It also reads the Quantity available as 10 from the database. Transaction 2 makes the sale first and it updates the Quantity available with a value of 8 in the database. Then Transaction 1 completes the sale and it silently overwrites the update the Quantity available as 9, while it actually should have updated it to 7. This is nothing but the Lost Update Concurrency Problems in SQL Server.
Example: Lost Update Concurrency Problem
Open 2 instances of SQL Server Management Studio. From the first instance execute the Transaction 1 code and from the second instance, execute the Transaction 2 code.
Transaction 1 is processing an order for 1 Mobile, while Transaction 2 is processing an order for 2 Mobiles. At the end of both the transactions, the Quantity of the Mobile should be 7 in the database, but we have a value of 9. This is because Transaction 1 silently overwrites the update which is made by Transaction 2. This is called the Lost Update Concurrency Problem in SQL Server.
Transaction1
In the first instance copy and paste the following code. This is going to be our first transaction.
-- Transaction 1 BEGIN TRANSACTION DECLARE @QunatityAvailable int SELECT @QunatityAvailable = Quantity FROM Products WHERE Id=1001 -- Transaction takes 10 seconds WAITFOR DELAY '00:00:10' SET @QunatityAvailable = @QunatityAvailable - 1 UPDATE Products SET Quantity = @QunatityAvailable WHERE Id=1001 Print @QunatityAvailable COMMIT TRANSACTION
Transaction2
In the second instance copy and paste the following code. This is going to be our second transaction.
-- Transaction 2 BEGIN TRANSACTION DECLARE @QunatityAvailable int SELECT @QunatityAvailable = Quantity FROM Products WHERE Id=1001 SET @QunatityAvailable = @QunatityAvailable - 2 UPDATE Products SET Quantity = @QunatityAvailable WHERE Id=1001 Print @QunatityAvailable COMMIT TRANSACTION
First, run the transaction 1 code and then immediately run the transaction 2 code. And you will see that Transaction2 is completed first and then the second transaction is completed. Now, if you verify the Products table, then you will see that the quantity for the product Mobile is 9 by executing the below query.
SELECT * FROM Products WHERE Id = 1001
Once you execute the above query, then you will get the following output.
How to Overcome the Lost Update Concurrency Problem?
Both Read Uncommitted and Read Committed Transaction Isolation Levels have the Lost Update Concurrency Problem. The other Isolation Levels such as Repeatable Read, Snapshot, and Serializable do not have the Lost Update Concurrency Problem. So, if we run the above Transactions using any of the higher Transaction Isolation Levels such as Repeatable Read, Snapshot, or Serializable, then we will not get the lost update concurrency problem.
Let us see how to overcome the Lost Update Concurrency Problem using the Repeatable Read Transaction Isolation Level in SQL Server. The Repeatable Read Transaction Isolation Level uses additional locking on rows that are read by the current transaction which prevents those rows to be updated or deleted by other transactions. This solves the Lost Update Concurrency Problem. First, update the Quantity as 10 for the Product Mobile by executing the below update query.
Update Products SET Quantity = 10 WHERE Id = 1001
Modify both the transactions as shown below to use the Repeatable Read Isolation Level to solve to Lost Update Concurrency Problem.
Transaction 1
-- Transaction 1 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION DECLARE @QunatityAvailable int SELECT @QunatityAvailable = Quantity FROM Products WHERE Id=1001 -- Transaction takes 10 seconds WAITFOR DELAY '00:00:10' SET @QunatityAvailable = @QunatityAvailable - 1 UPDATE Products SET Quantity = @QunatityAvailable WHERE Id=1001 Print @QunatityAvailable COMMIT TRANSACTION
Transaction 2
-- Transaction 2 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION DECLARE @QunatityAvailable int SELECT @QunatityAvailable = Quantity FROM Products WHERE Id=1001 SET @QunatityAvailable = @QunatityAvailable - 2 UPDATE Products SET Quantity = @QunatityAvailable WHERE Id=1001 Print @QunatityAvailable COMMIT TRANSACTION
Now run Transaction1 first and then run the second transaction and you will see that Transaction 1 was completed successfully while Transaction 2 competed with the error. The 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, the Quantity will be updated correctly as expected in the database table. The following table shows the relation between the Concurrency Problem and the Transaction Isolation Level.
In the next article, I am going to discuss the Non-Repeatable Read Concurrency Problem in SQL Server with Examples. Here, in this article, I try to explain the Lost Update Concurrency Problem in SQL Server with Examples. I would like to have your feedback. Please post your feedback, question, or comments about this Lost Update Concurrency Problem article.
