Microsoft® SQL Server™ 2005 Administrator's Companion

Viewing Locks

You can view current locks held by selecting from the system view sys.dm_tran_locks. (The sp_lock procedure used to view locks in previous versions of SQL Server is supported for backward compatibility only.) To show an example of locking, run the following T-SQL that creates a test table and inserts two rows for our test:

USE AdventureWorks;
CREATE TABLE test1(col1 int);
INSERT INTO test1 VALUES (1);
INSERT INTO test1 VALUES (2);

To create the ability to capture the specific lock information for this example, we open three connections to the server via Management Studio Query Editor. We will run an update on test1 in one window with a time delay before the transaction commits, perform a query on test1 in the second window, and view ...