May 27, 2003 at 9:35 am
Could somebody clarify some output from sp_lock for me.
These are the lines:
157 8 1997510445 0 TAB IX GRANT
157 8 1997510445 1 PAG 1:265 IX GRANT
157 8 1997510445 1 KEY (10024abad13d) X GRANT
(apologies if it the format looks strange).
Does this show that Spid 157 is performing one process and is locking Dbid 1997510445 and showing each level of locking, or that Spid 157 is attempting 3 processes
thanks.
Growing old is mandatory, growing up is optional
May 30, 2003 at 8:00 am
This was removed by the editor as SPAM
May 30, 2003 at 8:58 am
From SQL BOL
quote:
sp_lockReports information about locks.
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.
Permissions
Execute permissions default to the public role.
Examples
A. List all locks
This example displays information about all locks currently held in SQL Server.
USE master
EXEC sp_lock
B. List a lock from a single-server process
This example displays information, including locks, on process ID 53.
USE master
EXEC sp_lock 53
In your example 8 is the DBID, the other number is the object within that database.
This comes from SQL BOL topic "syslockinfo" (just ignore the Number and look at the value afterwards.)
quote:
Lock request mode. This column is the lock mode of the requester and represents either the granted mode, or the convert or waiting mode. Can be:0 = NULL. No access is granted to the resource. Serves as a placeholder.
1 = Sch-S (Schema stability). Ensures that a schema element, such as a table or index, is not dropped while any session holds a schema stability lock on the schema element.
2 = Sch-M (Schema modification). Must be held by any session that wants to change the schema of the specified resource. Ensures that no other sessions are referencing the indicated object.
3 = S (Shared). The holding session is granted shared access to the resource.
4 = U (Update). Indicates an update lock acquired on resources that may eventually be updated. It is used to prevent a common form of deadlock that occurs when multiple sessions lock resources for potential update at a later time.
5= X (Exclusive). The holding session is granted exclusive access to the resource.
6 = IS (Intent Shared). Indicates the intention to place S locks on some subordinate resource in the lock hierarchy.
7= IU (Intent Update). Indicates the intention to place U locks on some subordinate resource in the lock hierarchy.
8= IX (Intent Exclusive). Indicates the intention to place X locks on some subordinate resource in the lock hierarchy.
9 = SIU (Shared Intent Update). Indicates shared access to a resource with the intent of acquiring update locks on subordinate resources in the lock hierarchy.
10 = SIX (Shared Intent Exclusive). Indicates shared access to a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
11 = UIX (Update Intent Exclusive). Indicates an update lock hold on a resource with the intent of acquiring exclusive locks on subordinate resources in the lock hierarchy.
12 = BU. Used by bulk operations.
13 = RangeS_S (Shared Key-Range and Shared Resource lock). Indicates serializable range scan.
14 = RangeS_U (Shared Key-Range and Update Resource lock). Indicates serializable update scan.
15 = RangeI_N (Insert Key-Range and Null Resource lock). Used to test ranges before inserting a new key into an index.
16 = RangeI_S. Key-Range Conversion lock, created by an overlap of RangeI_N and S locks.
17 = RangeI_U. Key-Range Conversion lock, created by an overlap of RangeI_N and U locks.
18 = RangeI_X. Key-Range Conversion lock, created by an overlap of RangeI_N and X locks.
19 = RangeX_S. Key-Range Conversion lock, created by an overlap of RangeI_N and RangeS_S. locks.
20 = RangeX_U. Key-Range Conversion lock, created by an overlap of RangeI_N and RangeS_U locks.
21 = RangeX_X (Exclusive Key-Range and Exclusive Resource lock). This is a conversion lock used when updating a key in a range.
June 2, 2003 at 2:33 am
Thankyou for this very comprehensive explanation.
Growing old is mandatory, growing up is optional
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply