1 Version 4.0 -- 1/15/89 sp_lock
______________________________________________________________________
NAME: sp_lock
FUNCTION:
Reports information on locks.
SYNTAX:
sp_lock [spid1 [, spid2]]
EXAMPLES:
1) sp_lock
Displays information on all locks currently held in
sp_lock Version 4.0 -- 1/15/89 2
______________________________________________________________________
SQL Server:
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
(5 rows affected)
2) sp_lock 1
Displays information on locks currently held on spid 1:
spid locktype table_id page dbname
3 Version 4.0 -- 1/15/89 sp_lock
______________________________________________________________________
----- --------- --------- ---- -----------
1 Sh_intent 16003088 0 master
PARAMETERS:
spid1 - is SQL Server process ID number from
master.dbo.sysprocesses. Run sp_who to get the spid of the
lock. The parameter is optional. If not supplied, informa-
tion on all locks is displayed.
spid2 - is another SQL Server process ID number to check for
locks. The parameter is optional.
COMMENTS:
o Executing sp_lock reports information on processes that
currently hold locks.
o Use the OBJECT_ID() function to derive a table's name from its
sp_lock Version 4.0 -- 1/15/89 4
______________________________________________________________________
id number.
o If the procedure is executed without parameters, it displays a
list of the current locks.
o The locktype column indicates not only whether the lock is a
shared lock (Sh) or an exclusive lock (Ex), but also whether it
is held on a table or a page, and whether it is an intent lock,
an extent lock, or a demand lock.
An intent lock indicates the intention to acquire a shared or
exclusive lock on a data page. Setting an intent lock prevents
another transaction from acquiring an exclusive lock on the
table that contains that page.
An extent lock is a lock held on a group of eight database
pages while they are being allocated or de-allocated. Extent
locks are set while a CREATE or DROP command is running, or
5 Version 4.0 -- 1/15/89 sp_lock
______________________________________________________________________
while an INSERT operation that requires new data or index pages
is running.
A demand lock prevents any more shared locks from being set.
It indicates that a transaction is next in line to lock a table
or page. Demand locks are necessary because shared locks can
overlap, so that read transactions keep monopolizing a table or
page, forcing a write transaction to wait indefinitely. After
waiting on four different read transactions, a write transac-
tion is given a demand lock. As soon as the existing read
transactions finish, the write transaction is allowed to
proceed. Any new read transactions then have to wait for the
write transaction to finish.
PERMISSIONS:
Execute permission to public.
sp_lock Version 4.0 -- 1/15/89 6
______________________________________________________________________
TABLES USED:
master.dbo.spt_values, master.dbo.syslocks
SEE ALSO:
sp_who, KILL