哪位大佬分享一个锁等待和死锁的排查过程嘛

ygb · 2025 年6 月 27 日 10:44

兰葩奇 · 2025 年6 月 27 日 10:48

需求同上，学习学习

淇铭 · 2025 年6 月 27 日 11:08

select
/*+QUERY_TIMEOUT(3600000000) read_consistency(weak) */
b.TRANS_ID,
case
WHEN b.TYPE = ‘TM’ THEN ‘表锁’
WHEN b.TYPE = ‘TX’ THEN ‘事务锁’
WHEN b.TYPE = ‘TR’ THEN ‘行锁’
ELSE NULL
END
AS lock_type,
ID1,
LMODE,
CASE
WHEN BLOCK = 0 THEN ‘该事务持有锁’
WHEN BLOCK = 1 THEN ‘该事务被阻塞’
ELSE NULL
END
as BLOCK,
a.SVR_IP,
ID,
user,
command,
time,
total_time,
STATE,
info,
retry_cnt,
retry_info,
thread_id,
trace_id
from
gv$ob_processlist a,
GV$OB_LOCKS b,
oceanbase.CDB_ob_table_locations c
where
a.trans_id = b.trans_id
and c.table_name = ‘表名称’
and (
ID1 like concat(’%’ , c.table_id, ‘%’)
OR ID1 like concat(’%’ , c.tablet_id ,’%’)
);
死锁可以查这个表