Oracle 锁等待深度分析

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

锁等待是常见性能问题[1]：

类型：

TX 锁（事务）
TM 锁（DML）
DDL 锁
Latch/Mutex

2. 锁视图

2.1 v$lock

SELECT 
  sid, 
  type, 
  id1, 
  id2, 
  lmode, 
  request, 
  block
FROM v$lock
WHERE type IN ('TM', 'TX');

2.2 v$session

SELECT 
  sid, 
  serial#, 
  username, 
  blocking_session, 
  blocking_session_status,
  event,
  seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

3. 锁模式

模式	值	说明
NULL	0	无
Row-S	1	行共享
Row-X	2	行排他
Share	3	共享
S/Row-X	4	共享行排他
Exclusive	6	排他

4. TX 锁

4.1 产生

INSERT/UPDATE/DELETE
事务未提交

4.2 等待事件

enq: TX - row lock contention
enq: TX - allocate ITL entry
enq: TX - index contention

4.3 诊断

-- 1. 找阻塞会话
SELECT 
  blocking_session,
  sid,
  event,
  sql_id,
  seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

-- 2. 查阻塞 SQL
SELECT sql_text FROM v$sql 
WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = &blocking_sid);

-- 3. 查事务
SELECT 
  s.sid,
  s.serial#,
  t.start_time,
  t.used_ublk
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr;

4.4 处理

-- 1. 提交/回滚事务（建议）
-- 2. 杀掉会话
ALTER SYSTEM KILL SESSION 'sid,serial#';

-- 或
ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

5. TM 锁

5.1 产生

DML 操作表
锁定表对象

5.2 诊断

SELECT 
  l.sid,
  o.object_name,
  l.type,
  l.lmode,
  l.request
FROM v$lock l, dba_objects o
WHERE l.id1 = o.object_id
  AND l.type = 'TM';

6. DDL 锁

6.1 产生

DDL 操作

6.2 等待事件

library cache lock
library cache pin

6.3 诊断

SELECT 
  s.sid,
  s.serial#,
  s.username,
  s.event,
  s.sql_id
FROM v$session s
WHERE s.event LIKE 'library cache%';

7. 死锁

7.1 检测

-- 自动检测
-- ORA-00060: deadlock detected
-- trace 文件

7.2 分析

# 找死锁 trace
ls $ADR_HOME/trace/*ora*.trc

# 查看
tkprof ...

7.3 处理

-- 1. 调整事务顺序
-- 2. 减少事务持有时间
-- 3. 增加 ITL

8. ITL 等待

8.1 概述

Interested Transaction List
块中事务槽
数量不足导致等待

8.2 诊断

SELECT event FROM v$session_wait 
WHERE event = 'enq: TX - allocate ITL entry';

8.3 优化

-- 1. 增大 INITRANS
ALTER TABLE employees INITRANS 20;

-- 2. 增大 MAXTRANS（10g+ 自动）
-- 3. 重建表
ALTER TABLE employees MOVE INITRANS 20;

9. Latch/Mutex

9.1 Latch

latch: shared pool
latch: cache buffers chains
latch: library cache

9.2 Mutex

cursor: pin S
cursor: pin X
library cache: mutex X

9.3 诊断

SELECT 
  event, 
  total_waits, 
  time_waited
FROM v$system_event
WHERE event LIKE 'latch%' OR event LIKE 'cursor%' OR event LIKE 'library cache: mutex%';

10. ASH 分析

-- 1. 锁等待历史
SELECT 
  sample_time,
  session_id,
  blocking_session,
  event,
  sql_id
FROM v$active_session_history
WHERE event LIKE 'enq: TX%'
  AND sample_time > SYSDATE - 1/24
ORDER BY sample_time;

-- 2. Top 阻塞会话
SELECT 
  blocking_session,
  COUNT(*) AS blocks
FROM v$active_session_history
WHERE blocking_session IS NOT NULL
  AND sample_time > SYSDATE - 1/24
GROUP BY blocking_session
ORDER BY blocks DESC;

11. 锁监控脚本

-- 完整锁诊断
SELECT 
  s.sid AS waiter_sid,
  s.serial# AS waiter_serial,
  s.username AS waiter_user,
  w.event AS wait_event,
  s.seconds_in_wait AS wait_secs,
  b.sid AS blocker_sid,
  b.username AS blocker_user,
  b.sql_id AS blocker_sql
FROM v$session s, v$session_wait w, v$session b
WHERE s.sid = w.sid
  AND s.blocking_session = b.sid(+)
  AND s.blocking_session IS NOT NULL;

12. 常见坑与排错

12.1 锁等待多

-- 1. 应用层：提交及时
-- 2. 减少事务持有时间
-- 3. 优化 SQL 速度
-- 4. 调整事务顺序

12.2 死锁频繁

-- 1. 分析 trace 文件
-- 2. 调整事务顺序
-- 3. 减少事务跨表
-- 4. 索引减少全表锁

12.3 ITL 等待

-- 1. INITRANS
-- 2. PCTFREE
-- 3. 重建表

13. 最佳实践

及时提交：应用层
短事务：减少持有
合理顺序：避免死锁
索引优化：减少全表
INITRANS：高并发
监控阻塞：及时
ASH 分析：历史
死锁 trace：分析
杀会话谨慎：业务影响
应用设计：根本

14. 参考资料

[1] Oracle Database Concepts 19c, “Locks” https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/data-concurrency-and-consistency.html