【YashanDB 知识库】锁冲突检查

2025-01-21
广东
本文字数：784 字
阅读完需：约 3 分钟

本文内容来自 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;