Microsoft® SQL Server® 2012 Bible

1080

Part VIII: Performance Tuning and Optimization

DeptID Name

------ ----

1 Engineering

16 Executive

14 Facilities and Maintenance

10 Finance

ABC Dept is in the result list, and that’s the phantom row.

Serialized Transaction Isolation Level

The highest transaction isolation level can defend the transaction against the phantom

row. Transaction 2 fi rst inserts a sample row, “Amazing FX Dept,” so transaction 1 has

a row that can be deleted without worrying about referential integrity issues. It then sets

the transaction isolation level, begins a transaction, and reads a range of data:

-- Transaction 2

USE AdventureWorks2012

-- insert test row for deletion

INSERT HumanResources.Department

(Name, GroupName)

VALUES ('Amazing FX Dept', 'Test Dept')

SET TRANSACTION ISOLATION LEVEL

SERIALIZABLE

BEGIN TRANSACTION

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

Transaction 2’s select returned six rows.

With transaction 2 in a transaction and serialized transaction isolation level protecting the

range of names from “A” to “G”, transaction 1 attempts to insert, update, and delete into

and from that range:

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

http://www.it-ebooks.info