The Lost Update Problem in Concurrent Transactions

Total: 7 Average: 4.1

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

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.

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.

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.

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

Understanding Dirty Read Problem with SQL Server

Total: 33 Average: 4.5

One of the most common problems that occur while running concurrent transactions is the Dirty Read problem. A dirty read occurs when one transaction is permitted to read data that is being modified by another transaction that is running concurrently but which has not yet committed itself.

If the transaction that modifies the data commits itself, the dirty read problem doesn’t occur. However if the transaction that modifies the data is rolled back after the other transaction has read the data, the latter transaction has dirty data that doesn’t actually exist.

Read More

Introducing Common Table Expressions in SQL Server

Total: 1 Average: 5

Common Table Expressions, or CTE for short, is simply a technique to create a temporary set of records that can be referenced within an INSERT, SELECT, UPDATE or DELETE statement.

Common table expressions were introduced by Microsoft in SQL Server 2005. They are not stored as objects in the database memory as their lifespan is equal to the execution time of the query. As soon as a query completes they are removed from the database memory. CTE can be referenced in a query as many times as you want and they can also be self-referencing.

Read More

Introduction to Temporary Tables in SQL Server

Total: 191 Average: 4.2

A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.

Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.

Read More