印刻数据库

外观

Oracle 锁等待与等待事件分析 ​

锁的基本概念 ​

锁类型 ​

Oracle 数据库中的锁分为多种类型，根据不同的操作和资源对象进行分类：

DML 锁 ​

  • 行级锁 (TX锁)：对表中的行进行锁定，防止其他事务修改同一行
  • 表级锁 (TM锁)：对表结构进行锁定，确保 DML 操作的完整性
    • TM0：插入操作
    • TM2：更新操作
    • TM3：删除操作
    • TM4：表锁定操作
    • TM5：行共享 (RS)
    • TM6：行排他 (RX)
    • TM7：共享 (S)
    • TM8：共享排他 (SX)
    • TM9：排他 (X)

DDL 锁 ​

  • 排他 DDL 锁 (X锁)：防止其他会话修改对象结构
  • 共享 DDL 锁 (S锁)：允许并发查询对象结构
  • 可中断解析锁 (BR锁)：用于解析 SQL 语句时的对象引用

系统锁 ​

  • 闩锁 (Latch)：保护内存结构的轻量级锁
  • 互斥锁 (Mutex)：保护共享内存的更轻量级锁
  • 库缓存锁：保护 SQL 语句的执行计划
  • 库缓存钉扎：防止 SQL 语句的执行计划被老化

等待事件概述 ​

等待事件分类 ​

Oracle 等待事件主要分为以下几类：

空闲等待事件 ​

  • 指会话处于空闲状态，不消耗系统资源
  • 示例：SQLNet message from client、SQLNet message to client

非空闲等待事件 ​

  • 指会话正在等待某种资源或操作完成
  • 主要子类：
    • 锁定等待：如 enq: TX - row lock contention
    • I/O 等待：如 db file sequential read、db file scattered read
    • CPU 等待：如 CPU time
    • 网络等待：如 SQL*Net more data from client
    • 并发等待：如 latch free、enq: HW - contention
    • 应用等待：如 application wait
    • 配置等待：如 log file sync、log buffer space

锁等待诊断方法 ​

查看当前锁等待情况 ​

使用 V$LOCK 和 V$SESSION 视图 ​

sql
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.program,
    l.type,
    l.lmode,
    l.request,
    o.owner,
    o.object_name,
    o.object_type
FROM
    v$lock l
    JOIN v$session s ON l.sid = s.sid
    LEFT JOIN dba_objects o ON l.id1 = o.object_id
WHERE
    s.username IS NOT NULL
ORDER BY
    s.sid;

使用 V$SESSION_WAIT 视图查看等待事件 ​

sql
SELECT
    sid,
    serial#,
    username,
    program,
    event,
    p1,
    p2,
    p3,
    wait_time_micro / 1000000 AS wait_seconds
FROM
    v$session_wait
WHERE
    event NOT LIKE 'SQL*Net%'
    AND event NOT LIKE 'rdbms%message%'
    AND username IS NOT NULL
ORDER BY
    wait_seconds DESC;

识别阻塞会话 ​

使用 V$SESSION_BLOCKERS 视图 (12c+) ​

sql
SELECT
    blocker_sid,
    blocked_sid,
    blocking_session_status,
    wait_event_text
FROM
    v$session_blockers
ORDER BY
    blocker_sid;

使用阻塞链查询 ​

sql
SELECT
    LEVEL,
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.status,
    s.event,
    s.wait_class
FROM
    v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
    AND s.username IS NOT NULL
    AND s.status = 'ACTIVE';

死锁处理 ​

死锁的产生原因 ​

死锁是指两个或多个会话相互等待对方释放锁资源，导致所有会话无法继续执行的情况。常见原因：

  • 事务设计不合理，持有锁的时间过长
  • 应用程序并发访问同一组资源的顺序不一致
  • 缺少适当的索引导致全表扫描
  • 长事务未及时提交或回滚

查看死锁信息 ​

查看最近的死锁日志 ​

sql
SELECT
    *
FROM
    v$diag_info
WHERE
    name = 'Default Trace File';

分析死锁跟踪文件 ​

使用 tkprof 工具分析死锁跟踪文件：

bash
tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_1234.trc /tmp/deadlock_analysis.txt sys=no

使用 DBA_HIST_ENQUEUE_STAT 视图查看历史死锁情况 ​

sql
SELECT
    sample_time,
    event,
    username,
    sql_id,
    session_id,
    blocking_session,
    wait_time_micro / 1000000 AS wait_seconds
FROM
    dba_hist_active_sess_history
WHERE
    event LIKE '%deadlock%'
ORDER BY
    sample_time DESC;

解决死锁问题 ​

  1. 识别死锁会话：使用上述查询找到死锁的会话 ID
  2. 终止阻塞会话：
sql
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
  1. 分析死锁原因：
    • 检查应用程序逻辑，确保资源访问顺序一致
    • 添加适当的索引减少锁竞争
    • 优化长事务，减少锁持有时间
    • 使用 FOR UPDATE NOWAIT 或 FOR UPDATE WAIT n 避免长时间等待

等待事件详细分析 ​

常见 I/O 等待事件 ​

db file sequential read ​

  • 含义：单个数据块的顺序读取
  • 常见原因：索引扫描、回滚段读取、排序段读取
  • 优化方法：
    • 优化 SQL 语句，减少逻辑读
    • 调整索引设计
    • 增加数据缓存大小
    • 优化存储系统性能

db file scattered read ​

  • 含义：多个数据块的分散读取，通常用于全表扫描
  • 常见原因：全表扫描、索引快速全扫描
  • 优化方法：
    • 添加适当的索引
    • 优化 SQL 语句，避免不必要的全表扫描
    • 调整 db_file_multiblock_read_count 参数
    • 考虑分区表设计

log file sync ​

  • 含义：会话等待 redo 日志缓冲区的内容写入磁盘
  • 常见原因：频繁的提交操作、 redo 日志文件性能差
  • 优化方法：
    • 减少不必要的提交操作
    • 优化 redo 日志存储（使用高速存储）
    • 增加 redo 日志组和成员数量
    • 调整 commit_write 参数

常见锁定等待事件 ​

enq: TX - row lock contention ​

  • 含义：行级锁竞争
  • 常见原因：多个会话同时修改同一行数据
  • 优化方法：
    • 减少事务持有锁的时间
    • 优化应用程序逻辑，避免热点数据
    • 使用乐观锁定机制
    • 考虑分区表设计，分散热点数据

enq: TX - index contention ​

  • 含义：索引块竞争
  • 常见原因：多个会话同时插入索引的同一叶子块
  • 优化方法：
    • 使用反向索引（对于递增列）
    • 使用分区索引
    • 调整序列缓存大小
    • 考虑使用全局临时表

enq: TM - contention ​

  • 含义：表级锁竞争
  • 常见原因：DDL 操作与 DML 操作冲突、外键约束缺失索引
  • 优化方法：
    • 为外键约束添加索引
    • 避免在高峰时段执行 DDL 操作
    • 使用 ON DELETE CASCADE 或 ON DELETE SET NULL 选项

常见并发等待事件 ​

latch free ​

  • 含义：等待获取闩锁
  • 常见原因：内存结构竞争、闩锁持有时间过长
  • 优化方法：
    • 调整共享池大小
    • 优化 SQL 语句，减少硬解析
    • 调整闩锁相关参数
    • 使用绑定变量

library cache pin ​

  • 含义：等待获取库缓存钉扎
  • 常见原因：SQL 语句解析竞争、PL/SQL 程序编译
  • 优化方法：
    • 使用绑定变量减少硬解析
    • 避免频繁编译 PL/SQL 程序
    • 调整共享池大小

性能优化策略 ​

锁优化策略 ​

  1. 减少锁持有时间：

    • 尽量缩短事务长度
    • 避免在事务中执行不必要的操作
    • 及时提交或回滚事务

  2. 优化锁粒度：

    • 使用行级锁代替表级锁
    • 合理设计表结构和索引
    • 考虑使用分区表

  3. 避免锁竞争：

    • 避免热点数据
    • 实现合理的并发控制机制
    • 使用乐观锁定

  4. 优化应用程序逻辑：

    • 确保资源访问顺序一致
    • 避免长事务
    • 实现适当的超时机制

等待事件优化策略 ​

  1. I/O 等待优化：

    • 优化存储系统性能
    • 调整数据库参数（如 db_cache_size、db_file_multiblock_read_count）
    • 优化 SQL 语句，减少 I/O 操作
    • 考虑使用 SSD 存储

  2. 锁定等待优化：

    • 优化锁设计
    • 减少锁持有时间
    • 避免锁竞争

  3. 并发等待优化：

    • 调整共享池大小
    • 使用绑定变量
    • 优化 SQL 语句，减少硬解析

19c 和 21c 版本差异 ​

锁等待诊断增强 ​

Oracle 19c 增强 ​

  • 引入了 V$SESSION_BLOCKERS 视图，更直观地显示阻塞关系
  • 增强了 V$SQL_MONITOR 视图，提供更详细的锁等待信息
  • 改进了 AWR 报告中的锁等待分析

Oracle 21c 增强 ​

  • 引入了 DBMS_LOCK_MONITOR 包，提供更强大的锁监控功能
  • 增强了自动诊断功能，能够自动检测和报告锁相关问题
  • 改进了死锁检测算法，减少死锁发生的可能性
  • 引入了 V$LOCK_HISTORY 视图，提供锁历史信息

等待事件新特性 ​

Oracle 19c 新等待事件 ​

  • PGA memory operation：PGA 内存操作等待
  • gc current block 3-way：RAC 环境下的 3 路当前块等待

Oracle 21c 新等待事件 ​

  • buffer cache scan：缓冲区缓存扫描等待
  • direct path read temp (clustered)：集群环境下的临时表空间直接路径读取
  • enq: UL - contention：统一审计锁竞争

生产环境最佳实践 ​

监控与告警配置 ​

  1. 设置锁等待告警阈值：

    • 对于 enq: TX - row lock contention，设置等待时间超过 60 秒告警
    • 对于阻塞会话，设置阻塞链长度超过 3 级告警
    • 对于死锁，设置立即告警

  2. 定期分析锁等待情况：

    • 每天生成锁等待报告
    • 每周分析锁等待趋势
    • 每月进行一次全面的锁优化评估

  3. 实现自动诊断：

    • 使用 Oracle 自动诊断库 (ADR) 监控锁相关问题
    • 配置自动通知机制，及时发现和处理锁问题

预防锁问题的最佳实践 ​

  1. 应用程序设计：

    • 实现合理的事务设计
    • 使用绑定变量
    • 确保资源访问顺序一致
    • 实现适当的超时机制

  2. 数据库设计：

    • 为外键约束添加索引
    • 合理设计表和索引结构
    • 考虑使用分区表
    • 避免使用不必要的唯一约束

  3. 运维管理：

    • 定期收集统计信息
    • 定期检查和优化长事务
    • 避免在高峰时段执行 DDL 操作
    • 监控并优化锁等待情况

常见问题 (FAQ) ​

如何快速识别阻塞会话？ ​

使用以下查询可以快速识别阻塞会话：

sql
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.status,
    s.event,
    s.wait_class,
    s.blocking_session
FROM
    v$session s
WHERE
    s.blocking_session IS NOT NULL
    OR s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
ORDER BY
    s.blocking_session NULLS FIRST;

如何查看锁等待的具体 SQL 语句？ ​

sql
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    s.event,
    s.sql_id,
    t.sql_text
FROM
    v$session s
    LEFT JOIN v$sql t ON s.sql_id = t.sql_id
WHERE
    s.status = 'ACTIVE'
    AND s.username IS NOT NULL
    AND s.wait_class != 'Idle'
ORDER BY
    s.sid;

如何避免死锁？ ​

  • 确保应用程序访问资源的顺序一致
  • 减少事务持有锁的时间
  • 实现适当的超时机制
  • 为外键约束添加索引
  • 避免长事务
  • 使用乐观锁定机制

如何优化 enq: TX - row lock contention 等待事件？ ​

  • 优化应用程序逻辑，避免热点数据
  • 增加适当的索引，减少锁竞争
  • 调整事务隔离级别
  • 使用 FOR UPDATE NOWAIT 或 FOR UPDATE WAIT n 避免长时间等待
  • 考虑使用分区表设计，分散热点数据

如何分析锁等待历史数据？ ​

使用 AWR 报告或 DBA_HIST_ACTIVE_SESS_HISTORY 视图查看历史锁等待情况：

sql
SELECT
    sample_time,
    event,
    username,
    sql_id,
    session_id,
    blocking_session,
    wait_time_micro / 1000000 AS wait_seconds
FROM
    dba_hist_active_sess_history
WHERE
    event LIKE '%lock%'
    OR event LIKE '%enq%'
ORDER BY
    sample_time DESC;

19c 和 21c 在锁管理方面有什么主要区别？ ​

  • Oracle 21c 引入了更强大的锁监控功能，如 DBMS_LOCK_MONITOR 包
  • 21c 增强了自动诊断功能，能够自动检测和报告锁相关问题
  • 21c 改进了死锁检测算法，减少死锁发生的可能性
  • 21c 引入了 V$LOCK_HISTORY 视图，提供锁历史信息
  • 21c 增加了新的等待事件，如统一审计锁竞争

总结 ​

锁等待与等待事件分析是 Oracle 数据库性能优化的重要组成部分。DBA 需要掌握锁的基本概念、等待事件的分类和诊断方法，能够识别和解决锁相关问题。在实际生产环境中，应建立完善的监控和告警机制，定期分析锁等待情况，采取有效的优化策略，预防锁问题的发生。

随着 Oracle 版本的升级，锁管理和等待事件诊断功能不断增强，DBA 应充分利用这些新特性，提高数据库的性能和可靠性。通过合理的设计、优化和管理，可以有效减少锁等待和等待事件对数据库性能的影响，确保数据库系统的稳定运行。