达梦数据库的锁排查方法

关键字

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);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135

常用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';
 
1
2
3
4
5

问题总结

dm数据库的锁排查方法

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