Oracle 位圖索引導致 DML 鎖競爭死鎖：原理重現、AWR 分析與解決方案

現象描述

RAC 環境下，4 個應用並發執行 DELETE 語句極慢，AWR 報告顯示大量 enq: TX - row lock contention 等待事件，v$locked_object 顯示大量行鎖持有。

根因：位圖索引鎖機制

• 位圖索引為每個 distinct 值維護一個位圖，每 bit 對應一行（rowid 範圍）
• DML 修改某行時，需鎖定該值對應的整個位圖片段，導致同值不同行互斥
• 並發 DML 針對相同列值的不同行 → 爭搶同一位圖塊 → 嚴重鎖競爭

實驗重現

-- 1. 建表與位圖索引
CREATE TABLE test (id NUMBER, col VARCHAR2(20));
CREATE BITMAP INDEX idx_t_col ON test(col);

-- 2. 插入測試數據
INSERT INTO test VALUES (1, 'A');
INSERT INTO test VALUES (2, 'A');
INSERT INTO test VALUES (3, 'A');
INSERT INTO test VALUES (4, 'B');
COMMIT;

-- 3. Session 1：更新 col='A' 的第一行
UPDATE test SET col='A' WHERE id=1; -- 未提交

-- 4. Session 2：更新 col='A' 的第二行（被阻塞）
UPDATE test SET col='A' WHERE id=2; -- HANG，等待 Session 1 提交

-- 5. Session 3：更新 col='B' 的行（正常）
UPDATE test SET col='B' WHERE id=4; -- 立即成功

AWR 關鍵證據

解決方案

1. 刪除位圖索引（最直接）：DROP INDEX idx_t_col; → 並發 DML 秒級完成
2. 改用 B*Tree 索引：CREATE INDEX idx_t_col_btree ON test(col);（高基數列適用）
3. 分區表 + 局部索引：將熱點數據物理隔離
4. 應用層串行化：業務允許時，隊列化處理同值更新

適用場景對照

排查腳本

-- 查找所有位圖索引
SELECT owner, index_name, table_name, uniqueness, index_type
FROM dba_indexes
WHERE index_type = 'BITMAP';

-- 監控實時鎖等待
SELECT s.sid, s.serial#, s.username, o.object_name, l.locked_mode
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id
JOIN v$session s ON l.session_id = s.sid
WHERE l.locked_mode > 2;