如何分析enq: TM - contention_外键未建索引导致的表级锁阻塞-Oracle-PHP中文网

外键未建索引会导致主表dml时全表扫描子表校验引用，触发整表tm锁争用；通过ash查p2得子表object_id，再结合dba_constraints与dba_ind_columns定位缺失索引的外键。

怎么确认是外键没建索引引发的 enq: tm - contention

直接查 v$active_session_history 或历史 ash 视图，过滤出等待事件为 enq: tm - contention 的会话，重点看 p2 字段值——它就是被阻塞对象的 object_id。再用 dba_objects 反查这个 id 对应哪张表，大概率会发现：阻塞源头不是你正在操作的那张主表，而是它的某个子表（比如你 update tab1，但 p2 指向的是 tab2）。

接着验证外键关系：SELECT * FROM dba_constraints WHERE constraint_type = 'R' AND r_constraint_name IN (SELECT constraint_name FROM dba_constraints WHERE table_name = 'TAB1' AND constraint_type = 'P')，找出所有引用 TAB1 主键的子表约束；再检查这些子表的外键列上有没有索引——没有索引的，就是根因。

常见错误现象：update/delete 主表时，会话卡在 enq: TM - contention，BLOCKING_SESSION 显示另一个会话正对子表做 insert/update/commit 前的长时间事务
注意：P2 是对象 ID，不是数据文件号或块号，别误查 dba_data_files
如果子表是分区表，还要确认索引是否是本地（LOCAL）且覆盖全部分区，否则仍可能争用

为什么外键没索引会导致子表被锁住

Oracle 在主表执行 DML（尤其是 update 主键、delete 行）时，必须确保子表中没有“悬空引用”。如果没有索引，数据库只能全表扫描子表来校验一致性——这个过程需要在子表上申请 TM 锁（MODE=4，即 Share Row Exclusive），而该锁与子表上正在运行的 insert/update 事务持有的 MODE=3（Row Exclusive）不兼容，于是产生阻塞。

关键点在于：这个锁不是加在某几行上，而是整张子表级别；哪怕只改主表一行，也会触发对整个子表的锁申请。

场景举例：子表 ORDER_ITEMS 外键 order_id 无索引，此时有人在 insert 新订单项（未 commit），另一人想 delete ORDERS 中某条 order，就会被卡住
insert 主表也会触发同样逻辑（尤其当启用 ON DELETE CASCADE 但未建索引时）
并行 DML（如 /*+ APPEND */）也可能申请高阶 TM 锁，但那是另一类原因，和外键无关

怎么快速找出所有缺失外键索引的子表

用这段 SQL 扫一遍全库（建议在非高峰执行）：

SELECT
  c.table_name,
  c.constraint_name,
  c.columns AS fk_columns,
  'CREATE INDEX idx_' || c.table_name || '_' || c.columns || ' ON ' || c.table_name || '(' || c.columns || ');' AS ddl
FROM (
  SELECT
    b.table_name,
    b.constraint_name,
    MAX(DECODE(position, 1, column_name)) || 
    NVL2(MAX(DECODE(position, 2, column_name)), ',' || MAX(DECODE(position, 2, column_name)), '') ||
    NVL2(MAX(DECODE(position, 3, column_name)), ',' || MAX(DECODE(position, 3, column_name)), '') AS columns
  FROM dba_cons_columns a, dba_constraints b
  WHERE a.constraint_name = b.constraint_name
    AND b.constraint_type = 'R'
    AND NOT EXISTS (
      SELECT 1 FROM dba_ind_columns i
      WHERE i.table_name = a.table_name
        AND i.column_name = a.column_name
        AND i.column_position = a.position
        AND i.index_name IN (
          SELECT index_name FROM dba_indexes
          WHERE table_name = a.table_name AND index_type != 'BITMAP'
        )
    )
  GROUP BY b.table_name, b.constraint_name
) c;

输出里每行都带可直接执行的 CREATE INDEX 语句，fk_columns 列显示外键字段组合，注意复合外键要按顺序建联合索引。

别漏掉大小写敏感的 schema 名（比如 "AA"."BB"），建索引时保持一致
如果外键列已存在其他用途索引（如唯一约束索引），只要前导列包含外键列，就无需重复建
OGG 环境下特别留意：DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION 这类操作本身就会触发 TM 锁，若外键无索引，会雪上加霜

建索引后还卡？检查这几个地方

加完索引不代表万事大吉。如果业务仍偶发卡顿，先确认索引是否真生效、是否被意外 disable，再排查事务模型本身是否合理。

确认索引状态：SELECT index_name, status, visibility FROM dba_indexes WHERE table_name = 'SUB_TABLE_NAME'，状态必须是 VALID，不可是 UNUSABLE
检查是否用了绑定变量导致硬解析失败，让优化器没走新索引——对比 dba_hist_sqlstat 中该 SQL 的 EXECUTIONS 和 PARSE_CALLS 比值
子表上有长事务（比如未 commit 的批量 insert）依然会 hold 住 TM 锁，索引只是消除了“校验阶段”的全表扫描开销，不是消除锁本身
如果主表更新非常频繁，而子表又极大，即使有索引，每次 update 主键仍需回表查子表，压力仍在，这时要考虑业务逻辑是否真需要频繁更新主键

最常被忽略的一点：外键索引建了，但字段顺序错了。比如外键是 (dept_id, loc_id)，却只在 loc_id 上单建索引，照样无效。