Locking and Concurrency - SQL Server 2012 Internals and Troubleshooting - page 169

WHERE ProductID =1;
--ROLLBACK TRAN;
SELECT resource_type
,resource_subtype
,resource_description
,resource_associated_entity_id
,request_mode
,request_status
FROM sys.dm_tran_locks
WHERE request_session_id = @@spid;
Note the intent locks ( request _ mode is IX ) on page and object in Figure 6-11. Now try to run this
ALTER TABLE statement in another query window:
FIGURE 6-11
USE AdventureWorks2012;
/* SESSION 2 */
BEGIN TRANSACTION;
ALTER TABLE Production.Product
ADD TESTCOLUMN INT NULL;
--ROLLBACK TRANSACTION;
The ALTER TABLE statement should be blocked. How do you know this? First, it will take forever
to make that change, as the explicit transaction in Session 1 hasn't been closed. However, more
important, look at row 5 in the output shown in Figure 6-12 (the query for sys . dm _ tran _ locks
has been rerun in the Session 1 window but also includes the SPID used for Session 2). Note that
the request_mode contains a schema modify lock , and that the request_status is set to WAIT .
This means it is on the wait list, which ties back to the fact that it is blocked. Finally, look at the
resource_type . It's an object resource request. The database engine checks for the existence of
an object resource_type for the same resource_associated_entity_id as the one requested.
Because one exists, the ALTER TABLE cannot proceed.
FIGURE 6-12
You might want to roll back those transactions now to release the locks.