从连接数耗尽到 TX 行锁：一次 Oracle 11g RAC 故障排查记录

一次 Oracle 11g RAC 生产故障，最初表现为数据库连接数耗尽。

应用不断报连接异常，数据库 sessions 已接近上限。继续往下查，真正的问题并不是连接数配置太小，而是大量 JDBC 会话被事务锁长期卡住，没有及时返回连接池。

锁的源头又不是应用看到的那条 UPDATE PUB_WAREDICT_CARD 本身。顺着 SQL 往下追，最终在表上的行级触发器里找到了一条高频 DELETE。这条 DELETE 位于双重循环中，执行次数极高，而目标表接近 300 万行，却只有主键索引，导致每次执行都走全表扫描。

给这条 Trigger SQL 补上合适的组合索引后，执行计划从 TABLE ACCESS FULL 切换为索引访问，TX 等待开始快速下降，最终 enq: TX - row lock contention 清零。

这篇文章记录完整的排查过程。

1. 故障现象：数据库连接数已经接近上限
#

故障发生时，业务侧首先反馈接口大量超时，新连接建立失败。

数据库资源水位已经非常高：

sessions   ≈ 3071 / 3072
processes  ≈ 1700 / 2000

同时出现：

ORA-00018: maximum number of sessions exceeded

如果只看这个现象，很容易把处理方向放到：

processes
sessions
连接池大小

甚至直接考虑扩大参数。

但 sessions 用完只说明大量连接没有释放，并不能解释这些连接为什么一直占着数据库会话。

生产环境里遇到这种问题，我通常先查一件事：

这些 Session 当前到底在做什么？

2. 从 GV$SESSION 看等待事件
#

先按等待事件统计活动会话：

set lines 300 pages 200

select
    inst_id,
    event,
    count(*) cnt
from gv$session
where status = 'ACTIVE'
group by inst_id,event
order by cnt desc;

结果里最突出的两个等待是：

enq: TX - allocate ITL entry
enq: TX - row lock contention

其中大量 Session 在等：

enq: TX - row lock contention

问题方向到这里已经发生变化。

这不是“连接太多”的问题，而是：

事务没有结束
  ↓
行锁没有释放
  ↓
后续事务排队
  ↓
JDBC 调用长时间不返回
  ↓
Session 一直被占用
  ↓
最终把 sessions 推满

这里还有一个容易误判的地方。

GV$SESSION.STATUS='ACTIVE' 并不代表 Session 正在使用 CPU。

例如：

STATUS = ACTIVE
EVENT  = enq: TX - row lock contention

它只是当前有一个数据库调用没有结束，但这个调用正在等锁。

因此故障现场不能只统计 ACTIVE 数量，还要同时看：

EVENT
STATE
SQL_ID
BLOCKING_SESSION
FINAL_BLOCKING_SESSION
LAST_CALL_ET

3. 找 TX 阻塞链
#

确认是 TX 等待以后，下一步是找 holder 和 waiter。

现场使用了下面的查询：

set lines 300 pages 1000

col holder for a10
col waiter for a10
col holder_user for a15
col waiter_user for a15
col holder_event for a35
col waiter_event for a35
col holder_sql_id for a13
col waiter_sql_id for a13
col holder_sql_text for a70
col waiter_sql_text for a70

select
    h.inst_id || ',' || h.sid as holder,
    hs.serial#                 as holder_serial,
    hs.username                as holder_user,
    hs.status                  as holder_status,
    hs.sql_id                  as holder_sql_id,
    substr(hsql.sql_text,1,70) as holder_sql_text,
    hs.event                   as holder_event,

    w.inst_id || ',' || w.sid  as waiter,
    ws.serial#                 as waiter_serial,
    ws.username                as waiter_user,
    ws.status                  as waiter_status,
    ws.sql_id                  as waiter_sql_id,
    substr(wsql.sql_text,1,70) as waiter_sql_text,
    ws.event                   as waiter_event,

    w.ctime                    as wait_seconds,
    h.type,
    h.id1,
    h.id2
from gv$lock h
join gv$lock w
  on h.type = w.type
 and h.id1  = w.id1
 and h.id2  = w.id2
 and h.request = 0
 and w.request > 0
left join gv$session hs
  on h.inst_id = hs.inst_id
 and h.sid     = hs.sid
left join gv$session ws
  on w.inst_id = ws.inst_id
 and w.sid     = ws.sid
left join gv$sqlarea hsql
  on hs.inst_id = hsql.inst_id
 and hs.sql_id  = hsql.sql_id
left join gv$sqlarea wsql
  on ws.inst_id = wsql.inst_id
 and ws.sql_id  = wsql.sql_id
where h.block in (1,2)
  and h.type = 'TX'
order by w.ctime desc;

锁链中反复出现一条 PUB_WAREDICT_CARD 的 UPDATE：

SQL_ID = 9wa9m4cj782sa

SQL 类似：

update PUB_WAREDICT_CARD
set BIZID = '449XprgW3Qv',
    CHECKTYPE = '20',
    NEWENDDATE = to_date('2031-07-20','yyyy/mm/dd'),
    ABATEDAYS = '30',
    BEGINDATE = to_date('2021-07-22','yyyy/mm/dd'),
    CARDTYPE = '8',
    ENDDATE = to_date('2031-07-20','yyyy/mm/dd'),
    STOPFLAG = '00',
    modifyuser = 1749,
    sysoptdate = sysdate
where bizid = '449XprgW3Qv'
  and decode(iscommondata,'0',ownerid,19) = 19;

大量 Session 同时执行这类 UPDATE，并且等待：

enq: TX - row lock contention

这说明相同业务记录存在高并发更新。

但还有一个问题没有解释：

为什么一次普通 UPDATE 能把事务拖这么久？

4. SQL 本身还有大量 literal 拼接
#

进一步检查发现，这类 SQL 没有使用 bind variable。

例如同一个 SQL 模板，仅仅因为 BIZID、日期或者 OWNERID 不同，就生成不同的 SQL_ID：

95qu2bsxxcwch
7xy7ussnbttp4
9wa9m4cj782sa
f9dya82zffwu3
3kqkh3nr6r5k3
...

通过 FORCE_MATCHING_SIGNATURE 聚合后，当时能看到：

SQL_CNT       127
EXECUTIONS   1656
PARSE_CALLS  1845
LOADS         139

很多 SQL 的特征是：

EXECUTIONS ≈ PARSE_CALLS

例如：

EXECUTIONS 737
PARSE_CALLS 737

这说明应用层存在明显的 SQL 字符串拼接和频繁 parse。

它会增加 shared pool 和 cursor 管理开销，但这还不是这次 TX 锁积压的直接原因。

继续追 PUB_WAREDICT_CARD。

5. PUB_WAREDICT_CARD 上存在行级触发器
#

查询表上的 Trigger：

select
    owner,
    trigger_name,
    trigger_type,
    triggering_event,
    status
from dba_triggers
where table_name = 'PUB_WAREDICT_CARD'
order by owner,trigger_name;

结果只有一个：

OWNER          GKSDQX
TRIGGER_NAME   INS_UPD_PUB_WAREDICT_CARD_INF
TRIGGER_TYPE   BEFORE EACH ROW
EVENT          INSERT OR UPDATE
STATUS         ENABLED

也就是说，每更新一行 PUB_WAREDICT_CARD，都会进入这个 Trigger。

把源码展开以后，看到两个 Cursor。

第一个查询 goods owner：

CURSOR cur_goodsowner(
    an_compid pub_clients.compid%type,
    an_ownerid pub_waredict_card.ownerid%type
)
IS
SELECT DISTINCT g.goodsownerid, g.ownerid
FROM inf_goodsowner g
INNER JOIN pub_dept d
        ON g.ownerid = d.deptid
INNER JOIN pub_subcom_dept c
        ON c.subcomid = d.deptid
       AND c.compid = an_compid
INNER JOIN pub_dept e
        ON c.deptid = e.deptid
WHERE d.ownerid = an_ownerid
  AND d.deptstyle = '10'
  AND e.deptstyle = '40'
  AND e.wmstype = '20';

第二个查询商品明细：

CURSOR cur_dtl(an_goodid pub_waredict_hdr.id%type)
IS
SELECT aa.goods,tt.isdrug
FROM pub_waredict_hdr tt
INNER JOIN pub_waredict_dtl aa
        ON tt.id = aa.id
WHERE tt.id = an_goodid;

后面的处理逻辑是：

OPEN cur_goodsowner(...);

LOOP
    FETCH cur_goodsowner ...;

    OPEN cur_dtl(...);

    LOOP
        FETCH cur_dtl ...;

        DELETE inf_inpt_waredict_card ...;

        INSERT INTO inf_inpt_waredict_card ...;

    END LOOP;

    CLOSE cur_dtl;
END LOOP;

CLOSE cur_goodsowner;

这就是问题里的第一个放大器。

假设：

cur_goodsowner 返回 10 行
cur_dtl        返回 5 行

一次主表 UPDATE 就可能产生：

50 次 DELETE
50 次 INSERT

如果外层 SQL 一次更新多行，调用量还会继续乘上去。

N×M 是否符合业务要求，需要业务和开发确认。即使 N×M 本身正确，用双层 PL/SQL LOOP 逐条 DELETE、逐条 INSERT，也不适合这种并发量。

6. Trigger 里的 DELETE 正是锁链里出现过的 SQL
#

Trigger 中有这样一段：

DELETE inf_inpt_waredict_card
WHERE goodsownid   = ls_goods
  AND issendflag   = '00'
  AND goodsownerid = ls_goodsownerid
  AND compid       = :new.compid
  AND cardno       = :new.cardno
  AND cardid       = :new.id;

对应数据库中的：

SQL_ID = bpvzfddkbuzuj

完整 SQL：

DELETE INF_INPT_WAREDICT_CARD
WHERE GOODSOWNID   = :B5
  AND ISSENDFLAG   = '00'
  AND GOODSOWNERID = :B4
  AND COMPID       = :B3
  AND CARDNO       = :B2
  AND CARDID       = :B1;

这条 SQL 在前面的 TX 锁链里多次出现在 blocker Session 上。

到这里，应用 SQL 和 Trigger SQL 已经串起来了：

UPDATE PUB_WAREDICT_CARD
        ↓
BEFORE EACH ROW Trigger
        ↓
DELETE INF_INPT_WAREDICT_CARD
        ↓
INSERT INF_INPT_WAREDICT_CARD

接下来只需要确认这个 DELETE 执行得有多差。

7. INF_INPT_WAREDICT_CARD 接近 300 万行，却只有 ID 主键索引
#

目标表统计信息：

select
    num_rows,
    blocks,
    avg_row_len,
    last_analyzed
from dba_tables
where owner='GKSDQX'
  and table_name='INF_INPT_WAREDICT_CARD';

结果：

NUM_ROWS      2840965
BLOCKS          47837
AVG_ROW_LEN        113
LAST_ANALYZED  10-AUG-26

Segment 大约：

0.37 GB

再看索引：

select
    i.index_name,
    i.uniqueness,
    i.status,
    c.column_position,
    c.column_name
from dba_indexes i
join dba_ind_columns c
  on c.index_owner = i.owner
 and c.index_name  = i.index_name
where i.table_owner = 'GKSDQX'
  and i.table_name  = 'INF_INPT_WAREDICT_CARD'
order by i.index_name,c.column_position;

当时只有：

PK_INF_INPT_WAREDICT_CARD
    ID

而 DELETE 使用的是：

GOODSOWNID
ISSENDFLAG
GOODSOWNERID
COMPID
CARDNO
CARDID

没有 ID。

主键索引帮不上这条 DELETE。

8. 执行计划确认了全表扫描
#

查看真实 cursor：

select *
from table(
    dbms_xplan.display_cursor(
        'bpvzfddkbuzuj',
        null,
        'BASIC +PREDICATE +PEEKED_BINDS'
    )
);

计划非常直接：

-----------------------------------------------------
| Id  | Operation          | Name                   |
-----------------------------------------------------
|   0 | DELETE STATEMENT   |                        |
|   1 |  DELETE            | INF_INPT_WAREDICT_CARD |
|*  2 |   TABLE ACCESS FULL| INF_INPT_WAREDICT_CARD |
-----------------------------------------------------

所有 child cursor 的：

PLAN_HASH_VALUE = 2640027899

也就是说，这不是 bind peeking 导致某几个 child 偶尔选了坏计划，而是没有可用索引，所有 child 都只能全表扫描。

这条 SQL 的执行量更值得注意。

从 V$SQL 统计看，它累计执行已经达到千万级，而且平均每次实际只处理很少的数据。

也就是说，数据库反复做的是：

扫描一张 284 万行的表
        ↓
找到 0～1 条目标记录
        ↓
DELETE

然后 Trigger 下一轮循环继续做同样的事情。

9. 为什么全表扫描会放大 TX 锁
#

这里需要区分两个概念。

全表扫描本身不会凭空产生 enq: TX - row lock contention。

TX row lock 的直接原因仍然是多个事务修改了存在交集的记录，其中一个事务没有结束，后面的事务只能等待。

全表扫描起到的是放大作用。

外层事务已经取得 PUB_WAREDICT_CARD 上的行锁后，Oracle 进入 Trigger：

UPDATE PUB_WAREDICT_CARD
        ↓
取得主表行锁
        ↓
Trigger
        ↓
N×M 次 DELETE
        ↓
每次扫描 INF_INPT_WAREDICT_CARD
        ↓
N×M 次 INSERT
        ↓
Trigger 返回
        ↓
应用后续逻辑
        ↓
COMMIT

锁不是 Trigger 执行完一条 SQL 就释放，而是一直保持到事务 COMMIT 或 ROLLBACK。

所以 Trigger 每多消耗一秒，外层事务的持锁时间也会增加。

当相同业务数据存在高并发更新时，等待队列很快就会堆起来：

事务 A 持锁并执行 Trigger
        ↓
事务 B 等 A
事务 C 等 A
事务 D 等 A
...

应用线程不返回，JDBC Session 也不会释放。

最终看到的就是：

TX waiter 增加
sessions 增加
processes 增加
新连接失败

这也是为什么最初看到的“连接数满”不能通过简单扩大 sessions 来解释。

10. 增加组合索引并验证执行计划
#

确认 bpvzfddkbuzuj 对 INF_INPT_WAREDICT_CARD 执行全表扫描后，下一步就是根据 DELETE 条件设计索引。

这条 DELETE 的过滤条件是：

DELETE INF_INPT_WAREDICT_CARD
WHERE GOODSOWNID   = :B5
  AND ISSENDFLAG   = '00'
  AND GOODSOWNERID = :B4
  AND COMPID       = :B3
  AND CARDNO       = :B2
  AND CARDID       = :B1;

先查看这些字段的基数：

select
    column_name,
    num_distinct,
    density,
    num_nulls,
    last_analyzed
from dba_tab_col_statistics
where owner='GKSDQX'
  and table_name='INF_INPT_WAREDICT_CARD'
  and column_name in
(
    'CARDID',
    'COMPID',
    'CARDNO',
    'GOODSOWNERID',
    'GOODSOWNID',
    'ISSENDFLAG'
)
order by num_distinct desc;

统计结果如下：

字段	NUM_DISTINCT	说明
GOODSOWNID	88136	选择性最高
CARDNO	9239	选择性较高
CARDID	4545	选择性较高
GOODSOWNERID	10	选择性较低
ISSENDFLAG	3	选择性很低
COMPID	1	当前几乎没有过滤价值

最终选择：

GOODSOWNID
CARDID
CARDNO

作为组合索引列：

CREATE INDEX GKSDQX.IDX_IWC_TRG_DEL_01
ON GKSDQX.INF_INPT_WAREDICT_CARD
(
    GOODSOWNID,
    CARDID,
    CARDNO
)
ONLINE;

索引创建完成后，重新查看 SQL 的执行计划。

优化前后对比如下：

项目	优化前	优化后
SQL_ID	`bpvzfddkbuzuj`	`bpvzfddkbuzuj`
PLAN_HASH_VALUE	`2640027899`	`788897287`
访问方式	`TABLE ACCESS FULL`	`INDEX RANGE SCAN`
使用索引	无	`IDX_IWC_TRG_DEL_01`
索引访问列	无	`GOODSOWNID`、`CARDID`、`CARDNO`
剩余过滤列	全部依赖表扫描过滤	`ISSENDFLAG`、`GOODSOWNERID`、`COMPID`
目标表规模	约 284 万行	约 284 万行
单次 DELETE 定位方式	扫描大量数据块后过滤	通过索引直接定位少量候选记录

优化前执行计划：

-----------------------------------------------------
| Id  | Operation          | Name                   |
-----------------------------------------------------
|   0 | DELETE STATEMENT   |                        |
|   1 |  DELETE            | INF_INPT_WAREDICT_CARD |
|*  2 |   TABLE ACCESS FULL| INF_INPT_WAREDICT_CARD |
-----------------------------------------------------

优化后，Predicate 已经变成：

access(
    GOODSOWNID=:B5
    AND CARDID=:B1
    AND CARDNO=:B2
)

剩余条件：

filter(
    ISSENDFLAG='00'
    AND GOODSOWNERID=:B4
    AND COMPID=:B3
)

实际访问路径由：

TABLE ACCESS FULL

切换为：

INDEX RANGE SCAN IDX_IWC_TRG_DEL_01
        ↓
TABLE ACCESS BY INDEX ROWID
        ↓
DELETE

这个变化解决的是 Trigger 内部 DELETE 的访问路径问题。

原来每执行一次 DELETE，都需要扫描一张接近 300 万行的表；增加索引后，Oracle 可以先通过 GOODSOWNID + CARDID + CARDNO 快速定位记录，再对少量候选行检查 ISSENDFLAG、GOODSOWNERID 和 COMPID。

后续增量观察中，这条 SQL 的平均逻辑读从优化前约：

2400 buffer gets / execution

下降到约：

256 buffer gets / execution

说明这个索引确实起到了明显作用。

不过，这一步只解决了“每次 DELETE 太贵”的问题。

Trigger 每秒仍然会执行大量 DELETE，因此代码层面的双重循环和逐条 DML 仍然需要继续整改。

11. 优化效果与故障恢复
#

索引生效后，对 bpvzfddkbuzuj 做增量观察，同时继续监控 TX 等待。

项目	优化前 / 初始状态	优化后 / 最终状态
执行计划	`TABLE ACCESS FULL`	`INDEX RANGE SCAN`
PLAN_HASH_VALUE	`2640027899`	`788897287`
平均逻辑读	约 `2400 gets/exec`	约 `256 gets/exec`
单次 DELETE 处理行数	约 1 行	约 1 行
DELETE 执行频率	高	101 秒执行约 25.9 万次，约 2565 次/秒
TX 阻塞状态	大量 `enq: TX - row lock contention`	等待队列持续收缩
最后主要 TX	多级阻塞链	`ID1=34406419 / ID2=293`
最终 TX Waiter	大量等待	`0`
数据库状态	Session 大量堆积	恢复正常

增量采样结果：

11:00:26
EXECUTIONS      3527669
BUFFER_GETS   902929762
ROWS_PROCESSED 3523130

11:02:07
EXECUTIONS      3786731
BUFFER_GETS   969250900
ROWS_PROCESSED 3782186

101 秒内，这条 Trigger DELETE 执行约 25.9 万次，平均约 2565 次/秒。索引把单次 SQL 的逻辑读从约 2400 降到 256 左右，事务执行时间缩短后，TX 等待也开始快速消退。

最终检查：

select count(*) tx_waiters
from v$session
where username='GKSDQX'
  and event='enq: TX - row lock contention';

结果：

TX_WAITERS
----------
0

到这里，锁等待已经清空，数据库恢复正常。

不过每秒仍有两千多次 DELETE，说明索引解决的是单次 SQL 成本，Trigger 双重循环带来的高调用量仍需要后续从代码层面整改。

12. 故障链路与后续整改
#

回头看整个故障，数据库连接数耗尽只是最外层现象。

实际链路是：

应用高并发 UPDATE PUB_WAREDICT_CARD
        ↓
相同业务记录产生 TX 竞争
        ↓
BEFORE EACH ROW Trigger 被触发
        ↓
Trigger 双重循环
        ↓
大量 DELETE INF_INPT_WAREDICT_CARD
        ↓
目标表约 284 万行且没有匹配索引
        ↓
DELETE TABLE ACCESS FULL
        ↓
Trigger 执行时间拉长
        ↓
外层事务持锁时间增加
        ↓
enq: TX - row lock contention 持续累积
        ↓
JDBC Session 长时间不释放
        ↓
sessions 接近上限
        ↓
ORA-00018

这次通过给 INF_INPT_WAREDICT_CARD 增加组合索引，解决了 Trigger 内 DELETE 全表扫描的问题，锁等待也随之逐步消退。

但索引解决的只是“单次 DELETE 成本过高”，Trigger 本身仍然存在明显的放大效应：

FOR goodsowner
    FOR goods
        DELETE
        INSERT
    END LOOP
END LOOP

如果 N×M 的结果是业务必需的，建议把逐条 DML 改成集合操作：

1 次 DELETE
+
1 次 INSERT ... SELECT

另外，Trigger 在 UPDATE 场景下大量使用 :NEW 条件删除旧数据，也值得开发确认业务语义。若 CARDNO、COMPID、GOODID 等业务键发生变化，更合理的处理通常是：

根据 :OLD 清理旧映射
        ↓
根据 :NEW 生成新映射

后续整改重点可以放在三件事上：

相同业务键尽量避免高并发同时更新；
Trigger 双重循环改成集合 SQL；
应用层减少 literal SQL 拼接，改用绑定变量或 PreparedStatement。

这次故障的关键不是把 sessions 参数调大，而是找到为什么这些 Session 长时间无法结束，并继续追到真正拖长事务的 Trigger SQL。

1. 故障现象：数据库连接数已经接近上限#

2. 从 GV$SESSION 看等待事件#

3. 找 TX 阻塞链#

4. SQL 本身还有大量 literal 拼接#

5. PUB_WAREDICT_CARD 上存在行级触发器#

6. Trigger 里的 DELETE 正是锁链里出现过的 SQL#

7. INF_INPT_WAREDICT_CARD 接近 300 万行，却只有 ID 主键索引#

8. 执行计划确认了全表扫描#

9. 为什么全表扫描会放大 TX 锁#

10. 增加组合索引并验证执行计划#

11. 优化效果与故障恢复#

12. 故障链路与后续整改#