-- 检查行锁冲突

WITH lockwait AS

(SELECT sid as request_sid, request as request_lock, id1 as xid FROM gv$lock WHERE request = 'ROW')

SELECT l.request_sid, l.request_lock, t.sid as hold_sid FROM lockwait l, gv$transaction t

WHERE l.xid = t.xid;

-- 检查表锁冲突 - 共享锁等独占锁

WITH lockwait AS

(SELECT sid as request_sid, request as request_lock, id1 as tid FROM gv$lock WHERE request = 'TS'),

lockhold AS

(SELECT DISTINCT gl.sid as hold_sid, gl.id1 as tid FROM gv$lock gl, lockwait l WHERE gl.id1 = l.tid AND lmode = 'TX')

SELECT w.request_sid, w.request_lock, o.owner||'.'||o.object_name as table_name, h.hold_sid FROM lockwait w, lockhold h, dba_objects o

WHERE w.tid = h.tid AND w.tid = o.object_id;

-- 检查表锁冲突 - 独占锁等共享锁

WITH lockwait AS

(SELECT sid as request_sid, request as request_lock, id1 as tid FROM gv$lock WHERE request = 'TX'),

lockhold AS

(SELECT gl.id1 as tid, WM_CONCAT(DISTINCT sid) as hold_sid_list FROM gv$lock gl, lockwait l WHERE gl.id1 = l.tid AND gl.lmode = 'TS' GROUP BY gl.id1)

SELECT w.request_sid, w.request_lock, o.owner||'.'||o.object_name as table_name, h.hold_sid_list FROM lockwait w, lockhold h, dba_objects o

WHERE w.tid = h.tid AND w.tid = o.object_id;

ITPub博客

【YashanDB 知识库】锁冲突检查