Microsoft® SQL Server® 2012 Bible

1055

Chapter 47: Managing Transactions, Locking, and Blocking

47

FIGURE 47-1

Opening multiple Query Editor windows and sending the second tab into a New Vertical Tab

Group (using the tab’s context menu) is the best way to experiment with transactions.

Transaction 1 (on my machine it’s on session 54), now has an exclusive (X) write lock on the

row being updated by locking the key of the record I’m updating. The locks can be viewed

using the DMV sys.dm_tran_locks. (You can fi nd the full query and more details about

locks later in this chapter.)

Result:

Spid Object Type Mode Status

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

51 HumanResources.Department PAGE IX GRANT

51 HumanResources.Department KEY X GRANT

Transaction 2 ensures the transaction isolation level is set to the default and then attempts

to read the same row transaction 1 updates:

-- Transaction 2

USE AdventureWorks2012

SELECT Name

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

http://www.it-ebooks.info