Displaying Locking Information

To get a report on the locks currently held on SQL Server, use SQL Enterprise Manager.

To get a report on locks

From the Server menu in SQL Enterprise Manager, choose Current Activity, and then examine the User Activity, Detail Activity, and Object Locks tabs in the Current Activity dialog box.

Execute the sp_lock system procedure:
```
sp_lock
```

spid	locktype	table_id	page	dbname
----	---------	--------	-----	-----------
1	Sh_intent	16003088	0	master
4	Ex_extent	0	440	pubs
4	Ex_extent	0	504	pubs
4	Sh_table	112003430	0	pubs
4	Ex_table	240003886	0	pubs

The locktype column provides the following information:

The type of lock: shared (Sh), exclusive (Ex), or Update.
What the lock is holding: a table, page, or extent
Whether the lock represents an intent to acquire a shared or exclusive lock on a data page.
Whether the lock is blocking (blk) another process

In general, you apply shared locks for read operations and exclusive locks for write operations. However, if you want to read data initially, but possibly change it later, use an update lock.

An intent lock indicates that SQL Server wants to acquire a shared or exclusive lock on a data page. Setting an intent lock prevents another transaction from subsequently acquiring an exclusive lock on the table containing that page.

An extent lock is a lock held on a group of eight contiguous database pages while they are being allocated or freed. Extent locks are set during a CREATE or DROP statement, or during an INSERT operation that requires new data or index pages.

The blk suffix indicates that one process is blocking other processes that need to acquire locks. As soon as the blocking process completes, the other processes move forward.

For more information about locks, you can query the syslocks system table. For details, see the Microsoft SQL Server Administrator's Companion.