Transact-SQL sp_lock 存储过程解析-优快云博客

Transact-SQL Reference

sp_lock

Reports information about locks.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_la-lz_6cdn.asp

[@more@]

Syntax

sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2']

Arguments

[@spid1 =] 'spid1'

Is the Microsoft® SQL Server™ process ID number from master.dbo.sysprocesses. spid1 is int, with a default of NULL. Execute sp_who to obtain process information about the lock. If spid1 is not specified, information about all locks is displayed.

[@spid2 =] 'spid2'

Is another SQL Server process ID number to check for lock information. spid2 is int, with a default of NULL. spid2 is another spid that may have a lock at the same time as spid1, and on which the user also wants information.

Note sp_who can have zero, one, or two parameters. These parameters determine whether the stored procedure displays locking information on all, one, or two spid processes.

Return Code Values

0 (success)

Result Sets

Column name	Data type	Description
spid	smallint	The SQL Server process ID number.
dbid	smallint	The database identification number requesting a lock.
ObjId	int	The object identification number of the object requesting a lock.
IndId	smallint	The index identification number.
Type	nchar(4)	The lock type: DB = Database FIL = File IDX = Index PG = PAGE KEY = Key TAB = Table EXT = Extent RID = Row identifier
Resource	nchar(16)	The lock resource that corresponds to the value in syslockinfo.restext.
Mode	nvarchar(8)	The lock requester's lock mode. This lock mode represents the granted mode, the convert mode, or the waiting mode.
Status	int	The lock request status: GRANT WAIT CNVRT

Remarks

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

In general, read operations, acquire shared locks, and write operations acquire exclusive locks. Update locks are acquired during the initial portion of an update operation when the data is being read. Update locks are compatible with shared locks. Later, if the data is changed, the update locks are promoted to exclusive locks. There are times when changing data that an update lock is briefly acquired prior to an exclusive lock. This update lock will then be automatically promoted to an exclusive lock.

Different levels of data can be locked including an entire table, one or more pages of the table, and one or more rows of a table. Intent locks at a higher level of granularity mean locks are either being acquired or intending to be acquired at a lower level of lock granularity. For example, a table 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.

When reading sp_lock information, use the OBJECT_NAME( ) function to get the name of a table from its ID number, for example:

SELECT object_name(16003088)

All distributed transactions not associated with a SPID value are orphaned transactions. SQL Server 2000 assigns all orphaned distributed transactions the SPID value of '-2', making it easier for a user to identify blocking distributed transactions. For more information, see KILL.

For more information about using the Windows NT Performance Monitor to view information about a specific process ID, see DBCC.