Microsoft® SQL Server® 2012 Bible

1081

Chapter 47: Managing Transactions, Locking, and Blocking

47

-- Transaction 1

-- Insert a row in the range

INSERT HumanResources.Department (Name, GroupName)

VALUES ('ABC Dept', 'Test Dept')

-- Update Dept into the range

UPDATE HumanResources.Department

SET Name = 'ABC Test'

WHERE DepartmentID = 1 -- Engineering

-- Delete Dept from range

DELETE HumanResources.Department

WHERE DepartmentID = 17 -- Amazing FX Dept

The signifi cant observation is that none of transaction 1’s DML commands produced a

‘‘1 row(s) affected” message; it is blocked on the fi rst statement.

Transaction 2 now reselects the same range:

SELECT DepartmentID as DeptID, Name

FROM HumanResources.Department

WHERE Name BETWEEN 'A' AND 'G'

Result:

DeptID Name

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

17 Amazing FX Dept

12 Document Control

1 Engineering

16 Executive

14 Facilities and Maintenance

10 Finance

The select returns the same six rows with the same values. Transactional integrity is

intact and the phantom row fault has been thwarted.

Transaction 1 is still on hold, waiting for transaction 2 to complete its transaction:

COMMIT TRANSACTION

As soon as transaction 2 issues a commit transaction and releases its locks, transaction 1 is

free to makes its changes and three “1 row(s) affected” messages appear in transac-

tion 1’s messages pane:

SELECT *

FROM HumanResources.Department

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

http://www.it-ebooks.info