面试宝典: Oracle数据库enq:TM - contention等待事件处理过程

原创于 2025-08-06 12:37:39 发布 · 661 阅读

21 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #oracle #面试

AI辅助_面试宝典专栏收录该内容

584 篇文章

订阅专栏

在这里插入图片描述
好的，我们来深入解析 Oracle 数据库中的 enq: TM - contention 等待事件。这个事件直接关系到数据库并发控制的核心机制，通常发生在多个会话试图以冲突的方式访问同一个表（或分区） 时，是 DML 操作性能调优的关键点之一。

1. 什么是 `enq: TM - contention` 等待事件？

本质： 它表示一个会话正在等待获取 TM (DML Enqueue / Table Lock) Enqueue。这个锁用于协调对数据库对象（主要是表、分区）的并发访问，确保 DML 操作（INSERT, UPDATE, DELETE, SELECT FOR UPDATE）和 DDL 操作 (ALTER TABLE, DROP TABLE) 的正确性和一致性。
名字解析：
- enq:：表示这是一个 Enqueue 等待事件。
- TM： Enqueue 的类型标识符。T 代表 Table，M 代表模式（Mode），表示这是一种表级锁。
- contention：表示发生了争用，即多个会话同时尝试获取对同一个表的冲突模式的 TM 锁，导致部分会话必须等待。
核心特征：
- 保护表结构： TM 锁的主要目的是防止在 DML 操作进行时，表的结构被意外修改（如 ALTER TABLE ... DROP COLUMN 删除一个正在被更新的列）。它确保了在事务期间，表定义是稳定的。
- 协调 DML 并发： 虽然行级锁 (TX Enqueue) 协调对具体行的修改，TM 锁在表级别上协调不同会话对同一对象的 DML 意图。不同模式的 TM 锁决定了允许哪些其他操作并发执行。
- 锁模式 (Lock Modes)： TM 锁有多种模式，决定了其兼容性：
  - Row Share (RS) / Sub-Share (SS)： 模式 2。允许其他会话同时获取 RS/SS 或 RX/SX 锁，但阻止独占锁（X）。由 SELECT ... FOR UPDATE 获取。
  - Row Exclusive (RX) / Sub-Exclusive (SX)： 模式 3。允许其他会话同时获取 RS/SS 或 RX/SX 锁，但阻止共享锁（S）和独占锁（X）。由 INSERT, UPDATE, DELETE 获取（在获取行锁 TX 之前）。
  - Share (S)： 模式 4。允许其他会话获取 RS/SS 或 S 锁，但阻止 RX/SX 和 X 锁。很少见，通常由显式 LOCK TABLE ... IN SHARE MODE 获取。
  - Share Row Exclusive (SRX)： 模式 5。比 S 更严格，只允许 RS/SS。很少见，通常由显式 LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE 获取。
  - Exclusive (X)： 模式 6。最严格的锁。阻止其他任何会话获取除 RS/SS 以外的任何 TM 锁。由 DDL 操作 (ALTER TABLE, DROP TABLE, CREATE INDEX) 或显式 LOCK TABLE ... IN EXCLUSIVE MODE 获取。
- 锁的持有时间： TM 锁通常在 DML 语句开始时获取，并在事务结束（COMMIT 或 ROLLBACK）时释放。这与 TX 行锁的释放时机相同。
- 与 TX 锁的关系： 执行 DML 时，会话会先获取目标表的 TM 锁（通常是 RX/SX），然后尝试获取要修改的具体行的 TX 锁。TM 锁是表级意图锁，TX 锁是行级锁。

2. 产生的过程

当一个会话执行需要修改表数据或结构，或者以特定方式查询数据时：

发起 DML/DDL/查询： 会话执行一条语句（如 UPDATE employees SET salary = ... WHERE ..., DELETE FROM orders ..., SELECT ... FROM departments FOR UPDATE, ALTER TABLE ...）。
确定目标对象： Oracle 解析语句，确定需要访问和修改的表（或分区）。
请求 TM Enqueue： 根据操作类型，会话尝试以特定模式获取保护该表的 TM Enqueue。
- UPDATE/DELETE/INSERT：请求 RX (Row Exclusive, mode 3) 锁。
- SELECT ... FOR UPDATE：请求 RS (Row Share, mode 2) 锁。
- LOCK TABLE ...：根据语句请求指定模式（S, SRX, X）。
- DDL (如 ALTER TABLE, DROP TABLE)：请求 X (Exclusive, mode 6) 锁。
检查锁兼容性：
- 兼容： 如果当前没有其他会话持有该表的冲突模式 TM 锁（例如，当前只有其他 RS 或 RX 锁，而本会话请求 RX 锁是兼容的），则会话立即成功获取 TM 锁。然后继续执行（对于 DML，下一步是获取行锁 TX）。
- 不兼容（争用）： 如果另一个会话持有该表的冲突模式 TM 锁（例如：一个会话持有 X 锁进行 ALTER TABLE，另一个会话尝试获取 RX 锁进行 UPDATE；或者一个会话持有 S 锁，另一个尝试获取 RX 锁），则当前会话无法立即获取锁。
进入等待： 会话进入 enq: TM - contention 等待状态，并排队等待该 TM Enqueue。在等待期间，会话处于阻塞状态。
持有者释放： 持有冲突模式 TM 锁的会话完成其操作并提交或回滚事务（释放 TM 锁），或者完成 DDL 操作（隐式提交并释放锁）。
唤醒等待者： 等待队列中的下一个会话（按排队顺序）被唤醒，成功获得请求模式的 TM 锁。
继续操作： 该会话获得锁后，继续执行其语句。
- 对于 DML：接下来尝试获取要修改的行的 TX 锁（可能又导致 enq: TX - row lock contention）。
- 对于 SELECT FOR UPDATE：尝试获取行的 TX 锁。
- 对于 DDL：执行表结构修改。
事务结束释放锁： 当持有 TM 锁的会话执行 COMMIT 或 ROLLBACK 时（对于 DML 和 SELECT FOR UPDATE），或者 DDL 语句执行完成时，该会话持有的 TM 锁（以及相关的 TX 锁）被释放。

3. 哪些场景会触发 `enq: TM - contention`

主要发生在多个会话试图以冲突模式并发访问同一个表（或分区） 时：

DDL 操作阻塞 DML：
- 经典场景： 一个会话执行长时间运行的 ALTER TABLE ...（如添加列、修改列类型、移动表、在线重定义准备阶段）、DROP TABLE、TRUNCATE TABLE（获取 X 锁）时，其他任何会话尝试对该表执行 INSERT/UPDATE/DELETE（需要 RX 锁）或 SELECT ... FOR UPDATE（需要 RS 锁）都会被阻塞，产生 enq: TM - contention 等待。这是最常见的原因之一。
未提交事务阻塞 DDL：
- 一个会话对表执行了 DML (INSERT/UPDATE/DELETE) 或 SELECT ... FOR UPDATE 但未提交（仍持有 RX 或 RS 锁），此时另一个会话尝试在该表上执行 DDL（需要 X 锁）会被阻塞。
外键约束未索引 (最隐蔽且常见的问题)：
- 核心问题： 当存在未索引的外键列时，在子表上执行 DELETE 或 UPDATE 主键/唯一键操作（修改父表）可能导致严重的 TM 争用。
- 过程：
  1. 会话 A 删除父表 PARENT 中的一行（或在唯一键列上更新）。
  2. Oracle 需要确保子表 CHILD 中没有对应的外键引用该行（参照完整性）。如果子表 CHILD 的外键列 parent_id 没有索引：
  3. Oracle 无法高效地检查子表，它会在子表 CHILD 上获取一个共享锁 (Share Lock, mode 4) 以防止其他会话修改子表（可能破坏检查的一致性）。
  4. 会话 A 持有子表 CHILD 的 S 锁（直到事务提交！）。
  5. 此时，任何其他会话尝试修改子表 CHILD（需要 RX 锁）都会被阻塞，因为 S 锁和 RX 锁冲突。大量会话堆积在 enq: TM - contention 等待上。
  6. 同样，会话 A 在父表上的 DML 未提交时，其他会话在子表上的 DML 也会被阻塞。问题具有对称性。
- 影响： 即使只删除父表的一行，也可能导致整个子表被长时间锁定，完全阻塞对该子表的并发 DML，造成灾难性性能问题。这是 DBA 必须避免的情况。
显式锁表：
- 应用程序显式执行 LOCK TABLE ... IN SHARE MODE（S 锁）或 ... IN EXCLUSIVE MODE（X 锁）。其他会话执行需要冲突锁模式的操作会被阻塞。
递归 SQL 或内部操作：
- 某些 Oracle 内部操作（如空间管理、递归 DML）在访问数据字典表（如 tab$, col$）时，如果发生冲突，也可能导致 TM 争用。这相对少见。

4. 可能的原因

enq: TM - contention 的出现通常意味着表级的并发访问控制成为了瓶颈。主要原因包括：

A. 长时间运行的 DDL 操作： ALTER TABLE, DROP TABLE, TRUNCATE TABLE, CREATE INDEX（非 Online）等操作持有 X 锁，阻塞所有并发 DML 和 SELECT FOR UPDATE。
B. 长时间未提交的事务： 一个会话执行了 DML (INSERT/UPDATE/DELETE) 或 SELECT FOR UPDATE 后长时间不提交，持有 RX 或 RS 锁，这会阻塞需要 X 锁的 DDL 操作（原因 1 的反向情况）。如果该事务修改的表恰好是外键约束的父表或子表，问题更严重（见 C）。
C. 外键约束未索引 (极其重要)： 这是最常见、最隐蔽、影响最大的原因。子表的外键列没有索引，导致父表 DML 时需要在子表上加 S 锁，或者子表 DML 时可能需要在父表上加 S 锁（取决于操作和 Oracle 版本），阻塞对子表/父表的并发 DML。
D. 应用程序设计问题：
- 不必要地使用 SELECT ... FOR UPDATE 且事务过长。
- 在业务高峰期执行 DDL。
- 事务设计不合理，持有锁的时间过长。
- 显式使用 LOCK TABLE 且模式冲突或持有时间过长。
E. 递归 SQL 或内部争用： 虽然较少见，但内部操作访问核心数据字典对象时发生冲突也可能导致。

5. 详细排查过程

排查 enq: TM - contention 的核心是：定位被争用的对象 -> 识别持有冲突锁的会话和操作 -> 分析根本原因（DDL、长事务、外键）-> 针对性解决。

步骤 1: 确认问题与范围

识别 Top Wait Event: 查看 AWR/ASH 报告 (awrrpt.sql, ashtop.sql)。确认 enq: TM - contention 是否在系统级别或特定时间段是主要等待事件。记录其 Total Wait Time (s) 和 Avg Wait (ms)。
确定影响范围： 是整个数据库？特定表？特定时间段（如 DDL 执行期间）？使用 ASH 报告 (ashrpt.sql) 或查询 gv$active_session_history / dba_hist_active_sess_history，按 sql_id, module, action, user_id, current_obj# 等维度聚合等待事件和时间。特别关注 sql_id 是否是 DDL 或特定 DML。
关联操作类型： 检查 AWR 报告的 “SQL Statistics” 部分，是否有高负载的 DDL 语句或涉及特定表的 DML。

步骤 2: 定位被争用的对象和阻塞会话 (关键步骤)

使用 DBA_HIST_ACTIVE_SESS_HISTORY (ASH) / GV$ACTIVE_SESSION_HISTORY：

-- 查找经历 TM 等待最多的对象和阻塞者
SELECT
  ash.session_id, ash.session_serial#, ash.user_id, u.username,
  ash.sql_id, ash.sql_opname, -- SQL 操作名 (e.g., UPDATE, DELETE, ALTER TABLE)
  ash.blocking_session, ash.blocking_session_serial#, -- 阻塞会话信息
  ash.blocking_inst_id, -- RAC 中阻塞实例 ID
  o.owner, o.object_name, o.object_type, o.subobject_name AS partition_name, -- 被锁对象
  COUNT(*) AS total_waits, SUM(ash.time_waited)/1000 AS total_wait_sec,
  ash.p1, ash.p2, ash.p3 -- Enqueue 参数
FROM dba_hist_active_sess_history ash -- 或用 gv$active_session_history 查实时
JOIN dba_users u ON (ash.user_id = u.user_id)
JOIN dba_objects o ON (ash.current_obj# = o.object_id)
WHERE ash.event = 'enq: TM - contention'
AND ash.sample_time BETWEEN ... AND ... -- 指定问题时间段
GROUP BY ash.session_id, ash.session_serial#, ash.user_id, u.username,
         ash.sql_id, ash.sql_opname, ash.blocking_session, ash.blocking_session_serial#,
         ash.blocking_inst_id, o.owner, o.object_name, o.object_type, o.subobject_name,
         ash.p1, ash.p2, ash.p3
ORDER BY total_waits DESC, total_wait_sec DESC;

这会直接告诉你：
- 哪些会话 (session_id, serial#) 在等待。
- 它们在执行什么 SQL (sql_id, sql_opname - 操作类型)。
- 哪个对象 (owner, object_name, object_type, partition_name) 被争用。
- 谁阻塞了它们 (blocking_session, blocking_session_serial#, blocking_inst_id)。
- 锁参数 (p1, p2, p3)。

解析锁参数 (P1, P2, P3)：
- P1 = 'name|mode' (e.g., 'TM|3' 表示请求 RX 锁，'TM|4' 表示请求 S 锁，'TM|6' 表示请求 X 锁)。
- P2 = id1。对于 TM Enqueue，id1 就是被锁定的对象的 object_id。 使用这个 object_id 可以直接查询 dba_objects 确认对象。
```
SELECT owner, object_name, object_type FROM dba_objects WHERE object_id = &id1_from_p2;
```
- P3 = id2。对于 TM 锁，id2 通常是 0。在特定场景下可能有其他含义，但通常 0 表示表级锁。

查询阻塞会话详情： 使用步骤 1 中找到的 blocking_session, blocking_session_serial#, blocking_inst_id (RAC) 查询：

-- 查询阻塞会话信息
SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.program, s.module, s.action,
       s.status, s.sql_id, s.prev_sql_id, s.row_wait_obj#,
       TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time,
       s.last_call_et -- 最后一次调用耗时 (秒)，长事务指示器
FROM gv$session s
WHERE s.inst_id = &blocking_inst_id -- RAC 需要
  AND s.sid = &blocking_session
  AND s.serial# = &blocking_session_serial#;

查看阻塞会话正在执行的 sql_id (s.sql_id) 或之前执行的 prev_sql_id。
查看 s.status： ACTIVE 通常表示正在执行 SQL，INACTIVE 可能表示空闲但有未提交事务。
last_call_et 很大（几百、几千秒）强烈暗示一个长时间未提交的事务。
查看 program, module, action, osuser, machine 定位应用来源。

获取阻塞会话的 SQL：

-- 当前 SQL
SELECT sql_text FROM v$sql WHERE sql_id = '&blocking_sql_id';
-- 上一个 SQL (如果当前为空闲)
SELECT sql_text FROM v$sql WHERE sql_id = '&blocking_prev_sql_id';

分析阻塞会话执行的 SQL：是否是 DDL？是否是修改了父表/子表的 DML？是否是 SELECT FOR UPDATE？

检查阻塞会话的事务状态：
```
SELECT t.addr, t.start_time, t.status, t.xid, t.used_ublk, t.used_urec
FROM v$transaction t
JOIN v$session s ON (t.addr = s.taddr)
WHERE s.sid = &blocking_session
  AND s.serial# = &blocking_session_serial#;
```
- 如果查询返回结果，说明该阻塞会话有未提交的事务 (t.status = 'ACTIVE')。used_ublk 表示使用的撤销块数，间接反映事务大小。
- 如果查询无结果，说明阻塞会话没有未提交事务，阻塞可能由 DDL 操作本身持有（DDL 是自动提交的，但在执行期间持有锁）或 SELECT FOR UPDATE 未提交。

步骤 3: 分析对象与外键关系 (针对外键问题)

如果被争用的对象是表，且阻塞模式涉及 S 锁（P1 显示请求 RX 但被 S 阻塞，或请求 S/X 被 S 阻塞），或者阻塞会话的 SQL 涉及父表/子表的 DML，必须检查外键约束和索引！

查询表上的外键约束：

SELECT c.owner, c.table_name AS child_table, c.constraint_name, c.r_constraint_name,
       p.owner AS parent_owner, p.table_name AS parent_table,
       cc.column_name AS fk_column, -- 外键列
       (SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY position)
        FROM dba_cons_columns
        WHERE constraint_name = c.constraint_name AND owner = c.owner) AS fk_columns, -- 外键列（复合键）
       (SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY position)
        FROM dba_cons_columns
        WHERE constraint_name = c.r_constraint_name AND owner = p.owner) AS pk_columns -- 父表主键/唯一键列
FROM dba_constraints c
JOIN dba_constraints p ON (c.r_owner = p.owner AND c.r_constraint_name = p.constraint_name)
LEFT JOIN dba_cons_columns cc ON (c.owner = cc.owner AND c.constraint_name = cc.constraint_name AND cc.position = 1) -- 取第一个列名
WHERE c.constraint_type = 'R' -- 外键
  AND (c.table_name = '&CHILD_TABLE_NAME' OR p.table_name = '&PARENT_TABLE_NAME') -- 替换为实际子表或父表名
  AND c.owner = '&OWNER'; -- 替换为实际 owner

确认存在外键约束。

检查外键列是否有索引：

-- 方法 1：检查约束列是否有索引 (不一定完美，但常用)
SELECT i.index_name, i.uniqueness, ic.column_name, ic.column_position
FROM dba_indexes i
JOIN dba_ind_columns ic ON (i.owner = ic.index_owner AND i.index_name = ic.index_name)
WHERE i.table_owner = '&CHILD_OWNER'
  AND i.table_name = '&CHILD_TABLE_NAME'
  AND ic.column_name IN ( -- 替换为实际外键列名
      SELECT column_name FROM dba_cons_columns
      WHERE owner = '&CONSTRAINT_OWNER' AND constraint_name = '&FK_CONSTRAINT_NAME'
      ORDER BY position)
ORDER BY i.index_name, ic.column_position;
-- 方法 2：更精确地检查索引是否按顺序覆盖外键列
-- 需要比较索引列顺序是否与外键列顺序完全一致且前缀匹配。

核心： 外键列（如果是复合键，则是构成外键的所有列）必须建立索引。索引不必是唯一索引。
如果查询没有返回结果，或者返回的索引没有按正确顺序包含所有外键列，那么外键列没有合适的索引！这就是 TM 争用的根源。

检查父表主键/唯一键索引： 确保父表被引用的键有主键或唯一键约束（通常都有，但需确认）。

步骤 4: 综合分析与解决方案

根据排查结果，针对不同原因采取措施：

1. 处理长时间运行的 DDL：
- 避免高峰 DDL： 将 ALTER TABLE, CREATE INDEX (非 Online), DROP TABLE 等需要 X 锁的 DDL 安排在维护窗口或业务低峰期执行。
- 使用 Online DDL (如果适用)： 对于 CREATE INDEX, 使用 CREATE INDEX ... ONLINE。对于某些 ALTER TABLE 操作（如添加可为空列、添加约束 NOT VALIDATED），可能减少阻塞时间，但不一定完全避免 X 锁。在线重定义 (DBMS_REDEFINITION) 是更彻底的在线方案。
- 沟通与协调： 提前通知用户，确保没有关键业务在目标表上运行。
- 监控与终止 (谨慎)： 如果 DDL 是意外执行或失控，且确认可以终止，使用 ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 终止阻塞会话（DDL 会回滚）。仅在绝对必要时使用，并评估影响。
2. 处理长时间未提交的事务：
- 定位应用逻辑： 找到持有锁的会话对应的应用程序代码或用户操作。检查代码逻辑，确保 DML 操作后及时提交事务。
- 优化事务设计： 避免在事务中包含不必要的操作或长时间等待（如用户输入），尽可能缩短事务长度。
- 设置事务超时： 使用 RESOURCE_LIMIT 和 PROFILES 设置 IDLE_TIME 或应用层设置超时机制，自动终止长时间空闲会话。
- 监控与终止： 对于已发现的、无用的长事务阻塞会话，通知用户提交或回滚。若无法联系或事务僵死，在评估风险后使用 ALTER SYSTEM KILL SESSION 终止。
- 分析 SELECT FOR UPDATE： 检查阻塞会话是否在执行 SELECT ... FOR UPDATE 后未提交。评估是否真的需要行级锁，或者是否可以使用 NOWAIT / SKIP LOCKED 选项避免阻塞，或者缩短持有锁的时间。
3. 解决外键未索引问题 (最高优先级)：
- 立即创建索引： 在子表的外键列上创建索引。这是唯一的根治方法。
```
CREATE INDEX &child_table_owner.&idx_name ON &child_table_owner.&child_table_name (&fk_column1, &fk_column2, ...) TABLESPACE ...;
```
  - 索引名称、表空间等按需指定。
  - 复合外键需要复合索引，列顺序与外键约束列顺序一致。
  - 创建索引本身可能需要 DDL 锁，安排在低峰期或使用 ONLINE 选项。
- 评估索引必要性： 创建前评估索引对查询性能的影响（通常是积极的）。
- 预防： 强制规定：所有外键列必须创建索引。 纳入数据库设计规范和审核流程。
4. 处理显式锁表：
- 审查代码： 查找应用程序中显式的 LOCK TABLE 语句。
- 评估必要性： 是否真的需要表级锁？是否可以用行级锁 (SELECT FOR UPDATE) 或更细粒度的并发控制替代？
- 优化锁模式和持有时间： 如果必须使用，选择最低必要模式（如避免不必要的 S/X 锁），并确保在尽可能短的时间内持有锁（尽快提交/回滚）。
5. 递归 SQL / 内部操作：
- 检查 Oracle 错误日志 (alert.log)： 是否有相关的 ORA-600 内部错误或其他异常。
- 分析 ASH/AWR： 尝试定位具体的内部 SQL 或操作。
- 考虑打补丁或升级： 如果是已知 Bug，查阅 My Oracle Support (MOS)，应用相应补丁或升级到修复版本。

关键诊断视图与脚本总结

定位等待与会话：
- V$SESSION_WAIT / V$SESSION_EVENT
- V$ACTIVE_SESSION_HISTORY (实时) / DBA_HIST_ACTIVE_SESS_HISTORY (历史 AWR) - 最强大
- V$LOCK / GV$LOCK (结合 V$SESSION)
- DBA_HIST_SYSTEM_EVENT / AWR 报告
分析阻塞链：
- V$SESSION (BLOCKING_INSTANCE, BLOCKING_SESSION, BLOCKING_SESSION_STATUS)
- GV$LOCK (BLOCK)
- 脚本： @?/rdbms/admin/utllockt.sql (Oracle 提供的锁树脚本)
获取 SQL 信息：
- V$SQL / V$SQLTEXT / DBA_HIST_SQLTEXT
检查事务状态：
- V$TRANSACTION (连接 V$SESSION.TADDR)
分析对象与外键：
- DBA_OBJECTS (由 OBJECT_ID 定位对象名)
- DBA_CONSTRAINTS, DBA_CONS_COLUMNS (查询外键约束)
- DBA_INDEXES, DBA_IND_COLUMNS (检查外键索引)
查看参数与配置：
- V$PARAMETER

重要提示

TM 锁是保护表结构的卫士： 它的存在是必要的，但设计不当会导致并发瓶颈。
外键未索引是隐形炸弹： 这是生产环境 TM 争用的最常见原因，且影响巨大。务必为所有外键列建立索引！
DDL 操作需要谨慎： 理解 DDL 的锁行为，避免在业务高峰执行阻塞性 DDL。优先使用 Online 选项。
短事务是良好实践： 及时提交事务，避免长时间持有任何锁 (TM 或 TX)。
ASH/AWR 是诊断利器： 充分利用历史活动会话数据快速定位问题对象、SQL 和阻塞源。
区分 TM 和 TX 锁： TM 是表级锁，TX 是行级锁。两者常伴随出现（DML 先拿 TM 再拿 TX），但原因和解决方案不同。
SELECT FOR UPDATE 也是锁： 它会获取 TM (RS) 和 TX 锁，同样需要在事务结束时提交释放。