Concurrency: Lost Updates

Concurrency: Lost Update

In a previous post, we discussed how to work with batches where we created a table named Accounting.BankAccounts and inserted two records. We will continue to use this table to continue our blog series on transaction isolation levels.

DECLARE @OldBalance int, @NewBalance int
BEGIN TRAN
	SELECT @OldBalance = Balance 
	FROM Accounting.BankAccounts
	WHERE AcctID = 1
	SET @NewBalance = @OldBalance - 300
WAITFOR DELAY '00:00:10:000'
	UPDATE Accounting.BankAccounts
	SET Balance = @NewBalance
	WHERE AcctID = 1

	SELECT @OldBalance AS OldBalance,
	AcctID, AcctName, Balance
	FROM Accounting.BankAccounts
	WHERE AcctID = 1
COMMIT TRAN

To begin, we will start with a new session and write the code as shown above. For this example, the Transaction Isolation Level should be using the default setting of Read Committed. Next, we will declare a variable named @OldBalance to hold the original balance of $500 from AcctID 1. Additionally, a variable named @NewBalance will be declared to hold the result from the calculation of @OldBalance minus $300, which should equal $200. Now to simulate running two sessions at the same time, we have added a WAITFOR DELAY statement to pause 10 seconds to allow us time to run the second session. In a real-world environment, you would not have this statement in your code. After the 10 seconds have expired, we will UPDATE the Accounting.BankAccoounts tables with the @NewBalance variable and then run a SELECT statement to compare the old balance of $500 with the updated balance of $200.

Lost Update Results 1
Session 1: Comparing the old balance with the updated balance.

During the 10 second delay of session one, we start a second session with the following code. This code is exactly the same as the first session with the exception that session two is subtracting $400 from the original balance. (And it is not waiting 10 seconds to update the table.)

DECLARE @OldBalance int, @NewBalance int
BEGIN TRAN
	SELECT @OldBalance = Balance 
	FROM Accounting.BankAccounts
	WHERE AcctID = 1
	SET @NewBalance = @OldBalance - 400

	UPDATE Accounting.BankAccounts
	SET Balance = @NewBalance
	WHERE AcctID = 1

	SELECT @OldBalance AS OldBalance,
	 AcctID, AcctName, Balance
	FROM Accounting.BankAccounts
	WHERE AcctID = 1
COMMIT TRAN

Again, in a real-world environment, session 1 would not have a 10-second delay inside the code. We are using it to simulate two separate sessions at the same time. The result of session 2 will also show the original balance of $500, but the new balance will be $100.

Lost Update Results 2
Session 2: Comparing the old balance with the updated balance.

This is where the lost update concurrency issue becomes a problem. What is the actual balance for AcctID 1 after both sessions are executed? If the original balance was $500 and session one subtracted $300 while session two subtracted $400, we should have a balance of -$200. But, if we run a SELECT statement, we will observe that the balance ends up being a positive $200.

Basically, we lost the update from session two. This happens because with the Read Committed isolation level, the database engine will keep write locks on records until the end of a transaction but will release read locks once a SELECT statement has been executed. To solve this issue, we would change our transaction isolation level to either Repeatable Read, Serializable, or one of the Snapshot isolation levels.

Be the first to comment on "Concurrency: Lost Updates"

Leave a comment

Your email address will not be published.


*


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