【YashanDB知识库】锁冲突检查

本文内容来自YashanDB官网，原文内容请见 https://www.yashandb.com/newsinfo/7253740.html?templateId=1718516

应用并发操作时，可能发生锁冲突。

常见的有行锁冲突、表锁冲突，比如更新同一条记录会出现行锁等待。

可以使用下面语句检查当前数据库是否正在发生锁冲突，如果有的话，可以列出哪些应用在申请锁、申请什么样的锁，以及申请的锁被哪些应用持有。

-- 检查行锁冲突

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;

chkLockwait.sql