Database concurrency ensures that when multiple operations are occurring at once, the final result is still in agreement — that they concur. The problems described in this three-part article only occur when multiple sessions are occurring at once in your database, including lost updates (Part 1), dirty reads (Part 2) and non-repeatable reads.
NOTE:
You can run the examples in this article against the AdventureWorks2012 database.
What is a Non-repeatable Read?
A non-repeatable read is one in which data read twice inside the same transaction cannot be guaranteed to contain the same value. Depending on the isolation level, another transaction could have nipped in and updated the value between the two reads.
Non-repeatable reads occur because at lower isolation levels reading data only locks the data for the duration of the read, rather than for the duration of the transaction. Sometimes this behavior might be completely desirable. Some applications may want to know the absolute, real-time value, even mid transaction, whereas other types of transactions might need to read the same value multiple times.
Consider the following example. In Session 1 the transaction reads the data for the top five people from Person.Person and then waits for five seconds before repeating the step. Execute the code in Session 1 before flipping to a second session and executing the code in Session 2 (code file Ch6NonRepeatableReads.sql):
/*SESSION 1*/ USE AdventureWorks2012; SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --REPEATABLE READ; BEGIN TRANSACTION; SELECT TOP 5 FirstName ,MiddleName ,LastName ,Suffix FROM Person.Person ORDER BY LastName; WAITFOR DELAY '00:00:05.000'; SELECT TOP 5 FirstName ,MiddleName ,LastName ,Suffix FROM Person.Person ORDER BY LastName; COMMIT TRANSACTION; /*SESSION 2*/ USE AdventureWorks2012; BEGIN TRANSACTION; UPDATE Person.Person SET Suffix = 'Junior' WHERE LastName = 'Abbas' AND FirstName = 'Syed'; COMMIT TRANSACTION; /* UPDATE Person.Person SET Suffix = NULL WHERE LastName = 'Abbas' AND FirstName = 'Syed'; */
Providing you execute the update in Session 2 in time, your results will match Figure 1. The first read from Session 1, Syed Abbas, had no suffix; but in the second read he’s now Syed Abbas Junior. The first read, therefore, hasn’t been repeatable.
Figure 1.
You can use the commented-out code in Session 2 to reset the data. Execute this code now. To get a repeatable read, change the transaction isolation level in Session 1 as indicated here:
SET TRANSACTION ISOLATION LEVEL --READ COMMITTED; REPEATABLE READ;
Figure 2.
Now rerun Session 1 and Session 2 as before. You should notice that Session 2 has been blocked from performing its update until after the transaction has been completed. The first read in Session 1 is now repeatable. Your results from Session 1 should now match those in Figure 2.
Leave a Reply