Getting information about blocked processes

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:

1> sp_who "15"
fid    spid    status      loginame   origname   hostname      blk_spid
dbname     cmd                   block_xloid
-----  ------  ----------  ---------  ---------  ----------    --------
------     ----------------      -----------
0      15      recv sleep  sa         sa         PSALDINGXP    0
pubs2      AWAITING COMMAND      0

If you run sp_who against spid 16:

sp_who "16"
fid    spid    status      loginame   origname   hostname      blk_spid
dbname     cmd                   block_xloid
-----  ------  ----------  ---------  ---------  ----------    --------
------     ----------------      -----------
0      16      lock sleep  sa         sa         PSALDINGXP    15
pubs2      SELECT                 0

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:

1> sp_lock 15
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

NoteThe 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.”