达梦数据库阻塞与死锁查询

一、数据库阻塞

 1.查询被阻塞的信息和引起阻塞的信息

SELECT

        SYSDATE STATTIME,

        DATEDIFF(SS, S1.LAST_SEND_TIME, SYSDATE) SS ,

        '被阻塞的信息' WT , 

        S1.SESS_ID WT_SESS_ID,

        S1.SQL_TEXT WT_SQL_TEXT,

        S1.STATE WT_STATE,

        S1.TRX_ID WT_TRX_ID,

        S1.USER_NAME WT_USER_NAME,

        S1.CLNT_IP WT_CLNT_IP,

        S1.APPNAME WT_APPNAME,

        S1.LAST_SEND_TIME WT_LAST_SEND_TIME,

        '引起阻塞的信息' EM,

        s2.SESS_ID EM_SESS_ID,

        S2.SQL_TEXT EM_SQL_TEXT,

        S2.STATE FM_STATE,

        S2.TRX_ID FM_TRX_ID,

        S2.USER_NAME FM_USER_NAME,

        S2.CLNT_IP FM_CLNT_IP,

        S2.APPNAME FM_APPNAME,

        S2.LAST_SEND_TIME 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;

2. 杀掉会话

--SP_CLOSE_SESSION关闭等待事务（SESS_ID）

SP_CLOSE_SESSION(1883889960);

二、查询死锁历史记录

 select * from V$DEADLOCK_HISTORY;

V$DEADLOCK_HISTORY视图中记录了数据库死锁相关的信息