【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

posted @   YashanDB  阅读(2)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
点击右上角即可分享
微信分享提示