Microsoft® SQL Server® 2012 Bible

1079

Chapter 47: Managing Transactions, Locking, and Blocking

47

Keep a Copy of Your AdventureWorks2012

Database

Many of the samples in this chapter assume an unmodifi ed version of the AdventureWorks2012 data-

base. Once you download the AdventureWorks2012 data fi le, keep a copy of it around so that you can

attach it after running through the examples and start with a clean copy of the database.

-- Transaction 2

USE AdventureWorks2012

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

BEGIN TRANSACTION

SELECT DepartmentID as DeptID, Name

FROM HumanResources.Department

WHERE Name BETWEEN 'A' AND 'G'

Result:

DeptID Name

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

12 Document Control

1 Engineering

16 Executive

14 Facilities and Maintenance

10 Finance

Transaction 1 now inserts a new row into the range selected by transaction 2:

-- Transaction 1

-- Insert a row in the range

INSERT HumanResources.Department (Name, GroupName)

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

When transaction 2 selects the same range again, if ‘ABC Dept’ is in the result list, then a

phantom row transaction fault occurred:

-- Transaction 2

SELECT DepartmentID as DeptID, Name

FROM HumanResources.Department

WHERE Name BETWEEN 'A' AND 'G'

COMMIT TRANSACTION

Result:

DeptID Name

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

17 ABC Dept

12 Document Control

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

http://www.it-ebooks.info