Technical Articles
My fancy schmancy lock status query for ASE showing blocked lock requests
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
Be the first to leave a comment
You must be Logged on to comment or reply to a post.