Find Blocking Processes Using sp_who [Sybase]

If a user has executed an insert/update on a table, but not committed the transaction, other users will find that their queries on the same table hang. This is because the table has been locked and the first user's process is blocking everyone else's.

The following scenario illustrates this. User Bob updates a table but does not commit it. User Alice then tries to query the table, but here command hangs:

bob$ isql -S myServer -D myDatabase -U myUser -P myPass
1> begin transaction
2> update MY_TABLE set COL='C' where COL='A'
3> go
(1 row affected)

alice$ isql -S myServer -D myDatabase -U myUser -P myPass
1> select * from MY_TABLE where COL = 'A'
2> go -- Hangs

In this example, Alice's query to the table hangs because Bob has not committed his transaction. You can use the sp_who command in order to see which commands are blocked and who they are being blocked by.

alice$ isql -S myServer -D myDatabase -U myUser -P myPass
1> sp_who
2> go
fid spid status     loginame  origname  hostname                  blk_spid
        dbname      tempdbname cmd               block_xloid
--- ---- ---------- --------- --------- ------------------------- --------
         ----------- ---------- ----------------- -----------
  0   51 recv sleep myUser myUser MACHINE21302                     0
        myDatabase  tempdb     AWAITING COMMAND            0
  0  343 lock sleep myUser myUser MACHINE21501                    51
        myDatabase  tempdb     SELECT                      0

The output shows that spid 343 from MACHINE21501 is being blocked by spid 51 from MACHINE21302. You can use the command sp_lock 51 in order to find more information about the locking process.

You can either kill the blocking process (if you have DBA rights) using kill 51 or use the psloggedon utility to find out which user is logged onto MACHINE21302, so that you can tell them to commit their open transaction.