Microsoft® SQL Server® 2012 Bible

1056

Part VIII: Performance Tuning and Optimization

FROM HumanResources.Department

WHERE DepartmentID = 1;

There is no result yet for transaction 2. It’s waiting for transaction 1 to complete, blocked

by transaction 1’s exclusive lock.

Requerying sys.dm_tran_locks reveals that the second transaction (spid 51) has an

intent to Share (IS) read lock and is waiting for a Share (S) read lock.

Result:

Spid Object Type Mode Status

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

51 HumanResources.Department PAGE IS GRANT

51 HumanResources.Department PAGE S WAIT

54 HumanResources.Department KEY X GRANT

54 HumanResources.Department PAGE IX GRANT

54 HumanResources.Department PAGE IX GRANT

54 HumanResources.Department KEY X GRANT

54 HumanResources.Department KEY X GRANT

While transaction 1 is holding its exclusive lock, transaction 2 has to wait. In other words,

transaction 1 blocks transaction 2.

Now, transaction 1 commits the transaction and releases the exclusive lock:

-- Transaction 1

COMMIT TRANSACTION

Immediately, transaction 1 completes and releases its locks. Transaction 2 springs to life

and performs the select, reading the committed change.

Result:

Name

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

New Name

The point of transaction isolation level read committed is to avoid reading uncommitted

data. So what if the update doesn’t change the data? If transaction 1 updates the data from

“John” to “John” what’s the harm of reading “John”?

SQL Server handles this situation by not respecting an exclusive lock if the page hasn’t

been changed, that is, if the page isn’t fl agged as dirty. This means that sometimes

(because there’s probably more data on the page than just the data in question) SQL Server

avoids locking and blocking if the data isn’t actually being changed.

You can prove this behavior by re-executing the previous locking and blocking sample code

with the same update value.

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

http://www.it-ebooks.info