Microsoft® SQL Server® 2012 Bible

1077

Chapter 47: Managing Transactions, Locking, and Blocking

47

REPEATABLE READ

BEGIN TRANSACTION;

SELECT Name

FROM HumanResources.Department

WHERE DepartmentID = 1

Result:

Name

------------------------

Engineering

Transaction 1 now updates the department name to Non-Repeatable Read:

-- Transaction 1

USE AdventureWorks2012

UPDATE HumanResources.Department

SET Name = 'Non-Repeatable Read'

WHERE DepartmentID = 1

Here’s the fi rst major difference: Transaction 1 is blocked because it tries to update a record

still locked by transaction 2. Shared locks are still present on the records read from trans-

action 2, so transaction 1 cannot modify the data.

Transaction 2 reads the row again. If it sees the value updated by transaction 2, that’s a

nonrepeatable read:

SELECT Name

FROM HumanResources.Department

WHERE DepartmentID = 1

Result:

Name

------------------------

Engineering

But the result is not the updated value from transaction 1. Instead the original value is still

in place. The read was repeatable and the nonrepeatable read fault has been prevented.

When transaction 2 completes the transaction, it releases the share lock:

COMMIT TRANSACTION;

Immediately, transaction 1, is now free to complete its update and the “1 row(s)

affected” message appears in the Messages pane.

Although repeatable read protects against the selected rows being updated, it doesn’t pro-

tect against new rows being added to or deleted from the selected range. This is known as

a phantom row, which means you could get a different value/set of results if new rows are

added or deleted. To avoid this, use a Serializable transaction isolation level of protection.

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

http://www.it-ebooks.info