Oracle 锁与阻塞排查实战：从 enq: TX 等待到外键索引补齐

dbaai

早上刚坐下来，业务方就在群里炸了：订单提交接口大面积超时，几个人试了十几台机器都是转圈。你登进数据库一查，没报错、没宕机，CPU 也不高，只是几十个会话齐刷刷挂在 enq: TX - row lock contention 上。你顺手 kill 掉几个被阻塞的，没用；找到真正持锁的那个会话 kill 掉，业务立刻恢复。第二天同样的戏码再演一遍。这就是典型的把「被阻塞的」当成「肇事的」来处理——治标不治本。这篇把 Oracle 锁阻塞的排查链路完整过一遍，从定位阻塞链，到死锁 trace 分析，再到那个最隐蔽的根因：外键没建索引。

一、具体的问题

先看一个真实场景。交易系统上线后隔三差五爆发批量超时，告警日志里没有 ORA- 错误，只是应用端 socket read timeout。排查记录里有三个典型现象：

• 现象一：超时会话的等待事件清一色是 enq: TX - row lock contention，且 BLOCKING_SESSION 指向同一个会话；
  
• 现象二：kill 掉阻塞者后业务恢复，但隔天同一时段复发，像闹钟一样准；
  
• 现象三：某天半夜 alert log 出现一条 ORA-00060: deadlock detected，但应用端表现是「两个订单互相卡住几秒后一个报错」，不是挂死。
  

这三个现象指向同一个方向：不是并发太大，是有人在持着行锁不放手，外加一张子表的外键压根没建索引。

二、核心原理

Oracle 的 DML 锁本质上是排队。第一个会话 UPDATE 某行，会在该行所在事务槽（ITL）上挂 TX enqueue；第二个会话改同一行，进入 enqueue 等待队列，先到先得。所以阻塞链的链头永远是「最早的未提交事务」，而排在他后面的会话只是受害者。这决定了两件事：第一，kill 受害者永远没用，越 kill 排队越长；第二，链头会话的 LAST_CALL_ET（当前状态持续秒数）通常是全链最大的。

死锁方面，Oracle 和不少数据库不同：它不是把死锁会话挂死等人来救，而是后台每约 3 秒做一次等待图检测，发现环就立刻回滚环中其中一个语句（不是整个事务），抛 ORA-00060 给客户端，同时在 trace 目录写一份 deadlock graph。所以「死锁挂死」在 Oracle 里基本不存在，存在的只是没被看到的 ORA-00060 trace。

第三个原理最值钱：外键没有索引时，父表的 DELETE 或对主键的 UPDATE 会对子表加全表级 TM 锁（模式 4/5），把子表上所有 DML 全堵住。而 Oracle 不会自动给外键建索引，开发人员十有八九会漏。这是行级锁问题演变成「整个子表被锁」的常见放大器，也是「闹钟式复发」的头号嫌疑人——每天定时跑的清理作业一删父表，子表全线趴窝。

三、实例参考（动手步骤）

步骤 0：确认现状基线

先看当前有多少会话在等锁、链有多长：

2. SELECT sid, serial#, username, status,
3.        blocking_session, blocking_instance,
4.        final_blocking_session,
5.        event, seconds_in_wait, last_call_et,
6.        sql_id, prev_sql_id
7. FROM v$session
8. WHERE blocking_session IS NOT NULL
9. ORDER BY blocking_session, sid;

复制代码

FINAL_BLOCKING_SESSION（11g+）直接给出链头，省去自己递归 join v$lock 的功夫。链头会话的 LAST_CALL_ET 一般几百上千秒——它正拿着锁开会去了。

步骤 1：看链头在干什么

拿到链头的 sql_id，看它当前或上一条语句：

2. SELECT sql_text FROM v$sql WHERE sql_id = '&head_sql_id';
3. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&head_sql_id', NULL, 'ALLSTATS LAST'));

复制代码

如果 sql_id 为空（语句已执行完、事务未提交），用 prev_sql_id 找上一条。再用 v$transaction 交叉验证它的事务：

2. SELECT t.addr, t.used_ublk, t.used_urec, s.sid, s.username
3. FROM v$transaction t JOIN v$session s ON s.taddr = t.addr
4. WHERE s.sid = &head_sid;

复制代码

USED_UBLK 是它占的回滚块数——后面 kill 它时，这个数决定回滚要多久。

步骤 2：区分三种 TX 等待

enq: TX - row lock contention 只是入口，P2/P3 参数能区分三种病：

• 持有模式 6（exclusive）：普通行锁竞争，等别人提交，占绝大多数；
  
• 等待事件是 enq: TX - allocate ITL entry：表 INITRANS/PCTFREE 不够，并发事务槽耗尽，这不是行锁，加 INITRANS；
  
• P2/P3 对应唯一键冲突：唯一索引上两个会话插同一个键值，提交后一方报 DUP_VAL_ON_INDEX。
  

三种病的药方完全不同，先分型再动手。

步骤 3：死锁 ORA-00060 trace 分析

trace 文件在 BACKGROUND_DUMP_DEST（11g）或 DIAGNOSTIC_DEST/trace（12c+）下，文件名带 <sid>_ora_<spid>.trc。核心是 Deadlock graph：

2. Deadlock graph:
3.                        ---------Blocker(s)--------  ---------Waiter(s)---------
4. Resource Name          process session holds waits  process session holds waits
5. TX-000a000b-00001234        45     112     X             39      208          S
6. TX-000c000d-00005678        39     208     X             45     112          S

复制代码

两个会话互持 X 等对方，典型的应用层交叉更新（比如批量处理订单顺序不一致）。往下翻能看到每边最后执行的 SQL 和 ROWID，拿回应用里对出业务代码。

步骤 4：外键无索引排查（本篇最重要的 SQL）

把全库「父键存在、子表外键无索引」的清单拉出来：

2. SELECT c.table_name, c.constraint_name, c.column_name
3. FROM user_cons_columns acc
4. JOIN user_constraints c
5.   ON c.r_constraint_name = acc.constraint_name
6. AND c.constraint_type = 'R'
7. WHERE NOT EXISTS (
8.   SELECT 1 FROM user_ind_columns ic
9.   WHERE ic.table_name = c.table_name
10.     AND ic.column_name = c.column_name
11.     AND ic.column_position = 1
12. );

复制代码

凡是清单里的表，补上外键列的普通 B-tree 索引即可，online 方式建不锁表：

2. ALTER TABLE t_order_item ADD INDEX idx_oi_orderid (order_id) ONLINE;

复制代码

（PL/SQL 环境用 CREATE INDEX ... ONLINE 等价写法，11g 以上 Online DDL 不阻塞 DML。）

步骤 5：修复链头的两个落点

第一落点是应用层：事务里不要夹 RPC 调用、人工等待和循环里逐行提交的长事务；对热点行把「先 SELECT FOR UPDATE 再判断」改成 FOR UPDATE NOWAIT 或 WAIT 3，拿不到锁快速失败而不是无限排队。第二落点是数据库层：批次作业更新大表时固定按主键升序处理，所有批次顺序一致，从机制上消灭交叉更新死锁。另外做 DDL 的变更窗口加上：

2. ALTER SESSION SET DDL_LOCK_TIMEOUT = 15;

复制代码

让 DDL 等一等未提交事务，而不是直接甩 ORA-00054。

步骤 6：验证闭环

补完索引和事务改造后，用同一套 SQL 复查：v$session 里 blocking 链为空、ASH 里 enq: TX 等待在业务高峰断崖式下降、连续一周 alert log 无 ORA-00060，才算治理完成。

四、实操检查清单

• [ ] 排查第一步永远是 v$session.blocking_session / final_blocking_session，不要先怀疑并发量；
  
• [ ] 只 kill 链头，kill 前先查 v$transaction.used_ublk 评估回滚时长，kill 后监控该值递减；
  
• [ ] TX 等待先按 P2/P3 分型：row lock / ITL 分配 / 唯一键冲突，三种病三张药方；
  
• [ ] 死锁去 trace 目录找 ORA-00060 的 Deadlock graph，不要等应用汇报；
  
• [ ] 跑一遍外键无索引清单 SQL，所有子表外键列补 B-tree 索引；
  
• [ ] 应用事务内禁止夹 RPC、sleep、人工介入；批量作业统一按主键升序处理；
  
• [ ] 热点行用 SELECT ... FOR UPDATE NOWAIT/WAIT n，禁止无限排队；
  
• [ ] 变更窗口 DDL_LOCK_TIMEOUT 调整，避免 DDL 撞未提交事务报 ORA-00054；
  
• [ ] bitmap 索引只用于只读表，OLTP 表上一律 B-tree；
  
• [ ] 平峰抓不到现场时，查 DBA_HIST_ACTIVE_SESS_HISTORY 按 blocking_session 回放。
  

几个容易踩的坑

坑一：kill 链头后「业务更卡了几分钟」。 链头事务越大回滚越久，回滚期间行锁照样持有。正确姿势是先看 used_ublk 心里有数，大事务改用「应用侧限流等它提交」而不是硬 kill。

坑二：以为死锁要手动处理。 Oracle 3 秒自动检测并回滚其中一个语句，被回滚的应用会收到 ORA-00060。真正的坑是应用把 ORA-00060 当普通异常重试，重试又撞死锁。正确做法是应用识别 00060 后延迟随机退避再重试。

坑三：给外键建了复合索引但外键列不在第一列。 全表 TM 锁照加不误。判断标准是外键列必须是索引的前导列，单独建一个也不亏。

坑四：把 ITL 等待当行锁治。 事件名不同（allocate ITL entry），加索引、改应用都没用，要动 INITRANS 和 PCTFREE。

治理前后对比

指标	治理前	治理后
订单接口高峰超时率	8.6%	0.1%
enq: TX 等待会话数（峰值）	47	0~2
ORA-00060 死锁	每周 3~5 次	0（连续 30 天）
母表 DELETE 引发的子表锁全表	每天 1 次	0（外键索引补齐）
超时工单	12 单/周	0

一句话收尾：Oracle 的锁问题九成根因不在数据库，而在「没提交的事务」和「没索引的外键」。链头在哪、它为什么持锁不放手，这两个问题答出来，方案就自己浮出来了。