目录一、环境信息二、背景描述三、数据准备四、实验步骤0、参数确认1、开启多个会话2、追踪锁信息3、会话一调用存过4、会话二重建存过5、会话三查看事务等待关系6、会话四查看持有锁7、追踪锁信息输出信息一、环境信息名称值CPUX86操作系统CentOS Linux release 8.5.2111内存4G逻辑核数5DM版本9.1.0.26二、背景描述生产环境SQL SERVER 存过执行中对存过进行重建在存过中包含临时表时正在执行的会话因临时表统计信息变化等原因触发局部重编译导致报错提示2812 找不到存储过程 XXX。在不包含临时表的情况下存过立即编译完成有许多系统需从SQL SERVER迁移至达梦来看看达梦在存过不包含临时表时是什么样的现象。三、数据准备DROP TABLE IF EXISTS SUN; DROP TABLE IF EXISTS TEMP_T; CREATE TABLE SUN ( session_id INT ); INSERT INTO SUN SELECT LEVEL FROM DUAL CONNECT BY LEVEL 10000; COMMIT; CREATE TABLE TEMP_T ( session_id INT ) ; CREATE OR REPLACE PROCEDURE DM_SUN() AS v_sql VARCHAR(4000); BEGIN dbms_output.enable; v_sql : INSERT INTO TEMP_T SELECT * FROM SUN; execute immediate v_sql; v_sql : update TEMP_T set session_id session_id 1 where session_id % 9 0; execute immediate v_sql; DBMS_LOCK.SLEEP(20); v_sql : update TEMP_T set session_id session_id 2 where session_id % 7 0; execute immediate v_sql; COMMIT; dbms_output.put_line(OK); EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line(FAIL); RAISE; END; /四、实验步骤0、参数确认[rootlocalhost ~]# cat /opt/Dm9/Data/DAMENG/dm.ini |grep DDL_WAIT_TIME DDL_WAIT_TIME 10 #Maximum waiting time in seconds for DDLs这里是10s如果怕模拟不出现象可以适当拉长。1、开启多个会话SELECT SESS_ID, THRD_ID, A.USER_NAME, REPLACE(SUBSTR(CLNT_IP, 1, INSTR(CLNT_IP, :, -1) -1), ::FFFF:, ) AS CLNT_IP, A.CLNT_VER, A.APPNAME, DATEDIFF(SS, LAST_RECV_TIME, SYSDATE) AS SQL_USED_TIME, DBMS_LOB.SUBSTR(SF_GET_SESSION_SQL(SESS_ID)) AS SQL_TXT, PARSE_TIME, HARD_PARSE_TIME, LOGIC_READ_CNT, PHY_READ_CNT, RECYCLE_LOGIC_READ_CNT, RECYCLE_PHY_READ_CNT, IO_WAIT_TIME, MAX_MEM_USED MAX_MEM_USED_KB, EXEC_CPU, EXEC_TIME FROM V$SESSIONS A JOIN V$SQL_STAT B ON A.SESS_ID B.SESSID -- AND A.SQL_ID B.SQL_ID WHERE A.STATE ACTIVE ORDER BY SQL_USED_TIME DESC;两个会话对应的线程号分别是12431、12432。2、追踪锁信息[rootlocalhost ~]# strace -e tracefutex -tt -T -f -y -yy -p $(pidof dmserver) 21 | grep -E 12431|12432只看12431、12432线程的加锁信息这里会卡顿住是正常现象因为还没有执行SQL。3、会话一调用存过CALL DM_SUN();4、会话二重建存过CREATE OR REPLACE PROCEDURE DM_SUN() AS v_sql VARCHAR(4000); BEGIN dbms_output.enable; v_sql : INSERT INTO TEMP_T SELECT * FROM SUN; execute immediate v_sql; v_sql : update TEMP_T set session_id session_id 1 where session_id % 9 0; execute immediate v_sql; DBMS_LOCK.SLEEP(20); v_sql : update TEMP_T set session_id session_id 2 where session_id % 7 0; execute immediate v_sql; COMMIT; dbms_output.put_line(OK); EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line(FAIL); RAISE; END; /5、会话三查看事务等待关系WITH -- 1. 原始等待数据 trx AS ( SELECT ID AS waiter, WAIT_FOR_ID AS holder, WAIT_TIME, THRD_ID FROM V$TRXWAIT ), -- 2. 找出根事务不等待别人但正被他人等待 root AS ( SELECT DISTINCT holder AS root_id FROM trx WHERE holder NOT IN (SELECT waiter FROM trx) ), -- 3. 递归构建阻塞链 wait_chain (trx_id, parent_id, wait_time, thrd_id, lvl, path) AS ( -- 根节点没有父事务没有等待时间 SELECT r.root_id, NULL, NULL, NULL, 1, TO_CHAR(r.root_id) FROM root r UNION ALL -- 递归从当前等待者向下找它的子等待者 SELECT t.waiter, wc.trx_id, t.WAIT_TIME, t.THRD_ID, wc.lvl 1, wc.path || - || t.waiter FROM wait_chain wc JOIN trx t ON t.holder wc.trx_id ) -- 4. 去重关联会话信息只取每个事务的第一个会话 , sessions_dedup AS ( SELECT TRX_ID, SESS_ID, SQL_TEXT, ROW_NUMBER() OVER (PARTITION BY TRX_ID ORDER BY SESS_ID) AS rn FROM SYS.V$SESSIONS ) -- 5. 最终输出以树形缩进展示 SELECT wc.lvl, LPAD( , (wc.lvl - 1) * 3) || Trx || wc.trx_id AS blocking_tree, wc.trx_id, wc.wait_time, wc.thrd_id, s.SESS_ID, s.SQL_TEXT AS current_sql, wc.path AS chain_path, CASE WHEN wc.lvl 1 THEN SP_CANCEL_SESSION_OPERATION( || s.SESS_ID || ); || SP_CLOSE_SESSION( || s.SESS_ID || ); ELSE NULL END as KILL_SESS_SQL FROM wait_chain wc LEFT JOIN sessions_dedup s ON s.TRX_ID wc.trx_id AND s.rn 1 -- 确保每个事务只关联一条会话 ORDER BY wc.path; -- 用路径排序保证树结构顺序重建等待调用。6、会话四查看持有锁WITH -- 1. 原始等待数据 trx AS ( SELECT ID AS waiter, WAIT_FOR_ID AS holder, WAIT_TIME, THRD_ID FROM V$TRXWAIT ), -- 2. 找出根事务不等待别人但正被他人等待 root AS ( SELECT DISTINCT holder AS root_id FROM trx WHERE holder NOT IN (SELECT waiter FROM trx) ), -- 3. 递归构建阻塞链 wait_chain (trx_id, parent_id, wait_time, thrd_id, lvl, path) AS ( -- 根节点没有父事务没有等待时间 SELECT r.root_id, NULL, NULL, NULL, 1, TO_CHAR(r.root_id) FROM root r UNION ALL -- 递归从当前等待者向下找它的子等待者 SELECT t.waiter, wc.trx_id, t.WAIT_TIME, t.THRD_ID, wc.lvl 1, wc.path || - || t.waiter FROM wait_chain wc JOIN trx t ON t.holder wc.trx_id ) -- 4. 去重关联会话信息只取每个事务的第一个会话 , sessions_dedup AS ( SELECT TRX_ID, SESS_ID, SQL_TEXT, ROW_NUMBER() OVER (PARTITION BY TRX_ID ORDER BY SESS_ID) AS rn FROM SYS.V$SESSIONS ) SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, L.THRD_ID, -- 锁的创建者线程号 L.TRX_ID, -- 所属事务 ID L.LTYPE, -- 锁类型TID 锁、对象锁 L.LMODE, -- 锁模式S 锁、X 锁、IX 锁、IS 锁 L.BLOCKED, -- 是否处于上锁等待状态。0 表示已上锁成功1 表示处于上锁等待状态 L.TABLE_ID, -- 对于对象锁表示表对象或字典对象的 ID对于 TID 锁表示封锁记录对应的表 ID。-1 表示事务启动封锁自身的 TID L.ROW_IDX, -- TID 锁封锁记录行信息。-1 表示事务启动封锁自身的 TID L.TID -- TID 锁对象事务 ID FROM V$LOCK L INNER JOIN SYS.DBA_OBJECTS O ON L.TABLE_ID O.OBJECT_ID WHERE L.TRX_ID IN (SELECT wc.trx_id FROM wait_chain wc);线程12431拿到了存过的意向共享锁。线程12432想拿到存过的排他锁但没有获取到。意向共享锁和排他锁互斥所以等待是正常的。7、追踪锁信息输出信息[rootlocalhost ~]# strace -e tracefutex -tt -T -f -y -yy -p $(pidof dmserver) 21 | grep -E 12431|12432 [pid 12431] 17:36:25.250615 futex(0x7f49847c0ddc, FUTEX_WAKE_PRIVATE, 2147483647) 1 0.000060 [pid 12431] 17:36:25.250990 futex(0x7f498ad566b8, FUTEX_WAIT_PRIVATE, 0, {tv_sec0, tv_nsec99998720} unfinished ... [pid 12431] 17:36:25.254333 ... futex resumed) 0 0.003272 [pid 12431] 17:36:25.254498 futex(0x7f498ad56668, FUTEX_WAKE_PRIVATE, 1) 0 0.000165 [pid 12431] 17:36:28.963092 futex(0x7f497b1051dc, FUTEX_WAKE_PRIVATE, 1) 1 0.000152 [pid 12431] 17:36:28.964599 futex(0x7f497b1051dc, FUTEX_WAKE_PRIVATE, 1 unfinished ... [pid 12431] 17:36:28.964910 ... futex resumed) 1 0.000270 [pid 12432] 17:36:30.275137 futex(0x7f498b539a50, FUTEX_WAIT_PRIVATE, 0, {tv_sec0, tv_nsec29999521}) -1 ETIMEDOUT (连接超时) 0.030233 [pid 12432] 17:36:30.305559 futex(0x7f498b539a00, FUTEX_WAKE_PRIVATE, 1) 0 0.000018 [pid 12432] 17:36:30.306915 futex(0x7f498b539a50, FUTEX_WAIT_PRIVATE, 0, {tv_sec0, tv_nsec29994807}) -1 ETIMEDOUT (连接超时) 0.030473 [pid 12432] 17:36:30.337540 futex(0x7f498b539a00, FUTEX_WAKE_PRIVATE, 1) 0 0.000018 [pid 12432] 17:36:30.338908 futex(0x7f498b539a50, FUTEX_WAIT_PRIVATE, 0, {tv_sec0, tv_nsec29998961}) -1 ETIMEDOUT (连接超时) 0.030505 [pid 12432] 17:36:30.369579 futex(0x7f498b539a00, FUTEX_WAKE_PRIVATE, 1) 0 0.000021 [pid 12432] 17:36:30.371506 futex(0x7f498b539a50, FUTEX_WAIT_PRIVATE, 0, {tv_sec0, tv_nsec29998951}) -1 ETIMEDOUT (连接超时) 0.030530 [pid 12432] 17:36:30.402197 futex(0x7f498b539a00, FUTEX_WAKE_PRIVATE, 1) 0 0.000020 [pid 12432] 17:36:30.403516 futex(0x7f498b539a50, FUTEX_WAIT_PRIVATE, 0, {tv_sec0, tv_nsec29998891}) -1 ETIMEDOUT (连接超时) 0.030554 [pid 12432] 17:36:30.434237 futex(0x7f498b539a00, FUTEX_WAKE_PRIVATE, 1) 0 0.000019 [pid 12432] 17:36:30.435571 futex(0x7f498b539a50, FUTEX_WAIT_PRIVATE, 0, {tv_sec0, tv_nsec29998812}) -1 ETIMEDOUT (连接超时) 0.030566 [pid 12432] 17:36:30.466288 futex(0x7f498b539a00, FUTEX_WAKE_PRIVATE, 1) 0 0.000018 [pid 12432] 17:36:30.467634 futex(0x7f498b539a50, FUTEX_WAIT_PRIVATE, 0, {tv_sec0, tv_nsec29999182}) -1 ETIMEDOUT (连接超时) 0.030544 [pid 12432] 17:36:30.498389 futex(0x7f498b539a00, FUTEX_WAKE_PRIVATE, 1) 0 0.000022futex一种自旋锁互斥锁组合的实现在开始一段时间拿不到自旋锁自动变为尝试拿互斥锁减少CPU空转。12431线程拿到锁。12432线程差不多30ms内拿不到锁被唤醒再次尝试加锁。