Home » Lost update problem in concurrent transations

Lost update problem in concurrent transations

A lost update problem occurs in concurrent transactions, when two transactions try to read and update the same column on the same row within a database at the same time.

Suppose 1st transaction read any column value and process the update request while 2nd transaction began shortly afterward which read the same value and update the value immediately, later 1st transaction completes update process. In this way the updated value by 2nd transaction is lost as it is overwritten by 1st transaction.



Lets understand this with the help of above diagram, as you can see the 1st transaction read an available balance as 5000 and deduct an amount 2000 from available balance and process the update request while 2nd transaction immediately read an available balance as 5000 and deduct amount 1000 and quickly update the balance amount that is 5000 – 1000 = 4000, later 1st transaction completes the update request by updating the balance amount as 5000 – 2000 that is 3000, so in this way the 1st transaction overwrites the updated value of 2nd transaction.

Now the point is that, after the execution of both transactions the actual balance should be 2000, as 1st transaction deducts the amount 2000 and 2nd transaction deducts 1000 from available balance 5000 so the overall available balance should be 5000 – (1000 + 2000) = 2000,  but as a final result we get the overall available balance as 3000, means we lost the 2nd transaction update and this is called as Lost update problem.

Lets see this in practical, for this we will take two different session means two separate query window.

First we will create a sample table named UserBalance then insert some dummy records into this table.

Create table dbo.UserBalance
(
UserId int, 
Balance numeric(9,2)
)

insert into dbo.UserBalance
(UserId, Balance)
values
(501, 5000),
(502, 4000),
(503, 6000),
(504, 9000)

Now you can see table is created and it is having 4 records as shown below.



select * from dbo.UserBalance

Lets write the below T-SQL code in 1st query window.

-- 1st Transaction 
Begin Tran
Declare @AvailableBalance numeric(9,2)

Select @AvailableBalance = Balance
from dbo.UserBalance where UserId = 501

--Lets make tranasaction to wait for 15 seconds
Waitfor Delay '00:00:15'
Set @AvailableBalance = @AvailableBalance - 2000

Update dbo.UserBalance
Set Balance = @AvailableBalance where UserId = 501

Print @AvailableBalance

Commit Tran

After that write the below T-SQL Code in second query window.

-- 2nd Transaction
Begin Tran
Declare @AvailableBalance numeric(9,2)

Select @AvailableBalance = Balance
from dbo.UserBalance where UserId = 501

--Lets make tranasaction to wait for 5 seconds
Waitfor Delay '00:00:05'
Set @AvailableBalance = @AvailableBalance - 1000

Update dbo.UserBalance
Set Balance = @AvailableBalance where UserId = 501

Print @AvailableBalance

Commit Tran

You will notice that here we have used WaitFor delay artificial delay purposely to produce the lost update problem in concurrent transactions.



Make the 1st  transaction to wait for 15 sec before updating the balance amount while 2nd transaction waits for 1 sec to update the balance amount.

As you can see have taken two separate session window as shown below.

Lets execute both transactions quickly one by one, first run the 1st transaction then immediately run the second transaction.

You will see that, 2nd transaction update the balance amount as 4000, after that 1st transaction overwrites the updated value of 2nd transaction with 3000.

That means 2nd transaction lost the updated value.



Note that: Read Committed transaction and Read Uncommitted transaction isolation levels have the lost update side effect.

Above transactions run under the Read committed isolation level which is default isolation level for SQL Server that’s why we got the lost update problem.

To avoid the lost update problem you can run above transactions under any of the higher isolation levels such as Repeatable Read, Snapshot, or Serializable.



Lets set the transaction isolation level as Repeatable read for both above transactions to avoid lost update problem, before that we will update the balance as 5000 in table.

Update dbo.UserBalance
set Balance = 5000 where UserId = 501

Now we will set transaction isolation level REPEATABLE READ by adding a below T-SQL line at the top of both transactions

 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

For 1st transactions:

-- 1st Transaction 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Begin Tran
Declare @AvailableBalance numeric(9,2)

Select @AvailableBalance = Balance
from dbo.UserBalance where UserId = 501

--Lets make tranasaction to wait for 15 seconds
Waitfor Delay '00:00:15'
Set @AvailableBalance = @AvailableBalance - 2000

Update dbo.UserBalance
Set Balance = @AvailableBalance where UserId = 501

Print @AvailableBalance

Commit Tran

For 2nd transaction

-- 2nd Transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Begin Tran
Declare @AvailableBalance numeric(9,2)

Select @AvailableBalance = Balance
from dbo.UserBalance where UserId = 501

--Lets make tranasaction to wait for 5 seconds
Waitfor Delay '00:00:01'
Set @AvailableBalance = @AvailableBalance - 1000

Update dbo.UserBalance
Set Balance = @AvailableBalance where UserId = 501

Print @AvailableBalance

Commit Tran

Again we will run both transactions one by one as we did earlier.



Now you can see 2nd Transaction runs successfully while1st Transaction returns below error.
Transaction (Process ID 52) was deadlocked on lock resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

That means you need to rerun the 1st transaction again, in this way Repeatable read isolation level remove the lost update problem.

When you rerun the 1st transaction, the over all balance will be updated correctly as 2000 which is as we expected.

Lets rerun the 1st transaction and you can see this time the final updated balance is 2000 which is correct as per our expectation.

Lets check the balance for UserId 501 in table.

select * from dbo.UserBalance 
where UserId =501

And you can see the overall final balance amount is 2000.





Also Read..

Transaction concurrency: Dirty Reads problem in concurrent Transactions

SQL RollBack Transaction

 778 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.