My fancy schmancy lock status query for ASE showing blocked lock requests

[sladebe]

Originally posted as an answer in https://answers.sap.com/questions/13420793/locking-on-tablestable-lock.html

This is my fancy schmancy query to show lock statuses, including blocked lock requests.  Note that blocked lock requests aren't shown by sp_lock, although sp_who will show the process requesting the lock as blocked:

set nocount on

select p.spid, p.status, p.hostname, hostpid=p.hostprocess, p.program_name, p.cmd, BlockedOnSpid=p.blocked,

       object=l.DBName+".."+object_name(l.ObjectID,l.DBID),

       l.LockID, l.LockState, l.LockType, l.LockLevel, WaitSecs=l.WaitTime, l.BlockedState, BlockedByLockID=l.BlockedBy, cnt=count(*)

into #tmp_cnt

from master..sysprocesses p, master..monLocks l

where p.spid=l.SPID and p.spid!=@@spid

group by p.spid, p.status, p.hostname, p.hostprocess, p.program_name, p.cmd, p.blocked,

         l.DBName+".."+object_name(l.ObjectID,l.DBID),

         l.LockID, l.LockState, l.LockType, l.LockLevel, l.WaitTime, l.BlockedState, l.BlockedBy





exec sp_autoformat @fulltabname = "#tmp_cnt",

                   @selectlist  = "spid, status, hostname, hostpid, program_name, cmd, BlockedOnSpid, object,LockID, LockState, LockType, LockLevel, WaitSecs, BlockedState, BlockedByLockID, cnt",

                   @orderby     = "order by spid, status, hostname, hostpid, program_name, cmd, BlockedOnSpid, object, LockID, LockState, LockType, LockLevel"

drop table #tmp_cnt

It produces output like (santized for privacy reasons):

spid status     hostname hostpid program_name   cmd               BlockedOnSpid object                LockID LockState  LockType         LockLevel WaitSecs BlockedState BlockedByLockID cnt

---- ---------- -------- ------- -------------- ----------------- ------------- --------------------- ------ ---------  ---------------- --------- -------- ------------ --------------- ---

   93 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           186  Granted   exclusive intent TABLE         NULL NULL                    NULL   1

   93 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           186  Granted   exclusive page   PAGE          NULL NULL                    NULL   2

   93 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           186  Granted   update page      PAGE          NULL NULL                    NULL   1

   93 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           186  Requested exclusive page   PAGE             0 Blocked                 1230   1

  225 running    myhost1  33639   mycleanup.pl   EXECUTE IMMEDIATE             0 MyDB2..Aaaa             450  Granted   shared page      PAGE          NULL NULL                    NULL   1

  225 running    myhost1  33639   mycleanup.pl   EXECUTE IMMEDIATE             0 MyDB3..Mytbl2           450  Granted   exclusive intent TABLE         NULL NULL                    NULL   1

  225 running    myhost1  33639   mycleanup.pl   EXECUTE IMMEDIATE             0 MyDB3..Mytbl2           450  Granted   exclusive page   PAGE          NULL NULL                    NULL   1

  225 running    myhost1  33639   mycleanup.pl   EXECUTE IMMEDIATE             0 MyDB3..Bbbb             450  Granted   exclusive intent TABLE         NULL NULL                    NULL   1

  335 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           670  Granted   update page      PAGE          NULL NULL                    NULL   1

  335 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           670  Requested exclusive page   PAGE             0 Blocked                 1230   1

  407 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           814  Requested exclusive page   PAGE             0 Blocked                 1230   1

  498 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           996  Granted   update page      PAGE          NULL NULL                    NULL   1

  498 lock sleep myhost2  4420    MyBusyProgram  UPDATE                      615 MyDB1..MyTbl1           996  Requested exclusive page   PAGE             0 Blocked                 1230   1

  543 send sleep myhost1  1344    MyOtherProgram SELECT                        0 MyDB4..Hhhhh            1086 Granted   shared intent    TABLE         NULL NULL                    NULL   1

  543 send sleep myhost1  1344    MyOtherProgram SELECT                        0 MyDB4..Iiiii            1086 Granted   shared page      PAGE          NULL NULL                    NULL   1

  650 running    myhost1  23362   sqsh-2.4       SELECT                        0 MyDB5..ZZ               1300 Granted   shared intent    TABLE         NULL NULL                    NULL   1

  650 running    myhost1  23362   sqsh-2.4       SELECT                        0 MyDB4..GGGG             1300 Granted   shared page      PAGE          NULL NULL                    NULL   1