Microsoft® SQL Server® 2012 Bible

1075

Chapter 47: Managing Transactions, Locking, and Blocking

47

concurrency. That being said, the read committed isolation level works quite well for most

SQL Server installations.

Level 3 — Repeatable Read

The third isolation level, repeatable read, is a more stringent isolation level than the previ-

ous two. In repeatable read, any shared lock acquired during the transaction is held until

the transaction has been committed. This is different than the read committed isolation

level, which holds only shared locks on data while it is being read. Holding shared locks

for the duration of the transaction ensures that the transaction can read the same data set

consistently for the duration of the transaction.

Nonrepeatable Read

A nonrepeatable read occurs when a transaction reads a set of data, performs additional

operations, and reads the set of data again in the same transaction, retrieving a different

result set the second time around, as shown in Figure 47-8. This type of isolation-level side

effect is present in the read-uncommitted and read-committed isolation levels.

FIGURE 47-8

A nonrepeatable read side effect occurs when transaction 2 selects the same data twice and

sees different values.

Isolation

t1 t2

Update

Commit

Select

Retrieves Updated Value Select

To demonstrate a nonrepeatable read, the following sequence sets up two concurrent trans-

actions. Transaction 2, on the right side, is in the default read committed transaction isola-

tion level, which allows the nonrepeatable read.

Assuming an unaltered copy of AdventureWorks2012, transaction 2 begins a logical transac-

tion and then reads the department name as Engineering.

-- Transaction 2

c47.indd 1075c47.indd 1075 7/31/2012 10:23:31 AM7/31/2012 10:23:31 AM

http://www.it-ebooks.info