外观
Oracle 死锁与锁等待
锁的基本概念
什么是数据库锁
数据库锁是数据库管理系统用于控制并发访问、保护数据一致性的机制。当多个用户同时访问相同数据时，锁确保数据的完整性和一致性。
Oracle锁类型
| 锁类型 | 描述 | 保护对象 |
|---|---|---|
| DML锁 | 数据操纵语言锁 | 表、行数据 |
| DDL锁 | 数据定义语言锁 | 数据库对象结构 |
| 内部锁 | 系统内部使用的锁 | 内存结构、文件 |
DML锁细分类
| 锁模式 | 描述 | 兼容性 |
|---|---|---|
| 共享锁 (S) | 允许读取但不允许修改 | 与其他共享锁兼容 |
| 排他锁 (X) | 完全独占，不允许其他锁 | 与任何锁都不兼容 |
| 行级共享锁 (RS) | 表级锁，允许行级共享 | 与大多数锁兼容 |
| 行级排他锁 (RX) | 表级锁，允许行级修改 | 与共享锁兼容，与排他锁不兼容 |
| 共享行级排他锁 (SRX) | 表级锁，用于某些DDL操作 | 与共享锁兼容，与修改操作不兼容 |
死锁的产生原因
死锁的定义
死锁是指两个或多个会话相互等待对方持有的锁，导致所有会话都无法继续执行的状态。
死锁产生的必要条件
- 互斥条件：资源不能被共享，一次只能被一个进程使用
- 请求与保持条件：进程已获得的资源在未使用完之前不能被剥夺
- 不剥夺条件：进程已获得的资源在未使用完之前不能被其他进程强行剥夺
- 循环等待条件：若干进程之间形成头尾相接的循环等待资源关系
常见的死锁场景
1. 交叉更新
sql
-- 会话1
UPDATE table1 SET column1 = value WHERE id = 1;
UPDATE table2 SET column1 = value WHERE id = 1;
-- 会话2
UPDATE table2 SET column1 = value WHERE id = 1;
UPDATE table1 SET column1 = value WHERE id = 1;2. 外键约束
- 父表与子表之间的并发操作
- 未索引的外键列
3. 位图索引
- 位图索引在DML操作时会锁定更大范围的数据
- 多个会话同时更新位图索引列
4. 表级锁与行级锁冲突
- 一个会话持有表级锁
- 另一个会话尝试获取同一表的行级锁
锁等待的原因
什么是锁等待
锁等待是指一个会话需要获取某个资源的锁，但该资源已被另一个会话锁定，导致该会话必须等待直到锁被释放。
锁等待的常见原因
| 原因 | 描述 | 影响程度 |
|---|---|---|
| 长事务 | 事务执行时间过长，持有锁时间过长 | 高 |
| 未提交事务 | 事务执行完成但未提交，锁未释放 | 高 |
| 锁升级 | 从行级锁升级为表级锁 | 高 |
| 全表扫描 | 未使用索引，导致锁定更多行 | 中 |
| 缺少索引 | 外键列或查询条件缺少索引 | 中 |
| 并发度过高 | 系统并发访问量过大 | 中 |
| 应用设计问题 | 应用程序逻辑导致的锁竞争 | 高 |
锁等待与死锁的区别
| 特征 | 锁等待 | 死锁 |
|---|---|---|
| 会话状态 | 一个会话等待另一个会话 | 多个会话相互等待 |
| 解决方式 | 等待持有锁的会话完成 | 需要外力介入解决 |
| 系统影响 | 单个会话受阻 | 多个会话受阻，可能影响系统 |
| 自动检测 | Oracle会监控但不自动解决 | Oracle会自动检测并解决 |
死锁与锁等待的检测
1. 死锁检测
Oracle自动检测
- Oracle数据库会自动检测死锁
- 检测到死锁后，Oracle会选择一个会话作为牺牲品并终止其事务
- 被终止的会话会收到 ORA-00060 错误
查看死锁信息
sql
-- 查看当前锁情况
SELECT * FROM v$lock;
-- 查看阻塞情况
SELECT * FROM v$lock WHERE block = 1;
-- 查看死锁历史
SELECT * FROM dba_hist_deadlock;2. 锁等待检测
查看等待会话
sql
SELECT s.sid, s.serial#, s.username, s.program,
s.wait_class, s.seconds_in_wait,
s.blocking_session,
sw.event
FROM v$session s,
v$session_wait sw
WHERE s.sid = sw.sid
AND s.status = 'ACTIVE'
AND s.seconds_in_wait > 0
AND s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;查看锁持有情况
sql
SELECT l.sid, l.type, l.id1, l.id2, l.lmode, l.request,
s.username, s.program
FROM v$lock l,
v$session s
WHERE l.sid = s.sid
AND l.type IN ('TM', 'TX')
ORDER BY l.sid;查看阻塞链
sql
SELECT blocker.sid AS blocker_sid,
blocker.username AS blocker_user,
blocked.sid AS blocked_sid,
blocked.username AS blocked_user,
blocked.program AS blocked_program,
blocked.seconds_in_wait AS wait_seconds
FROM v$session blocker,
v$session blocked
WHERE blocker.sid IN (
SELECT blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL
)
AND blocked.blocking_session = blocker.sid
ORDER BY wait_seconds DESC;死锁的处理方法
1. Oracle自动处理
- Oracle数据库会自动检测死锁
- 检测到死锁后，Oracle会选择一个牺牲会话
- 牺牲会话会收到 ORA-00060 错误："Deadlock detected. More info in file <trace file>"
2. 手动处理死锁
步骤1：识别死锁会话
sql
-- 查找阻塞会话
SELECT sid, blocking_session, username, program,
seconds_in_wait, event
FROM v$session
WHERE blocking_session IS NOT NULL
OR sid IN (
SELECT blocking_session
FROM v$session
WHERE blocking_session IS NOT NULL
);步骤2：分析死锁原因
- 查看死锁跟踪文件
- 分析会话正在执行的SQL
sql
SELECT s.sid, s.username, s.sql_id,
t.sql_text
FROM v$session s,
v$sql t
WHERE s.sid IN (<deadlock_sids>)
AND s.sql_id = t.sql_id;步骤3：终止死锁会话
sql
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;3. 死锁处理的最佳实践
- 优先选择：让Oracle自动处理死锁
- 手动干预：仅当自动处理失败或需要快速恢复时
- 分析原因：处理死锁后，必须分析根本原因
- 记录信息：记录死锁发生的时间、原因和处理方法
- 预防措施：根据分析结果实施预防措施
锁等待的处理方法
1. 短时间锁等待的处理
- 等待：对于短时间的锁等待，通常等待持有锁的会话完成即可
- 监控：设置合理的等待时间阈值，超过阈值才进行干预
2. 长时间锁等待的处理
步骤1：识别阻塞会话
sql
SELECT s.sid, s.serial#, s.username, s.program,
s.sql_id, s.seconds_in_wait,
blocking_session
FROM v$session s
WHERE s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;步骤2：分析阻塞原因
sql
-- 查看阻塞会话正在执行的SQL
SELECT s.sid, s.username, t.sql_text
FROM v$session s,
v$sql t
WHERE s.sid = <blocking_sid>
AND s.sql_id = t.sql_id;
-- 查看阻塞会话的事务状态
SELECT * FROM v$transaction
WHERE ses_addr IN (
SELECT saddr FROM v$session WHERE sid = <blocking_sid>
);步骤3：处理阻塞会话
- 联系用户：如果是合法的长时间操作，联系用户确认
- 终止会话：如果是异常会话，终止它
sql
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;3. 锁等待处理的最佳实践
- 区分类型：区分正常的业务操作和异常的锁等待
- 设置阈值：根据业务特点设置合理的等待时间阈值
- 自动处理：对于超过阈值的锁等待，考虑自动处理
- 通知机制：建立锁等待的通知机制，及时通知相关人员
死锁与锁等待的预防
1. 应用设计层面
事务设计
- 缩短事务时间：尽量减少事务持有锁的时间
- 合理的事务范围：一个事务只包含必要的操作
- 批量处理优化：对于大批量操作，考虑分批处理
- 提交策略：及时提交或回滚事务，避免长时间未提交
并发控制
- 统一访问顺序：所有会话以相同的顺序访问表和数据
- 避免循环依赖：设计时避免表之间的循环依赖关系
- 使用乐观锁：对于高并发场景，考虑使用乐观锁
- 合理的隔离级别：根据业务需求选择合适的事务隔离级别
索引优化
- 为外键列创建索引：避免级联锁定
- 优化查询条件：确保查询使用索引，减少锁定范围
- 覆盖索引：使用覆盖索引减少回表操作
- 位图索引谨慎使用：在高并发更新场景中避免使用位图索引
2. 数据库配置层面
锁相关参数
| 参数 | 描述 | 建议值 |
|---|---|---|
| ddl_lock_timeout | DDL操作等待DML锁的时间 | 根据业务需求设置，默认0（不等待） |
| distributed_lock_timeout | 分布式事务锁超时时间 | 默认60秒，根据网络情况调整 |
| enqueue_resources | 队列资源数量 | 自动管理，通常不需要手动设置 |
| enqueue_hash_buckets | 队列哈希桶数量 | 自动管理，通常不需要手动设置 |
事务相关参数
| 参数 | 描述 | 建议值 |
|---|---|---|
| transactions | 最大并发事务数 | 自动管理，通常不需要手动设置 |
| transactions_per_rollback_segment | 每个回滚段的事务数 | 自动管理，通常不需要手动设置 |
3. 运维管理层面
监控与告警
- 建立监控：监控锁等待和死锁情况
- 设置告警：为异常锁等待设置告警
- 定期分析：定期分析锁等待和死锁的趋势
- 历史数据：保存锁相关的历史数据，用于趋势分析
定期维护
- 统计信息收集：定期收集统计信息，确保执行计划优化
- 索引维护：定期重建或重组索引
- 空间管理：确保表空间有足够空间，避免因空间不足导致的锁问题
- 回滚段管理：确保回滚段配置合理
培训与规范
- 开发人员培训：培训开发人员了解锁机制和最佳实践
- 制定规范：制定数据库访问的编码规范
- 代码审查：在代码审查中关注锁相关问题
- 案例分享：分享死锁和锁等待的案例分析
常见的死锁场景及解决方案
1. 交叉更新死锁
场景描述
两个会话以不同的顺序更新相同的表
sql
-- 会话1
UPDATE table1 SET col1 = 'A' WHERE id = 1;
UPDATE table2 SET col1 = 'B' WHERE id = 1;
-- 会话2
UPDATE table2 SET col1 = 'C' WHERE id = 1;
UPDATE table1 SET col1 = 'D' WHERE id = 1;解决方案
- 统一更新顺序：所有会话以相同的顺序更新表
- 使用批量更新：将多个更新操作合并为一个批量操作
- 缩短事务：减少事务中包含的操作数
2. 外键死锁
场景描述
子表与父表之间的操作顺序问题，特别是当外键列没有索引时
解决方案
- 为外键列创建索引：必须为所有外键列创建索引
- 统一操作顺序：先操作父表，再操作子表
- 批量操作：批量处理相关数据
3. 位图索引死锁
场景描述
在高并发更新场景中使用位图索引
解决方案
- 避免使用位图索引：在高并发更新场景中使用B树索引
- 使用函数索引：对于低基数列，考虑使用函数索引
- 分区表：对于大型表，考虑使用分区表
4. 长事务死锁
场景描述
事务执行时间过长，持有锁时间过长
解决方案
- 拆分事务：将长事务拆分为多个短事务
- 使用批量提交：对于大批量操作，使用批量提交
- 异步处理：将非关键操作改为异步处理
- 合理的隔离级别：根据业务需求选择合适的隔离级别
死锁与锁等待的监控工具
1. Oracle内置工具
Oracle Enterprise Manager (OEM)
- 性能页面：查看当前会话等待情况
- 锁页面：查看详细的锁信息
- 告警设置：设置锁相关的告警
- 历史报告：查看锁等待和死锁的历史趋势
AWR报告
- Top 5 Timed Events：查看锁等待在等待事件中的占比
- Segments by Row Lock Waits：查看行锁等待最多的段
- SQL Statistics：查看导致锁等待的SQL语句
2. 自定义监控脚本
锁等待监控脚本
sql
-- lock_monitor.sql
SET LINESIZE 200
SET PAGESIZE 100
COLUMN username FORMAT A15
COLUMN program FORMAT A30
COLUMN event FORMAT A30
SELECT s.sid, s.serial#, s.username, s.program,
s.blocking_session,
s.seconds_in_wait,
sw.event
FROM v$session s,
v$session_wait sw
WHERE s.sid = sw.sid
AND s.status = 'ACTIVE'
AND s.seconds_in_wait > 0
AND s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;死锁监控脚本
sql
-- deadlock_monitor.sql
SET LINESIZE 200
SET PAGESIZE 100
SELECT l.sid, l.type, l.id1, l.id2, l.lmode, l.request,
l.block,
s.username, s.program
FROM v$lock l,
v$session s
WHERE l.sid = s.sid
AND (l.block = 1 OR l.request > 0)
ORDER BY l.block DESC, l.request DESC;3. 第三方监控工具
- Quest Spotlight on Oracle：提供详细的锁分析功能
- SolarWinds Database Performance Monitor：实时监控锁情况
- IBM InfoSphere Optim Performance Manager：提供全面的性能监控
- Datadog Database Monitoring：云环境下的数据库监控
性能优化建议
1. 索引优化
- 为外键列创建索引：避免级联锁定
- 为常用查询条件创建索引：减少全表扫描
- 合理使用唯一索引：提高数据访问效率
- 避免过度索引：索引过多会增加锁竞争
2. SQL优化
- 使用绑定变量：减少硬解析，提高并发性能
- 优化WHERE子句：确保查询使用索引
- 避免SELECT FOR UPDATE：除非必要，否则避免使用
- 使用ROWID：对于精确更新，使用ROWID定位
3. 事务优化
- 最小化事务范围：只包含必要的操作
- 及时提交：操作完成后及时提交
- 使用自治事务：对于日志记录等操作，使用自治事务
- 合理的隔离级别：根据业务需求选择合适的隔离级别
4. 应用优化
- 连接池管理：合理配置连接池参数
- 并发控制：实现应用级的并发控制
- 批量处理：对于大批量操作，实现分批处理
- 异步处理：将非实时操作改为异步处理
常见问题（FAQ）
Q1: Oracle数据库会自动处理死锁吗？
A1: 是的，Oracle数据库会自动检测和处理死锁。当检测到死锁时，Oracle会选择一个会话作为牺牲品并终止其事务，被终止的会话会收到 ORA-00060 错误。
Q2: 如何查看当前系统中的锁等待情况？
A2: 使用以下SQL语句查看锁等待情况：
sql
SELECT s.sid, s.serial#, s.username, s.program,
s.blocking_session,
s.seconds_in_wait,
sw.event
FROM v$session s,
v$session_wait sw
WHERE s.sid = sw.sid
AND s.status = 'ACTIVE'
AND s.seconds_in_wait > 0
AND s.blocking_session IS NOT NULL
ORDER BY s.seconds_in_wait DESC;Q3: 如何识别导致锁等待的SQL语句？
A3: 使用以下SQL语句识别导致锁等待的SQL：
sql
-- 查看阻塞会话正在执行的SQL
SELECT s.sid, s.username, t.sql_text
FROM v$session s,
v$sql t
WHERE s.sid = <blocking_sid>
AND s.sql_id = t.sql_id;
-- 查看被阻塞会话正在执行的SQL
SELECT s.sid, s.username, t.sql_text
FROM v$session s,
v$sql t
WHERE s.sid = <blocked_sid>
AND s.sql_id = t.sql_id;Q4: 外键列为什么需要创建索引？
A4: 外键列创建索引的原因：
- 避免级联锁定：当父表被更新或删除时，子表会被锁定
- 提高查询性能：外键通常用于连接查询
- 减少死锁风险：避免因外键检查导致的锁竞争
- 维护引用完整性：确保外键约束的高效检查
Q5: 如何避免交叉更新死锁？
A5: 避免交叉更新死锁的方法：
- 统一更新顺序：所有会话以相同的顺序更新表
- 使用批量更新：将多个更新操作合并为一个批量操作
- 缩短事务时间：减少事务持有锁的时间
- 使用表级锁：对于复杂的多表操作，考虑使用表级锁
Q6: 锁升级是什么？如何避免？
A6: 锁升级是指Oracle将多个行级锁升级为表级锁的过程。这通常发生在：
- 一个事务锁定了表中大部分行
- 系统认为表级锁比多个行级锁更高效
避免锁升级的方法：
- 优化SQL：使用更精确的WHERE条件，减少锁定的行数
- 增加初始化参数：适当增加_optimizer_max_permutations参数
- 使用ROWID：对于精确更新，使用ROWID定位
- 分批处理：将大批量操作拆分为小批量
Q7: 长时间运行的查询会导致锁等待吗？
A7: 是的，长时间运行的查询可能会导致锁等待：
- 共享锁：SELECT语句会持有共享锁
- 行级锁：如果使用了FOR UPDATE子句，会持有行级排他锁
- 表级锁：某些查询可能需要表级锁
解决方法：
- 优化查询：提高查询性能，减少执行时间
- 使用并行查询：对于大型查询，考虑使用并行执行
- 避免在高峰期执行：将大型查询安排在系统负载低的时候
Q8: 如何处理锁等待超时？
A8: 处理锁等待超时的方法：
设置合理的超时参数：
sqlALTER SYSTEM SET ddl_lock_timeout = 60;应用层面处理：在应用程序中设置SQL执行超时
监控和干预：
- 监控锁等待情况
- 对于长时间的锁等待，及时干预
- 分析锁等待的根本原因
Q9: 死锁和锁等待会影响数据库性能吗？
A9: 是的，死锁和锁等待会影响数据库性能：
- 资源浪费：等待的会话占用系统资源但不做有效工作
- 系统负载增加：锁竞争会增加系统的负载
- 响应时间延长：用户操作的响应时间会变长
- 系统吞吐量下降：并发处理能力会下降
严重的锁问题可能导致系统性能急剧下降，甚至系统不可用。
Q10: 如何建立锁相关的监控和告警机制？
A10: 建立锁相关监控和告警机制的步骤：
确定监控指标：
- 锁等待时间
- 阻塞会话数量
- 死锁发生频率
- 锁等待的SQL语句
设置监控工具：
- 使用Oracle Enterprise Manager
- 开发自定义监控脚本
- 使用第三方监控工具
设置合理的告警阈值：
- 锁等待时间超过30分钟
- 阻塞会话数量超过5个
- 死锁在短时间内频繁发生
建立处理流程：
- 告警通知机制
- 问题处理流程
- 事后分析和预防措施
定期审查和优化：
- 定期审查监控效果
- 调整告警阈值
- 优化监控策略
通过有效的监控和告警机制，可以及时发现和解决锁相关问题，保障系统的稳定运行。