sp_who reports
on system processes. If a user’s command is being blocked by
locks held by another task or worker process, the status column shows “lock
sleep” to indicate that this task or worker process is
waiting for an existing lock to be released.
The blk_spid or block_xloid column
shows the process ID of the task or transaction holding the lock
or locks.
You can add a user name parameter to get sp_who information about a particular Adaptive Server user. If you do not provide a user name, sp_who reports on all processes in Adaptive Server.
For example, consider what happens if you run three sessions in the pubs2 database: session one deletes the authors table, session two selects all the data from the authors table, and the third session running sp_who against spid 15. In this situation, session two hangs, and session three reports this in the sp_who output:
sp_who '15'
fid spid status loginame origname hostname blk_spid dbname tempdbname cmd block_xloid threadpool --- ---- --------- --------- --------- --------------- -------- ----------- ----------- ----------------- ----------- ----------------- 0 15 recv sleep sa sa PSALDINGXP 0 pubs2 tempdb AWAITING COMMAND 0 syb_default_pool
If you run sp_who against spid 16:
sp_who '16'
fid spid status loginame origname hostname blk_spid dbname tempdbname cmd block_xloid threadpool --- ---- ---------- --------- --------- --------------- -------- ----------- ----------- ----------------- ----------- ----------------- 0 16 lock sleep sa sa PSALDINGXP 15 pubs2 tempdb SELECT 0 syb_default_pool
If you run sp_lock against spid
15, the class column displays
the cursor name for locks associated with the current user’s
cursor and the cursor ID for other users:
fid spid loid locktype table_id page row dbname class context ----- ----- ---------- -------------------------- ---------- ---------- ----- --------------- --------------------- --------------- 0 15 30 Ex_intent 576002052 0 0 pubs2 Non Cursor Lock 0 15 30 Ex_page-blk 576002052 1008 0 pubs2 Non Cursor Lock 0 15 30 Ex_page 576002052 1040 0 pubs2 Non Cursor Lock Ind pg
If you run sp_lock against spid
16, the class column displays
the cursor name for locks associated with the current user’s
cursor and the cursor ID for other users:
fid spid loid locktype table_id page row dbname class context ------ ----- ---------- ----------------------- --------------- ------ ----------- ------------- ------------------- 0 16 32 Sh_intent 576002052 0 0 pubs2 Non Cursor Lock
The sample output for sp_lock and sp_familylock in
this chapter omits the class column to increase
readability. The class column
reports either the names of cursors that hold locks or “Non
Cursor Lock.”
