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.

SET TRANSACTION ISOLATION LEVEL
READ COMMITTED 
BEGIN TRAN
	SELECT AcctID, ModifiedDate
	FROM Accounting.BankAccounts
WAITFOR DELAY '00:00:10:000'
	SELECT AcctID, ModifiedDate
	FROM Accounting.BankAccounts
COMMIT TRAN

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 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. While we are waiting for the 10-second delay, we will run the code for the second session that will update the ModifiedDate field to 01/05/2013.

BEGIN TRAN
	UPDATE Accounting.BankAccounts
	SET ModifiedDate = '01/05/2013' --Change Date
COMMIT TRAN

After the 10-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 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.

--Use Session 2 to reset demonstration
BEGIN TRAN
	UPDATE Accounting.BankAccounts
	SET ModifiedDate = '12/12/2015' --Reset Date
COMMIT TRAN

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.

--Use Session 2 to reset demonstration
BEGIN TRAN
	UPDATE Accounting.BankAccounts
	SET ModifiedDate = GETDATE() --Reset Date
COMMIT TRAN

Now, set the Transaction Isolation Level for session one to Repeatable Read. Execute the code for session one and while waiting for the 10-second delay, run the code for session two.

SET TRANSACTION ISOLATION LEVEL
REPEATABLE READ
BEGIN TRAN
	SELECT AcctID, ModifiedDate
	FROM Accounting.BankAccounts
WAITFOR DELAY '00:00:10:000'
	SELECT AcctID, ModifiedDate
	FROM Accounting.BankAccounts
COMMIT TRAN

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 during the 10-second delay for session 1.