My fancy schmancy lock status query for ASE
source link: https://blogs.sap.com/2021/07/08/my-fancy-schmancy-lock-status-query-for-ase/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
My fancy schmancy lock status query for ASE
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 block 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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK