Microsoft® SQL Server® 2012 Bible

1084

Part VIII: Performance Tuning and Optimization

Transaction 1 now begins a reading transaction, leaving the transaction open

(uncommitted):

USE AdventureWorks2012

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT LastName

FROM Person.Person

WHERE BusinessEntityID = 1

Result:

LastName

--------

Sánchez

A second transaction begins an update to the same row that the fi rst transaction has

open:

USE AdventureWorks2012;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

UPDATE Person.Person

SET LastName = 'Chapman'

WHERE BusinessEntityID = 1

SELECT LastName

FROM Person.Person

WHERE BusinessEntityID = 1

Result:

LastName

---------

Chapman

This is amazing. The second transaction updated the row even though the fi rst transaction

is still open. Going back to the fi rst transaction, it can still see the original data:

SELECT LastName

FROM Person.Person

WHERE BusinessEntityID = 1

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

http://www.it-ebooks.info