Oracle 锁与闩锁诊断

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

1. 概述

锁和闩锁是并发控制的核心[1]：

类型	说明
Lock	DML/DDL 锁
Latch	内存结构保护
Mutex	轻量级锁

2. 锁类型

2.1 DML 锁

锁	说明
TX	事务锁
TM	表锁

2.2 表锁模式

RS（Row Share）
RX（Row Exclusive，默认 DML）
S（Share）
SRX（Share Row Exclusive）
X（Exclusive）

3. 查看锁

3.1 锁信息

SELECT 
  s.sid,
  s.serial#,
  s.username,
  s.program,
  l.type,
  l.lmode,
  l.request,
  l.id1,
  l.id2,
  l.block
FROM v$lock l, v$session s
WHERE l.sid = s.sid
ORDER BY l.block DESC;

3.2 锁等待

SELECT 
  blocking_session,
  session_id,
  lock_type,
  mode_held,
  mode_requested,
  blocking_username
FROM dba_waiters;

3.3 阻塞链

SELECT 
  LEVEL,
  s.sid,
  s.username,
  s.program,
  s.event,
  s.seconds_in_wait
FROM v$session s
START WITH s.blocking_session IS NULL
  AND s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
CONNECT BY PRIOR s.sid = s.blocking_session;

4. 锁场景

4.1 行锁阻塞

会话 1：UPDATE emp SET sal=100 WHERE id=1;  -- 不 COMMIT
会话 2：UPDATE emp SET sal=200 WHERE id=1;  -- 等待

4.2 表锁

LOCK TABLE employees IN EXCLUSIVE MODE;

4.3 SELECT FOR UPDATE

SELECT * FROM employees WHERE id = 100 FOR UPDATE;
SELECT * FROM employees WHERE id = 100 FOR UPDATE NOWAIT;
SELECT * FROM employees WHERE id = 100 FOR UPDATE WAIT 10;
SELECT * FROM employees WHERE dept_id = 10 FOR UPDATE SKIP LOCKED;

5. 死锁

5.1 检测

-- Oracle 自动检测死锁
-- 抛 ORA-00060
-- trace 文件记录

5.2 查找 trace

# alert log
grep ORA-00060 $ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace/alert_$ORACLE_SID.log

# trace 文件
ls $ORACLE_BASE/diag/rdbms/$DB_UNIQUE_NAME/$ORACLE_SID/trace/*ora*.trc

5.3 预防

固定加锁顺序
缩短事务
NOWAIT/SKIP LOCKED
批量提交

6. 闩锁（Latch）

6.1 查看

SELECT 
  name,
  gets,
  misses,
  spin_gets,
  sleeps
FROM v$latch
WHERE misses > 0
ORDER BY misses DESC;

6.2 常见 Latch

Latch	说明
shared pool	共享池
library cache	库缓存
cache buffers chains	Buffer Cache 链
cache buffers lru chain	LRU 链
redo writing	Redo 写
redo allocation	Redo 分配

6.3 等待事件

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

6.4 优化

shared pool：绑定变量
library cache：减少硬解析
cache buffers chains：减少热点块

7. Mutex

7.1 概述

替代部分 Latch
更轻量
pin 共享

7.2 查看

SELECT * FROM v$mutex_sleep_history;
SELECT * FROM v$mutex_sleep;

7.3 等待

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

8. 诊断工具

8.1 ASH

SELECT 
  sql_id,
  event,
  COUNT(*)
FROM v$active_session_history
WHERE event LIKE 'latch%' OR event LIKE 'enq%'
GROUP BY sql_id, event
ORDER BY COUNT(*) DESC;

8.2 AWR

-- Top wait events
SELECT * FROM dba_hist_system_event
WHERE snap_id BETWEEN 100 AND 110
  AND wait_class != 'Idle'
ORDER BY time_waited_micro DESC;

8.3 ORADEBUG

-- 转储锁信息
ORADEBUG SETORAPNAME <pid>
ORADEBUG DUMP LOCKS 1;

9. 处理锁问题

9.1 查找阻塞会话

SELECT 
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.event,
  s.seconds_in_wait,
  s.sql_id
FROM v$session s
WHERE s.sid IN (
  SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL
);

9.2 杀掉会话

ALTER SYSTEM KILL SESSION 'sid,serial#';

-- RAC
ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

9.3 断开连接

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

10. 常见坑与排错

10.1 ORA-00060: 死锁

-- 1. 查看 trace
-- 2. 调整加锁顺序
-- 3. 缩短事务

10.2 ORA-00054: 资源忙

-- NOWAIT 立即失败
-- 1. 查找锁
-- 2. 等待或杀会话

10.3 Latch 争用高

-- 1. 优化 SQL
-- 2. 绑定变量
-- 3. 增大 Shared Pool
-- 4. 减少 hard parse

10.4 长时间等待

-- 查找长事务
SELECT 
  sid,
  serial#,
  username,
  status,
  seconds_in_wait,
  event
FROM v$session
WHERE seconds_in_wait > 60
  AND username IS NOT NULL;

11. 最佳实践

缩短事务：减少锁持有
固定加锁顺序：避免死锁
外键加索引：避免锁升级
NOWAIT/SKIP LOCKED：避免阻塞
绑定变量：减少 Latch 争用
合理 Shared Pool：减少 Latch
监控锁等待：及时发现
监控 Latch：性能
杀掉卡死会话：恢复
定期分析：预防

12. 参考资料

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