sp_lock System Stored Procedure

Reports information about locks.

Syntax

sp_lock [spid1 [, spid2]]

where

spid1

Is the SQL Server process ID number from master.dbo.sysprocesses. Execute sp_who to get the spid of the lock. If this parameter is not supplied, information about all locks is displayed.

spid2

Identifies another SQL Server process ID number to check for locks.

Remarks

Users can control locking by adding an optimizer hint to the FROM clause of a SELECT statement or by setting the TRANSACTION ISOLATION LEVEL with the SET statement. For syntax and restrictions, see the SELECT and SET statements.

In general, read operations acquire shared locks, and write operations acquire exclusive locks. Update locks are created at the page level and are acquired during the initial portion of an update operation when the pages are being read. Update locks are compatible with shared locks. Later, if the pages are changed, the update locks are promoted to exclusive locks.

An intent lock indicates the intention to acquire a shared or exclusive page level lock. An intent lock prevents another transaction from acquiring a table lock for that table.

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

The locktype column indicates whether the lock is a shared lock (Sh), an exclusive lock (Ex), or an update lock. This column also indicates whether the lock is held on a table (table or intent), a page, or an extent, and whether it is blocking (blk) another process. The blk suffix in the locktype indicates that this process is blocking another process that needs to acquire a lock. As soon as this process finishes, the other process(es) move forward.

When reading sp_lock information, use the OBJECT_NAME( ) function to get a table's name from its ID number. For example:

SELECT object_name(16003088)

Other information can also be displayed based on the spid returned from sp_lock. For information about using the Windows NT Performance Monitor to view information about a specific spid, see the DBCC statement.

Examples

A.    List All Locks

This example displays information about all locks currently held in SQL Server.

sp_lock

B.    List a Single Lock

This example displays information about locks currently held on spid 1.

sp_lock 1

Permission

Execute permission defaults to the public group.

Tables Used

master.dbo.spt_values, master.dbo.syslocks, master.dbo.sysprocesses

See Also