Microsoft® SQL Server® 2012 Bible

1076

Part VIII: Performance Tuning and Optimization

USE AdventureWorks2012

SET TRANSACTION ISOLATION LEVEL

READ COMMITTED

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;

Transaction 2 reads the row again. If it sees the value updated by transaction 2, it results

in a nonrepeatable read:

SELECT Name

FROM HumanResources.Department

WHERE DepartmentID = 1

COMMIT TRANSACTION

Result:

Name

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

Non-Repeatable Read

Sure enough, transaction 2’s read was not repeatable.

Preventing the Fault

Rerunning the same scripts with transaction 2’s transaction isolation level to

repeatable read will result in a different behavior (assuming an unaltered copy of

AdventureWorks2012):

-- Transaction 2

USE AdventureWorks2012

SET TRANSACTION ISOLATION LEVEL

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

http://www.it-ebooks.info