达梦定时任务更新阻塞信息到表

数据库阻塞，一些原因，查不到历史的阻塞信息。配置存储过程，把一些阻塞信息更新到表，然后配置定时任务，定时更新

 

 

--STEP1:创建辅助记录表：

CREATE TABLE TRX_WAIT(
"STATTIME" TIMESTAMP,
"SS" INTEGER,
"WT" VARCHAR2(30),
"WT_SESS_ID" BIGINT,
"WT_SQL_TEXT" VARCHAR(1000),
"WT_STATE" VARCHAR(10),
"WT_TRX_ID" BIGINT,
"WT_USER_NAME" VARCHAR(128),
"WT_CLNT_IP" VARCHAR(128),
"WT_APPNAME" VARCHAR(128),
"WT_LAST_SEND_TIME" DATETIME(6),
"FM" VARCHAR2(30),
"FM_SESS_ID" BIGINT ,
"FM_SQL_TEXT" VARCHAR(1000),
"FM_STATE" VARCHAR(10),
"FM_TRX_ID" BIGINT,
"FM_USER_NAME" VARCHAR(128),
"FM_CLNT_IP" VARCHAR(128),
"FM APPNAME" VARCHAR(128),
"FM LAST SEND TIME"DATETIME(6)
);

 

--STEP2:创建辅助存储过程GET_TX_WAIT

CREATE PROCEDURE GET_TX_WAIT AS
BEGIN
INSERT INTO TRX_WAIT
SELECT SYSDATE AS STATTIME,
DATEDIFF(SS,S1.LAST_SEND_TIME,SYSDATE) AS SS，
'被阻塞的信息' AS WT,
S1.SESS_ID AS WT_SESS_ID,
S1.SQL_TEXT AS WT_SQL_TEXT,
S1.STATE AS WT_STATE,
S1.TRX_ID AS WT_TRX_ID,
S1.USER_NAME AS WT_USER_NAME,
S1.CLNT_IP AS WT_CLNT_IP,
S1.APPNAME AS WT_APPNAME,
S1.LAST_SEND_TIME AS WT_LAST_SEND_TIME,
'引起阻塞的信息' AS FM,
S2.SESS_ID AS FM_SESS_ID,
S2.SQL_TEXT AS FM_SQL_TEXT,
S2.STATE AS FM_STATE,
S2.TRX_ID AS FM_TRX_ID,
S2.USER_NAME AS FM_USER_NAME,
S2.CLNT_IP AS FM_CLNT_IP,
S2.APPNAME AS FM_APPNAME,
S2.LAST_SEND_TIME AS FM_LAST_SEND_TIME
FROM V$SESSIONS S1，
V$SESSIONS S2 ,V$TRXWAIT W
WHERE S1.TRX_ID = W.ID
AND S2.TRX_ID = W.WAIT_FOR_ID;
COMMIT;
END;

 

----STEP3:创建作业定时10分钟搜一次，收集阻塞数据：

call SP_CREATE_JOB('GETTRX',1,0,'',0,0,'',0,'获取历史阻塞信息');

call SP_JOB_CONFIG_START('GETTRX');

call SP_ADD_JOB_STEP('GETTRX', 'GETTRX_SQL', 0, 'call GET_TX_WAIT;', 1, 1, 0, 0, NULL, 0);

call SP_ADD_JOB_SCHEDULE('GETTRX', 'GETTRX01', 1, 1, 1, 0, 10, '00:00:00', '23:59:59', '2025-10-13 17:48:00', NULL, '');

call SP_JOB_CONFIG_COMMIT('GETTRX');

 

--最后查表，就知道历史的阻塞信息了
select * from TRX_WAIT;

 

###测试：

CREATE TABLE "SYSDBA"."TEST1"
(
"NAME" VARCHAR(20),
"ID" INT NOT NULL,
NOT CLUSTER PRIMARY KEY("ID"));

打开两个查询窗口:

在两个窗口都输入插入数据语句：
insert into sysdba.test1 values('aaa',1);

一个窗口顺利执行：
第二个窗口无反应，发生阻塞：

第一个窗口里，插入的数据由于没有 commit 提交，事务没有结束，此时因为存在主键约束，第二个窗口查不到对应的数据，同样也无法插入，此时发生阻塞。

再打开个窗口执行

call GET_TX_WAIT;

再查询，就可以看到阻塞信息了
select * from TRX_WAIT;