达梦数据库的锁排查方法

关键字

dm lock

问题描述

dm 数据库上锁问题如何排查

解决问题思路

准备数据
create table lock_test(name varchar(10),age varchar(10));

insert into lock_test values('ff','10');
insert into lock_test values('yy','20');
insert into lock_test values('ll','30');


达梦会话默认开启事务：
session1：
update lock_test set age = 'session1' where name = 'ff';

session2：
update lock_test set age = 'session2' where name = 'ff';

session2 因为更新同一行数据肯定 pending



--执行时间超 2s，可以自定义该时间
SELECT
	*
FROM
	(
	SELECT
		sess_id ,
		sql_text ,
		datediff (ss,
		last_recv_time,
		SYSDATE) Y_EXETIME,
		SF_GET_SESSION_SQL (SESS_ID) fullsql ,
		clnt_ip
	FROM
		V$SESSIONS
	WHERE
		STATE = 'ACTIVE'
        )
WHERE
	Y_EXETIME >= 2;



-- 查看锁
SELECT * FROM v$lock a WHERE blocked = 1;

|ADDR               |TRX_ID|LTYPE|LMODE|BLOCKED|TABLE_ID|ROW_IDX|TID   |IGN_FLAG|HLCK_EP|
|-------------------|------|-----|-----|-------|--------|-------|------|--------|-------|
|139,999,700,846,976|19,088|TID  |X    |1      |1,078   |1      |19,087|0       |255    |


19087 阻塞了 19088

-- 查看事务等待 
SELECT * FROM  v$trxwait;

|ID    |WAIT_FOR_ID|WAIT_TIME|THRD_ID|
|------|-----------|---------|-------|
|19,088|19,087     |328,412  |5,290  |



-- 查看阻塞
SELECT
        DS.SESS_ID "被阻塞的会话ID",
        DS.SQL_TEXT "被阻塞的SQL",
        DS.TRX_ID "被阻塞的事务ID",
        (CASE L.LTYPE WHEN 'OBJECT' THEN '对象锁' WHEN 'TID' THEN '事务锁' END CASE ) "被阻塞的锁类型",
        DS.CREATE_TIME "开始阻塞时间",
        SS.SESS_ID "占用锁的会话ID",
        SS.SQL_TEXT "占用锁的SQL",
        SS.CLNT_IP "占用锁的IP",
        L.TID "占用锁的事务ID"
FROM
        V$LOCK L
LEFT JOIN V$SESSIONS DS
ON
        DS.TRX_ID = L.TRX_ID
LEFT JOIN V$SESSIONS SS
ON
        SS.TRX_ID = L.TID
WHERE
        L.BLOCKED = 1 ;
        
		
|被阻塞的会话ID           |被阻塞的SQL                           |被阻塞的事务ID|被阻塞的锁类型|开始阻塞时间                 |占用锁的会话ID   |占用锁的SQL                      |占用锁的IP          |占用锁的事务ID|
|-------------------|--------------------------------------------------------|--------|-------|-----------------------|-----------|--------------------------------------------------------|----------------|--------|
|139,997,613,203,584|update lock_test set age = 'session2' where name = 'ff';|19,088  |事务锁    |2023-09-21 16:28:15.000|114,680,288|update lock_test set age = 'session1' where name = 'ff';|::ffff:127.0.0.1|19,087  |


会话 114680288 阻塞了 139997613203584


-- 杀掉活跃session
SP_CLOSE_SESSION(114680288);





模拟二
session1：
update lock_test set age = 'session1' where name = 'ff';

session2：
alter table lock_test add column name1 varchar(100);



SELECT
        DS.SESS_ID "被阻塞的会话ID",
        DS.SQL_TEXT "被阻塞的SQL",
        DS.TRX_ID "被阻塞的事务ID",
        DS.CREATE_TIME "开始阻塞时间",
        SS.SESS_ID "占用锁的会话ID",
        SS.SQL_TEXT "占用锁的SQL",
        SS.CLNT_IP "占用锁的IP",
        L.wait_for_id "占用锁的事务ID"
FROM
        v$trxwait L
LEFT JOIN V$SESSIONS DS
ON
        DS.TRX_ID = L.ID
LEFT JOIN V$SESSIONS SS
ON
        SS.TRX_ID = L.wait_for_id;
		
		
|被阻塞的会话ID      |被阻塞的SQL                                           |被阻塞的事务ID|开始阻塞时间      |占用锁的会话ID   |占用锁的SQL                                |占用锁的IP          |占用锁的事务ID|
|-------------------|----------------------------------------------------|--------|-----------------------|-----------|--------------------------------------------------------|----------------|--------|
|140,262,290,630,480|alter table lock_test add column name1 varchar(100);|22,094  |2023-09-21 16:54:40.000|117,445,568|update lock_test set age = 'session1' where name = 'ff';|::ffff:127.0.0.1|22,092  |
		
		
-- 杀掉活跃session
SP_CLOSE_SESSION(117445568);

常用SQL

--  查询实例中已执行未提交的 SQL
SELECT t1.sess_id,t1.sql_text, t1.state, t1.trx_id
 FROM v$sessions t1, v$trx t2
 WHERE t1.trx_id = t2.id AND t1.state = 'IDLE' AND t2.status = 'ACTIVE';
 

问题总结

dm数据库的锁排查方法

https://www.modb.pro/db/583100