Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 156

SET FirstName = 'James'
WHERE LastName = 'Jones';

WAITFOR DELAY '00:00:05.000';

ROLLBACK TRANSACTION;

SELECT FirstName
,LastName
FROM Person.Person
WHERE LastName = 'Jones';

Once Session 1 is running, quickly switch over to a second session and execute
the following SQL statement. The SQL in this second session will perform a dirty
read. If you time it right and execute this query while the transaction in Session 1
is open (it has not yet been rolled back), then your output will match Figure 6-3
and every person with a surname of “Jones” now has a i rst name of “James”:

FIGURE 6-3

/* SESSION 2 */
USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT FirstName
,LastName
FROM Person.Person
WHERE LastName = 'Jones';

Non-Repeatable Reads
A non-repeatable read is one in which data read twice inside the same transaction cannot be
guaranteed to contain the same value. This very behavior was discussed when looking at transac-
tions earlier in the chapter. 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 i ve people
from Person.Person and then waits for i ve seconds before repeating the step. Execute the code
in Session 1 before l ipping to a second session and executing the code in Session 2 (code i le
Ch6NonRepeatableReads.sql ):

/*SESSION 1*/
USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL