1. Oracle锁等待问题概述
在Oracle数据库运维中，锁等待问题就像交通堵塞一样常见且令人头疼。当多个会话同时竞争同一资源时，就会出现会话排队等待的情况，严重时会导致业务系统响应缓慢甚至完全卡死。根据Oracle官方统计，超过60%的性能问题都与锁等待相关，特别是在高并发场景下。
锁等待问题之所以棘手，主要因为以下几个特点：
- 突发性强：可能在业务高峰期突然出现
- 影响面广：一个会话阻塞可能导致连锁反应
- 诊断复杂：需要快速定位阻塞源头
- 处理风险高：不当操作可能造成数据不一致
2. 快速诊断四步法实战
2.1 第一步：识别当前锁等待会话
当接到锁等待告警时，我通常会先执行这个经过优化的SQL查询：
SELECT w.sid AS waiting_sid, w.serial# AS waiting_serial, w.username AS waiting_user, w.program AS waiting_program, w.event AS lock_event, h.sid AS holding_sid, h.serial# AS holding_serial, h.username AS holding_user, l.type AS lock_type, o.object_name, o.object_type FROM v$session w, v$lock l, v$session h, dba_objects o WHERE w.sid = l.sid AND l.block = 0 AND l.id1 = (SELECT id1 FROM v$lock WHERE sid = h.sid AND block = 1) AND l.id1 = o.object_id(+) AND w.wait_class = 'Concurrency' ORDER BY w.last_call_et DESC;这个查询的关键点在于：
- 通过
block=0筛选出等待锁的会话 - 通过子查询找到对应的持有锁会话
- 关联dba_objects获取被锁对象信息
- 按等待时间降序排列，优先处理等待时间长的会话
注意：在生产环境执行时建议加上ROWNUM限制，避免返回过多结果影响性能
2.2 第二步：分析锁等待链关系
锁等待往往不是简单的A等B，而是可能存在复杂的链式关系。我常用这个递归CTE查询来分析完整的锁等待链：
WITH lock_chain AS ( SELECT l.sid AS waiter_sid, l.block AS holder_sid, l.type AS lock_type, 1 AS level FROM v$lock l WHERE l.request > 0 UNION ALL SELECT l.sid, l.block, l.type, lc.level + 1 FROM v$lock l JOIN lock_chain lc ON l.sid = lc.holder_sid WHERE l.request > 0 ) SELECT LPAD(' ', (level-1)*2, ' ')||lc.waiter_sid AS wait_tree, s1.username AS waiting_user, s2.username AS holding_user, lc.lock_type, lc.level FROM lock_chain lc LEFT JOIN v$session s1 ON lc.waiter_sid = s1.sid LEFT JOIN v$session s2 ON lc.holder_sid = s2.sid CONNECT BY PRIOR lc.waiter_sid = lc.holder_sid START WITH lc.holder_sid IN ( SELECT sid FROM v$lock WHERE block = 1 MINUS SELECT sid FROM v$lock WHERE request > 0 ) ORDER BY lc.level;这个查询的亮点在于：
- 使用递归CTE构建完整的锁等待链
- 通过CONNECT BY实现树形展示
- 使用START WITH从最顶层的阻塞会话开始
- 直观展示各层级的等待关系
2.3 第三步：定位锁等待根因
根据锁类型的不同，根因分析的方法也各异。以下是我整理的常见锁类型诊断方法：
2.3.1 enq: TX - allocate ITL entry
这种锁表明表或索引的ITL(事务槽)不足。诊断步骤：
- 查询表的ITL配置：
SELECT table_name, ini_trans, max_trans FROM dba_tables WHERE table_name = '问题表名'; - 检查当前事务数：
SELECT table_name, COUNT(*) AS active_trans FROM v$locked_object lo, dba_objects o WHERE lo.object_id = o.object_id GROUP BY table_name; - 临时解决方案：
ALTER TABLE 表名 INITRANS 10;
2.3.2 enq: TX - index contention
索引争用常见于自增主键索引。诊断方法：
- 检查索引分裂情况：
SELECT index_name, blevel, leaf_blocks, distinct_keys FROM dba_indexes WHERE table_name = '问题表名'; - 解决方案：
- 重建索引降低blevel
- 考虑使用反向键索引
2.4 第四步：评估业务影响
评估业务影响时，我通常会关注以下几个维度：
会话等待时间：
SELECT sid, serial#, username, last_call_et/60 AS wait_minutes, program, module, action FROM v$session WHERE wait_class = 'Concurrency' ORDER BY last_call_et DESC;受影响业务模块：
SELECT module, COUNT(*) AS blocked_sessions FROM v$session WHERE wait_class = 'Concurrency' GROUP BY module ORDER BY COUNT(*) DESC;核心业务影响：
- 支付、订单等核心模块会话阻塞应立即处理
- 报表、统计等后台任务可适当延后
3. 高风险锁分类处理手册
3.1 数据库健康类锁
enq: CI - contention
特征：持续出现，伴随内存或进程异常 诊断：
SELECT * FROM v$sgastat WHERE name LIKE '%leak%'; SELECT * FROM v$process_memory WHERE allocated > 100000000;处理：
- 收集heapdump
- 重启异常进程
- 应用最新PSU补丁
3.2 事务管理类锁
enq: TX - row lock contention
特征：行级锁争用，常见于高频更新同一条记录 诊断：
SELECT sql_id, COUNT(*) FROM v$session WHERE event = 'enq: TX - row lock contention' GROUP BY sql_id;处理：
- 优化SQL加锁逻辑
- 添加FOR UPDATE NOWAIT
- 缩短事务时间
4. 应急处理流程
4.1 紧急处理步骤
确认会话可终止：
SELECT s.sid, s.serial#, s.username, s.status, s.sql_id, s.prev_sql_id, s.program, t.sql_text AS current_sql, pt.sql_text AS previous_sql FROM v$session s LEFT JOIN v$sql t ON s.sql_id = t.sql_id LEFT JOIN v$sql pt ON s.prev_sql_id = pt.sql_id WHERE s.sid = &blocking_sid;终止会话：
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;验证锁释放：
SELECT * FROM v$lock WHERE sid = &blocking_sid;
4.2 数据收集与分析
收集AWR报告：
@?/rdbms/admin/awrrpt.sql收集ASH报告：
@?/rdbms/admin/ashrpt.sql锁等待历史分析：
SELECT sample_time, event, COUNT(*) FROM dba_hist_active_sess_history WHERE event LIKE 'enq:%' GROUP BY sample_time, event ORDER BY sample_time;
5. 预防与优化建议
5.1 参数优化
调整ITL参数：
ALTER SYSTEM SET "_ktb_debug_flags"=8 SCOPE=SPFILE;优化锁超时：
ALTER SYSTEM SET "_optimizer_skip_scan_enabled"=FALSE;
5.2 应用设计建议
事务设计原则：
- 尽量缩短事务时间
- 避免在事务中执行用户交互
- 大事务拆分为小事务
SQL优化技巧：
- 为高频查询添加合适索引
- 避免全表扫描更新
- 使用SELECT FOR UPDATE NOWAIT
5.3 监控方案
- 实时监控脚本：
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') AS sample_time, COUNT(*) AS blocked_sessions, LISTAGG(sid||','||serial#, ';') WITHIN GROUP (ORDER BY last_call_et DESC) AS blocked_list FROM v$session WHERE wait_class = 'Concurrency' AND last_call_et > 300;- 历史趋势分析：
SELECT TO_CHAR(sample_time, 'YYYY-MM-DD HH24') AS hour, event, COUNT(*) AS event_count FROM dba_hist_active_sess_history WHERE event LIKE 'enq:%' GROUP BY TO_CHAR(sample_time, 'YYYY-MM-DD HH24'), event ORDER BY 1, 3 DESC;
