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:

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

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