印刻数据库

外观

Oracle 锁优化 ​

锁优化概述 ​

锁是数据库并发控制的重要机制，但不当的锁使用会导致性能问题、死锁和系统吞吐量下降。Oracle 锁优化的目标是在确保数据一致性的同时，最大限度地减少锁竞争，提高系统并发性能。

锁优化的重要性 ​

  • 减少锁等待时间，提高事务响应速度
  • 避免死锁，确保系统稳定运行
  • 提高系统吞吐量，支持更多并发用户
  • 优化资源利用率，减少系统开销

锁优化的主要方向 ​

  • 锁类型选择
  • 锁粒度调整
  • 锁持有时间优化
  • 死锁预防与处理
  • 锁等待监控与分析

Oracle 锁类型与机制 ​

锁类型分类 ​

Oracle 数据库中的锁可以按照不同维度进行分类：

按锁的粒度分类 ​

锁粒度描述优点缺点
行级锁锁定单个数据行并发度高管理开销大
块级锁锁定数据块平衡并发与开销粒度较大
表级锁锁定整个表管理简单并发度低
分区级锁锁定表分区适合分区表仅适用于分区表

按锁的模式分类 ​

锁模式描述兼容性
共享锁（S）允许并发读，阻止写与 S、RS 兼容
排他锁（X）阻止所有并发访问仅与 None 兼容
行共享锁（RS）允许其他事务并发访问与 S、RS、RX 兼容
行排他锁（RX）允许并发读，阻止排他访问与 S、RS、RX 兼容
共享行排他锁（SRX）限制并发写操作与 S、RS 兼容

锁机制实现 ​

Oracle 锁机制具有以下特点：

  • 自动锁管理：大多数情况下，Oracle 自动管理锁的获取和释放
  • 无锁升级：Oracle 不会将行级锁升级为表级锁
  • 多版本并发控制：通过 MVCC 减少锁竞争
  • 死锁检测：自动检测并解决死锁

锁等待监控与分析 ​

监控锁等待的视图 ​

Oracle 提供了多个视图用于监控锁等待情况：

  • v$lock：显示当前所有锁的信息
  • v$session：显示会话信息，包括锁等待状态
  • v$session_blockers：显示阻塞会话关系
  • v$lock_wait：显示锁等待详细信息
  • dba_blockers：显示阻塞其他会话的会话
  • dba_waiters：显示正在等待锁的会话

锁等待分析方法 ​

查看阻塞会话 ​

sql
-- 查看当前阻塞会话
SELECT 
  blocker.sid AS blocker_sid,
  blocker.username AS blocker_user,
  blocker.program AS blocker_program,
  waiter.sid AS waiter_sid,
  waiter.username AS waiter_user,
  waiter.program AS waiter_program,
  lock_type.type AS lock_type,
  lock_type.lmode AS lock_mode,
  lock_type.request AS request_mode
FROM 
  v$session blocker,
  v$session waiter,
  v$lock lock_type
WHERE 
  blocker.sid = lock_type.sid
  AND lock_type.block = 1
  AND waiter.lockwait = lock_type.kaddr;

查看锁等待历史 ​

sql
-- 查询 AWR 报告中的锁等待信息
SELECT 
  event,
  total_waits,
  total_timeouts,
  time_waited / 100 AS time_waited_seconds,
  avg_wait / 100 AS avg_wait_seconds
FROM 
  dba_hist_system_event
WHERE 
  event LIKE '%enq%' 
  AND snap_id IN (SELECT snap_id FROM dba_hist_snapshot WHERE begin_interval_time > SYSDATE - 7)
ORDER BY 
  time_waited DESC;

锁等待事件分析 ​

常见的锁等待事件包括：

  • enq: TX - row lock contention：行锁竞争
  • enq: TM - contention：表级锁竞争
  • enq: HW - contention：高水位线锁竞争
  • enq: ST - contention：空间管理锁竞争
  • enq: TX - index contention：索引锁竞争

死锁预防与处理 ​

死锁产生的原因 ​

死锁通常由以下原因导致：

  1. 循环等待：事务 A 等待事务 B 的锁，事务 B 等待事务 A 的锁
  2. 长事务：事务持有锁时间过长
  3. 不合理的锁粒度：使用过大的锁粒度
  4. 并发写入热点：多个事务同时写入同一数据
  5. 索引设计不合理：导致全表扫描或大范围锁定

死锁预防策略 ​

1. 统一资源访问顺序 ​

sql
-- 错误方式：不同事务访问资源顺序不一致
-- 事务 1
UPDATE table1 SET ... WHERE id = 1;
UPDATE table2 SET ... WHERE id = 1;

-- 事务 2  
UPDATE table2 SET ... WHERE id = 1;
UPDATE table1 SET ... WHERE id = 1;

-- 正确方式：统一资源访问顺序
-- 事务 1 和事务 2 都按相同顺序访问资源
UPDATE table1 SET ... WHERE id = 1;
UPDATE table2 SET ... WHERE id = 1;

2. 减少锁持有时间 ​

  • 尽量缩短事务持续时间
  • 避免在事务中进行外部调用
  • 避免在事务中等待用户输入
  • 使用批量提交减少事务数量

3. 使用合适的锁粒度 ​

  • 优先使用行级锁
  • 避免使用表级锁
  • 合理设计分区表，利用分区级锁

4. 优化索引设计 ​

  • 确保查询使用索引，减少锁定范围
  • 避免全表扫描
  • 优化索引结构，减少索引锁竞争

死锁处理方法 ​

1. 死锁检测 ​

Oracle 自动检测死锁，并通过回滚其中一个事务来解决死锁。可以通过以下方式查看死锁信息：

sql
-- 查看死锁信息
SELECT * FROM v$deadlock;

-- 查看死锁历史
SELECT * FROM dba_hist_deadlock;

2. 手动处理阻塞会话 ​

sql
-- 查找阻塞会话
SELECT sid, serial#, username, program 
FROM v$session 
WHERE sid IN (SELECT blocker_sid FROM v$session_blockers);

-- 终止阻塞会话
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

锁优化策略 ​

应用设计层面优化 ​

1. 选择合适的并发控制策略 ​

  • 读多写少场景：使用乐观锁
  • 高并发写场景：使用悲观锁
  • 关键业务数据：使用悲观锁

2. 优化事务设计 ​

  • 采用短事务设计
  • 避免大事务
  • 合理设置事务边界
  • 使用批量操作减少事务数量

3. 优化 SQL 语句 ​

  • 避免全表扫描
  • 优化 WHERE 子句，减少锁定范围
  • 避免不必要的 UPDATE 和 DELETE 操作
  • 使用 FOR UPDATE NOWAIT 避免无限等待

数据库层面优化 ​

1. 调整初始化参数 ​

参数描述建议值
undo_retention撤销数据保留时间根据业务需求调整，一般建议 900-1800 秒
_enable_table_locks是否启用表级锁默认值为 TRUE，可根据需要调整
_row_locking行锁模式默认值为 ALWAYS，建议保持默认

2. 优化表设计 ​

  • 使用分区表，利用分区级锁
  • 合理设计表结构，避免过度规范化
  • 使用合适的存储参数

3. 优化索引设计 ​

  • 创建合适的索引，减少锁定范围
  • 避免过度索引，减少索引维护开销
  • 优化索引结构，减少索引锁竞争

不同 Oracle 版本的锁优化特性 ​

版本锁优化特性
11g增强的锁监控视图、自动死锁检测
12c多租户环境下的锁隔离、自适应执行计划
19c实时统计信息、自动索引优化
21c混合分区表、增强的并行处理

锁优化最佳实践 ​

开发阶段最佳实践 ​

  1. 设计合理的并发控制策略：根据业务场景选择合适的锁机制
  2. 优化事务设计：采用短事务，避免大事务
  3. 优化 SQL 语句：确保使用索引，减少锁定范围
  4. 统一资源访问顺序：避免循环等待
  5. 使用合适的锁粒度：优先使用行级锁
  6. 添加适当的索引：减少锁定范围
  7. 避免在事务中进行外部调用：减少锁持有时间

测试阶段最佳实践 ​

  1. 进行并发测试：模拟真实并发场景
  2. 监控锁等待情况：及时发现锁竞争问题
  3. 进行死锁测试：确保系统能够处理死锁
  4. 测试不同负载下的性能：评估系统在不同负载下的表现
  5. 优化执行计划：确保执行计划最优

生产阶段最佳实践 ​

  1. 定期监控锁等待：使用 AWR、ASH 报告分析锁等待
  2. 及时处理阻塞会话：避免影响其他用户
  3. 优化数据库参数：根据实际情况调整参数
  4. 定期收集统计信息：确保执行计划准确
  5. 进行性能调优：持续优化系统性能
  6. 制定死锁处理流程：明确死锁处理步骤

锁优化案例分析 ​

案例 1：行锁竞争优化 ​

问题描述 ​

应用系统中频繁出现 enq: TX - row lock contention 等待事件，导致事务响应时间延长。

分析过程 ​

  1. 使用 AWR 报告查看锁等待情况
  2. 分析 SQL 语句，发现频繁更新同一数据行
  3. 查看执行计划，确认使用了正确的索引
  4. 检查事务设计，发现事务持续时间过长

解决方案 ​

  1. 优化事务设计，缩短事务持续时间
  2. 调整业务逻辑，减少对同一数据行的并发写入
  3. 考虑使用乐观锁替代悲观锁
  4. 增加适当的索引，减少锁定范围

案例 2：表级锁竞争优化 ​

问题描述 ​

应用系统中频繁出现 enq: TM - contention 等待事件，导致表级锁竞争严重。

分析过程 ​

  1. 使用 v$lock 视图查看锁信息
  2. 分析 SQL 语句，发现缺少必要的索引
  3. 确认事务中存在全表扫描操作
  4. 检查表设计，发现表结构不合理

解决方案 ​

  1. 添加适当的索引，避免全表扫描
  2. 优化 SQL 语句，减少锁定范围
  3. 调整表结构，优化数据访问模式
  4. 考虑使用分区表，利用分区级锁

常见问题（FAQ） ​

Q1: 如何查看当前的锁等待情况？ ​

A: 可以通过查询 v$lock、v$session、v$session_blockers 等视图查看当前的锁等待情况。例如：

sql
SELECT 
  blocker.sid AS blocker_sid,
  blocker.username AS blocker_user,
  waiter.sid AS waiter_sid,
  waiter.username AS waiter_user,
  lock_type.type AS lock_type
FROM 
  v$session blocker,
  v$session waiter,
  v$lock lock_type
WHERE 
  blocker.sid = lock_type.sid
  AND lock_type.block = 1
  AND waiter.lockwait = lock_type.kaddr;

Q2: 如何避免死锁？ ​

A: 避免死锁的方法包括：统一资源访问顺序，减少锁持有时间，使用合适的锁粒度，优化索引设计，避免长事务等。

Q3: Oracle 会自动检测和处理死锁吗？ ​

A: 是的，Oracle 会自动检测死锁，并通过回滚其中一个事务来解决死锁。可以通过 v$deadlock 视图查看死锁信息。

Q4: 行级锁和表级锁有什么区别？ ​

A: 行级锁只锁定单个数据行，并发度高但管理开销大；表级锁锁定整个表，管理简单但并发度低。Oracle 优先使用行级锁，只有在必要时才使用表级锁。

Q5: 如何处理阻塞会话？ ​

A: 可以通过以下步骤处理阻塞会话：

  1. 查找阻塞会话：SELECT sid FROM v$session WHERE blocking_session IS NOT NULL;
  2. 查看阻塞会话详情：SELECT sid, serial#, username, program FROM v$session WHERE sid = <blocker_sid>;
  3. 终止阻塞会话：ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Q6: 乐观锁和悲观锁有什么区别？ ​

A: 乐观锁假设冲突很少发生，只在提交时检查数据是否被修改；悲观锁假设冲突总是会发生，在事务开始时就锁定资源。乐观锁适用于读多写少的场景，悲观锁适用于高并发写操作的场景。

Q7: 如何优化索引锁竞争？ ​

A: 优化索引锁竞争的方法包括：设计合理的索引结构，避免过度索引，优化 SQL 语句减少索引锁定范围，使用合适的索引类型等。

Q8: 分区表如何减少锁竞争？ ​

A: 分区表可以将锁的粒度降低到分区级别，多个事务可以同时访问不同的分区，从而减少锁竞争。特别是对于范围分区表，不同的事务可以同时操作不同的分区，互不影响。