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.

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.

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.