sp_lock

Syntax

sp_lock [spid1[, spid2]] | [@verbose = int]

Parameters

spid1 – is the SAP ASE process ID number from the master.dbo.sysprocesses table. Run sp_who to get the spid of the locking process.
spid2 – is another SAP ASE process ID number to check for locks.
@verbose = int – displays a concatenated name of the table names instead of a spid, such as test..testa, following by the spid.
Note: int can be any number, as sp_lock only check to see whether the value of @verbose is null or not.

Examples

Example 1 – Shows the lock status of serial processes with spids 7, 18, and 23 and two families of processes. The family with fid 1 has the coordinating processes with spid 1 and worker processes with spids 8, 9, and 10. The family with fid 11 has the coordinating processes with spid 11 and worker processes with spids 12, 13, and 14:

sp_lock

The class column will display the cursor name for locks associated with a
cursor for the current user and the cursor id for other users.

fid spid locktype        table_id  page  dbname  class           context
--- ---- ------------- ---------- ----- ------- --------------  -------
0   7  Sh_intent      480004741     0 master  Non Cursor Lock NULL
0  18  Ex_intent       16003088     0 pubtune Non Cursor Lock NULL
0  18  Ex_page         16003088   587 pubtune Non Cursor Lock NULL
0  18  Ex_page         16003088   590 pubtune Non Cursor Lock NULL
0  18  Ex_page         16003088  1114 pubtune Non Cursor Lock NULL
0  18  Ex_page         16003088  1140 pubtune Non Cursor Lock NULL
0  18  Ex_page         16003088  1283 pubtune Non Cursor Lock NULL
0  18  Ex_page         16003088  1362 pubtune Non Cursor Lock NULL
0  18  Ex_page         16003088  1398 pubtune Non Cursor Lock NULL
0  18  Ex_page-blk     16003088   634 pubtune Non Cursor Lock NULL
0  18  Update_page     16003088  1114 pubtune Non Cursor Lock NULL
0  18  Update_page-blk 16003088   634 pubtune Non Cursor Lock NULL
0  23  Sh_intent       16003088     0 pubtune Non Cursor Lock NULL
0  23  Sh_intent      176003658     0 pubtune Non Cursor Lock NULL
0  23  Ex_intent      208003772     0 pubtune Non Cursor Lock NULL
1   1   Sh_intent     176003658    0  tpcd   Non Cursor Lock Sync-pt
duration request
1   1   Sh_intent-blk 208003772    0  tpcd   Non Cursor Lock Sync-pt
duration request
1   8  Sh_page        176003658 41571 tpcd    Non Cursor Lock NULL 
1   9  Sh_page        176003658 41571 tpcd    Non Cursor Lock NULL
1  10  Sh_page        176003658 41571 tpcd    Non Cursor Lock NULL
11  11   Sh_intent     176003658     0 tpcd   Non Cursor Lock Sync-pt
duration request
11  12  Sh_page        176003658 41571 tpcd    Non Cursor Lock NULL 
11  13  Sh_page        176003658 41571 tpcd    Non Cursor Lock NULL
11  14  Sh_page        176003658 41571 tpcd    Non Cursor Lock NULL

Example 2 – Displays information about the locks currently held by spid 7.

sp_lock 7

The class column will display the cursor name for locks associated with a
cursor for the current user and the cursor id for other users.
fid spid locktype   table_id  page dbname  class             context 
--- ---- ---------  --------- ---- ------  ----------------  ----------
 0   7   Sh_intent  480004741    0 master  Non Cursor Lock    NULL

Example 3 – First, queries the pubs2 database about the ID of its running processes that currently hold locks (1056003762), then queries the pubs2 database using the @verbose option, which returns the object name (master..spt_values) in addition to the process ID:

1> use pubs2
2> go
1> sp_lock
2> go

The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users.
fid spid loid locktype  table_id   page row dbname class context
--- ---- ---- --------  --------   ---- --- ------ ------ ----------
0     15   30 Sh_intent 1056003762    0   0 master Non Cursor Lock

(1 row affected)
(return status = 0)

1> sp_lock @verbose=0
2> go

The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other users.
fid spid loid locktype page row objectName         id       class  context
--- ---- ---- -------- ---- --- ------------------ -------- ------ -------
0     15   30 Sh_intent   0   0  master..spt_values 1056003762 Non Cursor Lock

(1 row affected)
(return status = 0)

Example 4 – This example shows all locks, including partition locks, currently held by SAP ASE.

sp_lock
go

fid spid loid locktype            table_id    partitionid page row dbname class context
--- ---- ---- --------            ----------  ----------- ---- --- ------ ------ ------- 
0     13   26 Ex_intent            420193516            0    0   0 master Non Cursor Lock
0     13   26 Ex_intent_partition  420193516    452193630    0   0 master Non Cursor Lock
0     13   26 Ex_page              420193516    452193630 4993   0 master Non Cursor Lock
0     14   28 Ex_intent            420193516            0    0   0 master Non Cursor Lock
0     14   28 Ex_intent_partition  420193516    468193687    0   0 master Non Cursor Lock
0     14   28 Ex_page              420193516    468193687 5001   0 master Non Cursor Lock
0     16   32 Sh_intent           1006623598            0    0   0 master Non Cursor Lock

Table lock and fine-grained lock values for partitionid are 0. partitionid is populated only for partition-level locks.

Usage

There are additional considerations when using sp_lock:

sp_lock with no parameters reports information on all processes that currently hold locks.
The only user control over locking is through the use of the holdlock keyword in the select statement.
Use the object_name system function to derive a table’s name from its ID number.
sp_lock in versions of the Cluster Edition earlier than 15.0.3 displayed information about only the locks associated with the instance on which you issued the stored procedure. sp_lock on Cluster Edition version 15.0.3 and later displays information about all locks in the cluster.
sp_lock output is ordered by fid and then spid.
sp_lock output also displays the following lock types:
- “Sh_row” indicates shared row locks
- “Update_row” indicates update row locks
- “Ex_row” indicates exclusive row locks

The sp_lock columns are:

loid – The column identifies unique lock owner ID of the blocking transaction. Even loid values indicate that a local transaction owns the lock. Odd values indicate that an external transaction owns the lock.
locktype – The column indicates whether the lock is a shared lock (“Sh” prefix), an exclusive lock (“Ex” prefix) or an update lock, and whether the lock is held on a table (“table” or “intent”) or on a page (“page”).
A “blk” suffix in the “locktype” column indicates that this process is blocking another process that needs to acquire a lock. As soon as this process completes, the other process(es) moves forward. A “demand” suffix in the “locktype” column indicates that the process is attempting to acquire an exclusive lock. See the Performance and Tuning Guide for more information about lock types.
class – The column indicates whether a lock is associated with a cursor. It displays one of the following:
- “Non Cursor Lock” indicates that the lock is not associated with a cursor.
- “Cursor Id number” indicates that the lock is associated with the cursor ID number for that SAP ASE process ID.
- A cursor name indicates that the lock is associated with the cursor cursor_name that is owned by the current user executing sp_lock.
fid – The column identifies the family (including the coordinating process and its worker processes) to which a lock belongs. Values for fid are:
- A zero value indicates that the task represented by the spid is executed serially. It is not participating in parallel execution.
- A nonzero value indicates that the task (spid) holding the lock is a member of a family of processes (identified by fid) executing a statement in parallel. If the value is equal to the spid, it indicates that the task is the coordinating process in a family executing a query in parallel.
context – The column identifies the context of the lock. Worker processes in the same family have the same context value. Legal values for “context” are as follows:
- “NULL” – the task holding this lock is either a query executing serially, or is a query executing in parallel in transaction isolation level 1.
- “Sync-pt duration request” – the task holding the lock holds the lock until the query is complete.
  
  A lock’s context may be “Sync-pt duration request” if the lock is a table lock held as part of a parallel query, if the lock is held by a worker process at transaction isolation level 3, or if the lock is held by a worker process in a parallel query and must be held for the duration of the transaction.
- “Ind pg” – indicates locks on index pages (allpages-locked tables only)
- “Inf key” – indicates an infinity key lock (for certain range queries at transaction isolation level 3 on data-only-locked tables)
- “Range” – indicates a range lock (for range queries at transaction isolation level 3 on data-only-locked tables)
These new values may appear in combination with “Fam dur” (which replaces “Sync pt duration”) and with each other, as applicable.
row – The column displays the row number for row-level locks.

See also kill, select in Reference Manual: Commands.

Permissions

Any user can execute sp_lock. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect