Back to: SQL Server Tutorial For Beginners and Professionals

Lost Update Concurrency Problem in SQL Server

In this article, I am going to discuss the Lost Update Concurrency Problem in SQL Server with an example. Please read our previous article before proceeding to this article where we discussed the Dirty Read Concurrency Problem in SQL Server with an example.

What is the Default Transaction Isolation Level in SQL Server?

The default Transaction Isolation level in SQL Server is Read committed. So, if we do not specify any transaction isolation level 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 except the Dirty Read Concurrency Problem.

What is Lost Update Concurrency Problem in SQL Server?

The Lost Update happens in SQL Server when two or more transactions are allowed to read and update the same data. Let’s understand the Lost Update Concurrency Issue with an example. We are going to use the following Products table for this example. 

Please use below SQL Script to create and populate the Products table with the required 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)

Lost Update Concurrency Problem in SQL Server Example:

As shown 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:

Open 2 instances of SQL Server Management Studio. From the first instance execute 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 the Transaction 1 silently overwrites the update which is made by Transaction 2. This is called the Lost Update Concurrency Problem in SQL Server. 

-- 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


-- Transaction 2
BEGIN TRANSACTION
  DECLARE @QunatityAvailable int

  SELECT @QunatityAvailable = Quantity
  FROM Products WHERE Id=1001

  -- Transaction takes 10 seconds
  WAITFOR DELAY '00:00:1'
  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 the Transaction2 complete first and then the second transaction completed. If you see the Products table the Quantity for the product Mobile is 9 as shown below.

SELECT * FROM Products WHERE Id = 1001

Note:

Both the Read Uncommitted and Read Committed Transaction Isolation Levels in SQL Server 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.

Using Repeatable Read Transaction Isolation Level:

Let us see how to overcome the Lost Update Concurrency Problem using Repeatable Read Transaction Isolation Level. The repeatable read 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 problem. 

First, update the Quantity as 10 for the Product Mobile as Shown below.

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
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
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:1'
  SET @QunatityAvailable = @QunatityAvailable - 2

  UPDATE Products
  SET Quantity = @QunatityAvailable 
  WHERE Id=1001

  Print @QunatityAvailable
COMMIT TRANSACTION

Now run the Transaction1 first and then run the second transaction and you will see that the Transaction 1 completed successfully while the Transaction 2 competed with the following 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, Quantity will be updated correctly as expected. The following table shows the relation between the Concurrency Problem and the Transaction Isolation Level.

In the next article, I will discuss the non-repeatable read concurrency problem in SQL Server with examples.

SUMMARY

In this article, I try to explain the Lost Update Concurrency Problem in SQL Server with an example. I would like to have your feedback. Please post your feedback, question, or comments about this article.