Oracle enq: TX - index contention 锁等待事件问题排查总结

原创数据库路上 2天前

130

Oracle enq: TX - index contention 锁等待事件问题排查总结

一、锁等待原因

这个等待事件的本质是：多个并发会话同时向同一个索引叶子块插入数据时，该块触发分裂（split），Oracle 用 TX mode 4 锁序列化分裂操作，其他会话必须排队等待分裂完成。

触发条件需要同时满足：

条件	说明
索引键值单调递增	如 `SEQUENCE.NEXTVAL`、`SYSDATE`，导致所有新值都插入索引最右侧叶子块
高并发 INSERT	多个会话同时向该表插入数据
最右叶子块空间耗尽	块满后触发 90-10 分裂，分裂过程需要 TX mode 4 锁

二、内部机制

B-Tree 索引结构（单调递增键值）：

                    [Root Block]
                   /            \
          [Branch]              [Branch]
          /      \              /      \
     [Leaf1]  [Leaf2]     [Leaf3]  [Leaf4]
     1~100    101~200     201~300   301~???
                                      ↑
                                 所有新 INSERT
                                 都挤在这个块！
                                 块满 → 触发 90-10 分裂
                                 分裂需要 TX mode 4 锁
                                 其他会话排队等待

90-10 分裂过程：

会话 A 向最右叶子块插入数据，发现块满
会话 A 获取 TX mode 4 锁，开始分裂：将 90% 的数据移到新块，10% 留在原块
会话 B、C、D… 也要向同一个块插入 → 发现块正在分裂 → 排队等待 TX mode 4
分裂完成后，会话 A 释放 TX mode 4，下一个会话获得锁继续分裂

三、模拟案例

准备工作（环境搭建）

-- ============================================================
-- 模拟 enq: TX - index contention
-- ============================================================
-- 1. 创建测试表和索引（填充因子调低，让块更容易满）
DROP TABLE t_hot_idx PURGE;
CREATE TABLE t_hot_idx (
    id    NUMBER,
    data  VARCHAR2(500)
);

-- 创建一个普通B树索引（不使用反向键）
CREATE INDEX idx_hot_id ON t_hot_idx(id);

-- 2. 创建一个序列（无缓存，加剧争抢）
CREATE SEQUENCE seq_hot_idx NOCACHE;

步骤一：预填充数据（把索引的根/枝节点建立好，填充若干叶子块）

-- 一次性插入大量数据，填满多个叶子块
INSERT INTO t_hot_idx (id, data)
SELECT rownum, rpad('X', 400, 'X')
FROM dual
CONNECT BY rownum <= 50000;
COMMIT;

-- 收集统计信息（可选）
EXEC DBMS_STATS.GATHER_TABLE_STATS(NULL, 'T_HOT_IDX');

步骤二：开启两个 SQL*Plus 或 PL/SQL 会话（模拟并发插入）

会话 1（Session A）：执行不间断插入，且不提交，以锁定当前热点块并触发分裂。

BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO t_hot_idx (id, data) 
    VALUES (seq_hot_idx.NEXTVAL, rpad('Y', 400, 'Y'));
    -- 重点：故意不提交，持续占用块
    IF MOD(i, 100) = 0 THEN
      DBMS_LOCK.SLEEP(0.01); -- 稍微慢一点，让另一个会话有机会争抢
    END IF;
  END LOOP;
  COMMIT;
END;
/

会话 2（Session B）：在同一时刻，立刻执行另一个插入循环。

BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO t_hot_idx (id, data) 
    VALUES (seq_hot_idx.NEXTVAL, rpad('Z', 400, 'Z'));
    -- 频繁提交，但提交后立即抢下一个最大值
    COMMIT; 
  END LOOP;
END;
/

会话 3 / 4 / 5 — 更多并发插入（越多效果越明显）：

-- 会话 C、D、E 同时执行相同代码...

四、查询分析

验证等待事件：

-- 查询具体的TX锁等待会话，等待会话正在执行的语句，正在等待的对象，会话自身的连接属性等详细信息（建议使用连接工具查询，而非sqlplus方式）
set linesize 200 pagesize 999
col sid for 9999
col serial# for 9999
col program for a30
col event for a30
col state for a10
col inst_id for 9
col owner format a15
col object_name format a20
col object_type format a1
select to_char(a.logon_time,'yyyy-mm-dd hh24:mi') logon_time,
       a.sql_id,
       a.event,
       a.username,
       a.osuser,
       a.process,
       a.machine,
       a.program,a.module,
       b.sql_text,
       b.LAST_LOAD_TIME,a.logon_time,
       to_char(b.last_active_time,'yyyy-mm-dd hh24:mi:ss') last_active_time,
       c.owner,c.object_name,
       a.last_call_et,
       a.sid,a.SQL_CHILD_NUMBER,a.blocking_instance,a.BLOCKING_SESSION,
       c.object_type,p.PGA_ALLOC_MEM,a.p1,a.p2,a.p3,
       'kill -9 '||p.spid killstr
  from v$session a, v$sql b, dba_objects c,v$process p
 where a.wait_class <> 'Idle' and a.status='ACTIVE' and p.addr=a.paddr
   and a.sql_id = b.sql_id(+) 
   and a.sql_child_number = b.CHILD_NUMBER(+)
   and a.row_wait_obj# = c.object_id(+)
   and a.type='USER'
   and a.event like 'enq: TX%'
 order by a.sql_id,a.event;
 
--等待事件名称：enq: TX - index contention
--如果实时模拟不出来，可以通过整体的性能统计信息来验证，或者历史会话信息查看v$active_session_history这个视图
--a.row_wait_obj# = c.object_id 相关联，可以定位到具体的对象名称--->object_name，即具体的索引名称！！

查看统计信息确认：

-- 查看索引分裂统计
SELECT name, value
FROM v$sysstat
WHERE name IN ('leaf node splits', 'leaf node 90-10 splits', 'leaf node 50-50 splits');

-- 输出示例：
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
leaf node splits                                                        774
leaf node 90-10 splits                                                  432  ← 绝大多数是 90-10 分裂（右倾增长）


-- 查看索引上的行锁等待
SELECT object_name, statistic_name, value
FROM v$segment_statistics
WHERE object_name = 'IDX_HOT_ID'
  AND statistic_name = 'row lock waits';

-- 输出示例：
OBJECT_NAME          STATISTIC_NAME                                                        VALUE
-------------------- ---------------------------------------------------------------- ----------
IDX_HOT_ID           row lock waits                                                           45

五、解决方案

方案一：反向键索引（REVERSE KEY INDEX）

原理：将键值的字节顺序反转，使原本集中在最右端的插入分散到多个叶子块。

-- 删除原索引
DROP INDEX idx_contention_test_id;

-- 创建反向键索引
CREATE INDEX idx_contention_test_id ON idx_contention_test(id) REVERSE;

优点	缺点
彻底消除右热点块	不支持范围扫描（`BETWEEN`、`>`、`<` 会退化为全表扫描）
改动最小	不支持函数索引、位图索引
无需分区许可	等值查询（`WHERE id = ?`）不受影响

适用场景：主键/唯一索引仅用于等值查询，不做范围扫描。

方案二：哈希分区索引（推荐）

原理：通过 HASH(id) 将递增值打散到多个分区，每个分区有独立的右热点块，互不干扰。

-- 删除原索引
DROP INDEX idx_contention_test_id;

-- 创建全局哈希分区索引
CREATE INDEX idx_contention_test_id
  ON idx_contention_test(id)
  GLOBAL PARTITION BY HASH(id)
  PARTITIONS 8;

优点	缺点
支持范围扫描（每个分区内仍有序）	需要分区许可
打散效果可控（分区数越多越均匀）	跨分区查询可能略慢
单分区索引碎片不影响全局	分区数建议 2 的幂次（4/8/16）

适用场景：既需要高并发 INSERT，又需要范围扫描的业务。

方案三：增大 SEQUENCE CACHE

原理：减少序列获取频率，间接降低索引块访问频率。

-- 将 CACHE 从默认 20 增大到 1000 或更高
ALTER SEQUENCE seq_test CACHE 1000;

优点	缺点
改动最小，无需重建索引	不能根治问题，只是缓解
减少序列获取的 redo 和锁	实例崩溃会丢失未用完的 cache 值
RAC 环境下减少跨节点序列争用	若索引热点仍存在，需配合其他方案

方案四：重建索引 + 调整存储参数

原理：减少索引碎片，增大 INITRANS 和 PCTFREE，降低分裂频率。

-- 重建索引，增大 INITRANS 和 PCTFREE
ALTER INDEX idx_contention_test_id REBUILD
  INITRANS 16
  PCTFREE 20
  ONLINE;

优点	缺点
在线操作，不影响业务	只是缓解，不能根治
减少分裂时的等待时间	随着数据增长，效果逐渐减弱

方案五：自适应序列（18c新增特性）（推荐！！）

用于优化使用序列作为主键列，当insert过于频繁时，出现索引块分裂的问题（序列的数值一直在不断地增长，通常每次增加一。每一个新增的条目都会被放在索引的最右边叶子块，会使得这个叶子块非常的热，从而产生争用，如果是在一起RAC集群当中，那么就会争用的更加厉害，会导致更多的集群等待事件）
为了改善以序列值作为键值的表的数据加载性能，从Oracle 18.1数据库开始，自适应序列 (Scalable Sequences) 被引入这个特性为序列提供了添加 instance 和 session 偏移量的选项，当跨 RAC节点加载数据或者单实例多个进程并发加载数据时，可以显著减少序列争用和索引块争用的可能性。
这个新特性的好处是，在以序列作为键值的表的数据加载时，它通过减少争用来进一步提升Oracle数据库加载数据的能力。在创建序列的时候，将 instance 和 session 的 id 添加到序列的值中，这样在生成序列值时产生的争用和 insert 键值时产生的索引块争用可以显著的减少。这表明Oracle数据库数据的数据加载能力可以进一步扩展，并且可以支撑更高速率的数据加载。

SCALE/NOSCALE

当 SCALE 被指定的时候，一个数字偏移量会附着在序列值的开头。
偏移量的表现形式为 iii||sss|| ，具体解释如下：
iii 表现为一个3个数字的instance偏移量，数值来源为: (instance_id % 100) + 100,
sss 表现为一个3个数字的instance偏移量，数值来源为：(session_id % 1000)
|| 是连接符

EXTEND/NOEXTEND

当 EXTEND 和 SCALE 关键字同时被指定时，产生的序列值的长度都是 (x+y)，这里 x 是自适应偏移量 (默认是6)，而 y 则是序列的 maxvalue/minvalue 关键字限定的最大值的数字位数。举例来说，如果一个升序的序列，maxvalue 被指定为100，并且指定了SCALABLE EXTEND关键字，那么产生的序列值的表现形式为 iii||sss||001，iii||sss||002 … iii||sss||100

默认情况下 SCALE 是 NOEXTEND 的，当 NOEXTEND 被设置时，产生的序列值长度最大只能是序列 maxvalue/minvalue 关键字限定的最大值的数字位数。在某些应用中，序列被用来填充固定宽度的列，那么在整合这些应用时，这个设定很有用。在调用一个 SCALABLE NOEXTEND 的序列的 NEXTVAL 值时，如果产生的序列值需要的数字位数比 maxvalue/minvalue 限定的最大值位数更大，那么一个用户错误会被抛出。

使用案例1

create sequence my_seq minvalue 1 maxvalue 9999999999 scale;  //默认为NOEXTEND
Sequence created.
SQL> select my_seq.nextval as scale_seq from dual;
 SCALE_SEQ
----------
1013910001
SQL> select my_seq.nextval as scale_seq from dual;
 SCALE_SEQ
----------
1013910002
101为(instance_id % 100) + 100
391为(session_id % 1000)

使用案例2

col scale_seq for 999999999999999999
create sequence my_seq1 minvalue 1 maxvalue 9999999999 scale extend;
Sequence created.

select my_seq1.nextval as scale_seq from dual;
          SCALE_SEQ
-------------------
   1013910000000001
select my_seq1.nextval as scale_seq from dual;
          SCALE_SEQ
-------------------
   1013910000000002

查询数据库中存在的自适应序列

column sequence_name format a30
column scale_flag format a10
column extend_flag format a10
select sequence_name,
       scale_flag,
       extend_flag
from   user_sequences
where  sequence_name = 'MY_SEQ1';
SEQUENCE_NAME                  SCALE_FLAG EXTEND_FLA
------------------------------ ---------- ----------
MY_SEQ1                         Y          Y

七、SQL常用查询

-- 1. 快速确认是否存在 enq: TX - index contention
SELECT event, COUNT(*) AS cnt, SUM(time_waited) AS total_wait_us
FROM v$active_session_history
WHERE event = 'enq: TX - index contention'
  AND sample_time > SYSDATE - 1/24   -- 最近1小时
GROUP BY event;

-- 2. 定位争用的索引对象
SELECT
  ash.current_obj#,
  ob.owner,
  ob.object_name,
  ob.object_type,
  COUNT(*) AS wait_count,
  SUM(ash.time_waited) AS total_wait_us
FROM v$active_session_history ash
JOIN dba_objects ob ON ash.current_obj# = ob.object_id
WHERE ash.event = 'enq: TX - index contention'
  AND ash.sample_time > SYSDATE - 1/24
GROUP BY ash.current_obj#, ob.owner, ob.object_name, ob.object_type
ORDER BY wait_count DESC;

-- 3. 确认索引是否为单调递增键值
SELECT
  i.index_name,
  i.table_name,
  ic.column_name,
  ic.column_position,
  i.index_type
FROM dba_indexes i
JOIN dba_ind_columns ic ON i.owner = ic.index_owner AND i.index_name = ic.index_name
WHERE i.index_name = 'IDX_CONTENTION_TEST_ID'
ORDER BY ic.column_position;

-- 4. 检查索引分裂统计
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%leaf node%split%';

-- 5. 检查序列 CACHE 大小
SELECT sequence_name, cache_size, order_flag, min_value, max_value
FROM dba_sequences
WHERE sequence_owner = 'YOUR_SCHEMA';

墨力计划 oracle

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle enq: TX - index contention 锁等待事件问题排查总结