The lost update problem occurs when 2 concurrent transactions try to read and update the same data. Let’s understand this with the help of an example.
Suppose we have a table named “Product” that stores id, name, and ItemsinStock for a product.
It is used as part of an online system that displays the number of items in stock for a particular product and so needs to be updated each time a sale of that product is made.
The table looks like this:
|
Id |
Name |
ItemsinStock |
|
1 |
Laptops |
12 |
Now consider a scenario where a user arrives and initiates the process of buying a laptop. This will initiate a transaction. Let’s call this transaction, transaction 1.
At the same time another user logs into the system and initiates a transaction, let’s call this transaction 2. Take a look at the following figure.
Transaction 1 reads the items in stock for laptops which is 12. A little later transaction 2 reads the value for ItemsinStock for laptops which will still be 12 at this point of time. Transaction 2 then sells three laptops, shortly before transaction 1 sells 2 items.
Transaction 2 will then complete its execution first and update ItemsinStock to 9 since it sold three of the 12 laptops. Transaction 1 commits itself. Since transaction 1 sold two items, it updates ItemsinStock to 10.
This is incorrect, the correct figure is 12-3-2 = 7
Working Example of Lost Update Problem
Let’s us take a look at the lost update problem in action in SQL Server. As always, first, we will create a table and add some dummy data into it.
As always, be sure that you are properly backed up before playing with new code. If you’re not sure, see this article on SQL Server backup.
Execute the following script on your database server.
<span style="font-size: 14px;">CREATE DATABASE pos;
USE pos;
CREATE TABLE products
(
Id INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
ItemsinStock INT NOT NULL
)
INSERT into products
VALUES
(1, 'Laptop', 12),
(2, 'Iphon', 15),
(3, 'Tablets', 10)</span>Now, open two SQL server management studio instances side by side. We will run one transaction in each of these instances.
Add the following script to the first instance of SSMS.
<span style="font-size: 14px;">USE pos;
-- Transaction 1
BEGIN TRAN
DECLARE @ItemsInStock INT
SELECT @ItemsInStock = ItemsInStock
FROM products WHERE Id = 1
WaitFor Delay '00:00:12'
SET @ItemsInStock = @ItemsInStock - 2
UPDATE products SET ItemsinStock = @ItemsInStock
WHERE Id = 1
Print @ItemsInStock
Commit Transaction</span>This is the script for transaction 1. Here we begin the transaction and declare an integer type variable “@ItemsInStock”. The value of this variable is set to the value of the ItemsinStock column for the record with Id 1 from the products table. Then a delay of 12 seconds is added so that transaction 2 can complete its execution before transaction 1. After the delay, the value of @ItemsInStock variable is decremented by 2 signifying the sale of 2 products.
Finally, the value for ItemsinStock column for the record with Id 1 is updated with the value of @ItemsInStock variable. We then print the value of @ItemsInStock variable on the screen and commit the transaction.
In the second instance of SSMS, we add the script for transaction 2 which is as follows:
<span style="font-size: 14px;">USE pos;
-- Transaction 2
BEGIN TRAN
DECLARE @ItemsInStock INT
SELECT @ItemsInStock = ItemsInStock
FROM products WHERE Id = 1
WaitFor Delay '00:00:3'
SET @ItemsInStock = @ItemsInStock - 3
UPDATE products SET ItemsinStock = @ItemsInStock
WHERE Id = 1
Print @ItemsInStock
Commit Transaction</span>The script for transaction 2 is similar to transaction 1. However, here in transaction 2, the delay is only for three seconds and the decrement in the value for @ItemsInStock variable is three, as it is a sale of three items.
Now, run transaction 1 and then transaction 2. You will see transaction 2 completing its execution first. And the value printed for @ItemsInStock variable will be 9. After some time transaction 1 will also complete its execution and the value printed for its @ItemsInStock variable will be 10.
Both of these values are wrong, the actual value for ItemsInStock column for the product with Id 1 should be 7.
NOTE:
It is important to note here that the lost update problem only occurs with read committed and read uncommitted transaction isolation levels. With all the other transaction isolation levels, this problem does not occur.
Read Repeatable Transaction Isolation Level
Let’s update the isolation level for both the transactions to read repeatable and see if the lost update problem occurs. But before that, execute the following statement to update the value for ItemsInStock back to 12.
Update products SET ItemsinStock = 12
Script For Transaction 1
<span style="font-size: 14px;">USE pos;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- Transaction 1
BEGIN TRAN
DECLARE @ItemsInStock INT
SELECT @ItemsInStock = ItemsInStock
FROM products WHERE Id = 1
WaitFor Delay '00:00:12'
SET @ItemsInStock = @ItemsInStock - 2
UPDATE products SET ItemsinStock = @ItemsInStock
WHERE Id = 1
Print @ItemsInStock
Commit Transaction</span>Script For Transaction 2
<span style="font-size: 14px;">USE pos;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
-- Transaction 2
BEGIN TRAN
DECLARE @ItemsInStock INT
SELECT @ItemsInStock = ItemsInStock
FROM products WHERE Id = 1
WaitFor Delay '00:00:3'
SET @ItemsInStock = @ItemsInStock - 3
UPDATE products SET ItemsinStock = @ItemsInStock
WHERE Id = 1
Print @ItemsInStock
Commit Transaction</span>Here in both the transactions, we have set the isolation level to repeatable read.
Now run transaction 1 and then immediately run transaction 2. Unlike the previous case, transaction 2 will have to wait for transaction 1 to commit itself. After that the following error occurs for transaction 2:
Msg 1205, Level 13, State 51, Line 15
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
This error occurs because repeatable read locks the resource which is being read or updated by transaction 1 and it creates a deadlock on the other transaction that tries to access the same resource.
The error says that transaction 2 has a deadlock on a resource with another process and that this transaction has been blocked by the deadlock. This means that the other transaction was given access to the resource while this transaction was blocked and not given access to the resource.
It also says to rerun the transaction as the resource is free now. Now, if you run transaction 2 again, you will see the correct value of items in stock i.e. 7. This is because transaction 1 had already decremented the IteminStock value by 2, transaction 2 further decrements this by 3, therefore 12 – (2+3) = 7.
Tags: sql server Last modified: September 23, 2021
