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.
Be the first to comment on "Concurrency: Lost Updates"