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.
To begin, we will start with a new session and write the code as shown above. For this example, we will set the Transaction Isolation Level to Read Committed. Later in this post, we will run the code again but with the Transaction Isolation Level set to Repeatable Read. We begin our transaction for session one to select the AcctID and ModifiedDate from the Accounting.BankAccounts table.
Now to simulate running two sessions at the same time, we have added a WAITFOR DELAY statement to pause 30 seconds to allow us time to run the second session. In a real-world environment, you most likely would not have this statement in your code. While we are waiting for the 30-second delay, we will run the code for the second session that will update the ModifiedDate field to 01/05/2013.
After the 30-second delay has expired, session one will run the SELECT statement again to retrieve the AcctID and ModifiedDate from the Accounting.BankAccounts table. Notice that the ModifiedDate field changed during the session one transaction. This concurrency problem is called an Inconsistent Analysis or a Non-Repeatable Read.
To solve this problem, we will run the code again. First, we need to reset the example code. Run the second session again with the date of ’12/12/2015′ to change the date back to the original setting. Once the date is reset, update the code for session two to SET ModifiedDate = GETDATE(). Wait to execute the second session until the transaction isolation level for session one has been changed. Set the Transaction Isolation Level for session one to Repeatable Read. Execute the code for session one and while waiting for the 30-second delay, run the code for session two.
Notice this time that session two had to wait until session one finished. This is because, with the Repeatable Read isolation level, the database engine will keep both read and write locks on records until the end of the transaction. Additionally, notice that this time the dates did not change from before the 30-second delay and after the delay.