外观
Oracle 死锁与锁等待故障处理最佳实践
生产场景案例
案例1：电商系统订单表死锁
背景：某电商平台在大促期间，订单量突增，系统出现大量ORA-00060死锁错误，订单处理延迟严重。
诊断过程：
- 通过告警日志发现大量"ORA-00060: Deadlock detected while waiting for resource"错误
- 查询
v$session_wait发现大量enq: TX - row lock contention等待事件 - 分析
v$lock和v$session视图，发现多个会话互相等待对方持有的订单表行锁 - 查看SQL语句，发现两个不同的订单处理流程以相反的顺序更新订单和订单详情表
解决方案：
- 终止阻塞会话，恢复系统运行
- 修改应用程序，统一订单和订单详情表的更新顺序
- 添加适当的锁提示，减少锁冲突
- 优化事务设计，缩短事务持有锁的时间
结果：系统恢复正常，死锁发生率降低95%以上
案例2：金融系统批量处理锁等待
背景：某银行的夜间批量处理任务长时间运行，导致日间交易出现大量锁等待，影响正常业务。
诊断过程：
- 监控发现日间交易会话等待时间超过30秒
- 查询
v$session_wait发现等待事件为enq: TM - contention - 分析发现夜间批量更新任务持有大量表级锁
- 检查批量任务SQL，发现未使用索引，导致全表扫描和表级锁
解决方案：
- 优化批量任务SQL，添加适当索引，将表级锁改为行级锁
- 调整批量任务执行时间，避开业务高峰期
- 实现任务分片，减少单次锁定的数据量
- 监控批量任务执行情况，设置超时机制
结果：日间交易锁等待时间从30秒降至100毫秒以内
死锁与锁等待概述
在Oracle数据库中，锁是保护共享资源、确保数据一致性和完整性的核心机制。然而，不当的锁使用会导致锁等待和死锁，严重影响数据库性能和可用性。
核心概念
- 锁等待：一个会话等待另一个会话持有的锁释放，是数据库正常并发行为，但过长的等待会影响性能
- 死锁：两个或多个会话互相等待对方持有的锁，形成循环等待，导致所有相关会话无法继续执行
常见症状
- 会话长时间处于等待状态，无法继续执行
- 数据库响应时间显著增加，超出正常范围
- 活跃会话数量异常上升，接近数据库配置上限
- 出现ORA-00060: Deadlock detected while waiting for resource错误
- 锁等待相关等待事件占比增加，如
enq: TX - row lock contention、enq: TM - contention - 应用程序出现超时、500错误或响应缓慢
- 事务回滚增加，业务数据不一致风险提高
锁的类型与模式
理解Oracle锁的类型和模式是诊断和解决锁问题的基础。Oracle使用多种锁类型来保护不同的资源，从数据行到数据库对象结构。
Oracle锁的类型
| 锁类型 | 描述 | 常见场景 | 相关等待事件 |
|---|---|---|---|
| DML锁 | 保护数据不被并发修改，包括行锁和表锁 | INSERT、UPDATE、DELETE操作 | enq: TX - row lock contention、enq: TM - contention |
| DDL锁 | 保护数据库对象的结构完整性 | CREATE、ALTER、DROP操作 | library cache lock、library cache pin |
| 内部锁 | 保护内部数据库结构 | 数据库启动、关闭、备份 | 通常不直接可见 |
| 闩锁（Latches） | 保护内存结构的轻量级锁，用于短期资源保护 | 共享池访问、缓冲区缓存访问 | latch free、cursor: pin S wait on X |
| 互斥锁（Mutexes） | 轻量级锁，用于替代闩锁，提供更好的并发性能 | 高并发环境下的内存访问 | cursor: mutex S、cursor: mutex X |
DML锁的模式
DML锁是最常见的锁类型，DBA需要重点关注。以下是DML锁的主要模式：
| 锁模式 | 描述 | 缩写 | 常见操作 | 兼容性 |
|---|---|---|---|---|
| 行共享（Row Share） | 允许其他会话访问表，防止排他锁 | RS | SELECT ... FOR UPDATE（部分行） | 与S、RS兼容，与X、SRX不兼容 |
| 行排他（Row Exclusive） | 允许其他会话访问表，防止共享锁 | RX | INSERT、UPDATE、DELETE | 与RS、RX兼容，与S、SRX、X不兼容 |
| 共享（Share） | 允许其他会话查询，防止修改 | S | LOCK TABLE ... IN SHARE MODE | 与S、RS兼容，与RX、SRX、X不兼容 |
| 共享行排他（Share Row Exclusive） | 允许查询，防止大多数修改 | SRX | LOCK TABLE ... IN SHARE ROW EXCLUSIVE MODE | 仅与RS兼容，与其他模式不兼容 |
| 排他（Exclusive） | 完全锁定表，防止其他会话访问 | X | LOCK TABLE ... IN EXCLUSIVE MODE | 与任何锁模式都不兼容 |
锁的兼容性矩阵
理解锁的兼容性对于诊断锁冲突至关重要。以下是DML锁模式的兼容性矩阵：
| 请求锁模式 | 现有RS锁 | 现有RX锁 | 现有S锁 | 现有SRX锁 | 现有X锁 |
|---|---|---|---|---|---|
| RS | 兼容 | 兼容 | 兼容 | 不兼容 | 不兼容 |
| RX | 兼容 | 兼容 | 不兼容 | 不兼容 | 不兼容 |
| S | 兼容 | 不兼容 | 兼容 | 不兼容 | 不兼容 |
| SRX | 不兼容 | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
实际应用中的锁行为
- INSERT操作：通常只获取行级RX锁，不锁定整个表
- UPDATE/DELETE操作：先获取行级锁，再执行修改
- SELECT ... FOR UPDATE：获取行级RS锁，防止其他会话获取排他锁
- LOCK TABLE语句：显式获取表级锁，优先级高于隐式锁
- 外键约束：会导致父表获取共享锁，可能引发锁冲突
锁升级机制
Oracle不会自动将行级锁升级为表级锁，这是Oracle的一个重要特性，有助于提高并发性能。但在某些情况下，如全表更新，Oracle可能直接获取表级锁。
示例：
sql
-- 此语句会获取表级RX锁，因为没有WHERE子句，会更新所有行
UPDATE large_table SET status = 'ACTIVE';
-- 此语句通常只获取行级锁，因为有WHERE子句和索引
UPDATE large_table SET status = 'ACTIVE' WHERE id = 123;锁等待的诊断方法
诊断工作流
当遇到锁等待问题时，DBA应该按照以下步骤进行诊断：
- 快速识别锁等待：确认是否存在锁等待，识别阻塞会话和等待会话
- 分析锁等待详情：了解锁的类型、模式和涉及的对象
- 查看相关SQL：分析阻塞和等待会话执行的SQL语句
- 定位根本原因：确定锁冲突的原因，如SQL设计问题、事务过长等
- 制定解决方案：根据分析结果采取适当的解决措施
1. 快速识别锁等待
使用以下脚本快速识别当前的锁等待情况：
sql
-- 脚本1：快速识别锁等待会话
SET LINESIZE 200
SET PAGESIZE 100
-- 查看当前锁等待概览
SELECT '锁等待数量' AS metric, COUNT(*) AS value FROM v$session_wait WHERE blocking_session IS NOT NULL
UNION ALL
SELECT 'TOP等待事件', event FROM (
SELECT event, COUNT(*) AS cnt FROM v$session_wait WHERE blocking_session IS NOT NULL GROUP BY event ORDER BY cnt DESC
) WHERE ROWNUM = 1
UNION ALL
SELECT '平均等待时间(秒)', ROUND(AVG(seconds_in_wait), 1) FROM v$session_wait WHERE blocking_session IS NOT NULL;
-- 查看详细的锁等待信息
SELECT w.sid AS waiting_sid,
w.serial# AS waiting_serial,
s1.username AS waiting_user,
s1.program AS waiting_program,
s1.machine AS waiting_machine,
w.event AS wait_event,
w.seconds_in_wait AS wait_seconds,
b.sid AS blocking_sid,
b.serial# AS blocking_serial,
s2.username AS blocking_user,
s2.program AS blocking_program,
s2.machine AS blocking_machine,
s2.status AS blocking_status,
s2.sql_id AS blocking_sql_id
FROM v$session_wait w
JOIN v$session s1 ON w.sid = s1.sid
JOIN v$session b ON w.blocking_session = b.sid
WHERE w.blocking_session IS NOT NULL
ORDER BY w.seconds_in_wait DESC;2. 分析锁的详细信息
sql
-- 脚本2：查看锁的详细信息
SELECT s.sid,
s.serial#,
s.username,
s.program,
s.machine,
s.status,
DECODE(l.type,
'TM', '表锁',
'TX', '事务锁',
'UL', '用户锁',
'DL', '字典锁',
l.type) AS lock_type,
o.object_name,
o.object_type,
DECODE(l.lmode,
0, 'None',
1, 'Null',
2, '行共享(RS)',
3, '行排他(RX)',
4, '共享(S)',
5, '共享行排他(SRX)',
6, '排他(X)',
l.lmode) AS held_mode,
DECODE(l.request,
0, 'None',
1, 'Null',
2, '行共享(RS)',
3, '行排他(RX)',
4, '共享(S)',
5, '共享行排他(SRX)',
6, '排他(X)',
l.request) AS requested_mode,
l.block AS is_blocking
FROM v$session s
JOIN v$lock l ON s.sid = l.sid
LEFT JOIN dba_objects o ON (l.type = 'TM' AND o.object_id = l.id1) OR (l.type = 'TX' AND o.data_object_id = l.id1)
WHERE l.type IN ('TM', 'TX')
ORDER BY l.block DESC, s.username, s.sid;3. 查看相关SQL语句
sql
-- 脚本3：查看阻塞和等待会话的SQL
SET LONG 20000
-- 查看阻塞会话的SQL
SELECT '阻塞会话SQL' AS info,
b.sid,
b.serial#,
b.username,
DBMS_LOB.SUBSTR(t.sql_text, 1000) AS sql_text
FROM v$session b
JOIN v$sqltext t ON b.sql_id = t.sql_id AND t.piece = 0
WHERE b.sid IN (SELECT DISTINCT blocking_session FROM v$session_wait WHERE blocking_session IS NOT NULL);
-- 查看等待会话的SQL
SELECT '等待会话SQL' AS info,
w.sid,
w.serial#,
w.username,
DBMS_LOB.SUBSTR(t.sql_text, 1000) AS sql_text
FROM v$session w
JOIN v$sqltext t ON w.sql_id = t.sql_id AND t.piece = 0
WHERE w.sid IN (SELECT sid FROM v$session_wait WHERE blocking_session IS NOT NULL);
-- 查看完整的SQL文本（针对特定SQL_ID）
SELECT sql_id, DBMS_LOB.SUBSTR(sql_fulltext, 2000) AS full_sql
FROM v$sql
WHERE sql_id = '&sql_id';死锁的诊断方法
诊断工作流
死锁诊断与锁等待类似，但需要重点关注死锁的历史记录和详细信息：
- 检查告警日志：查找ORA-00060错误
- 查看死锁历史：使用
v$lock_history和dba_hist_active_sess_history - 分析死锁追踪文件：如果启用了死锁追踪，查看详细的追踪信息
- 识别死锁的根本原因：分析涉及的表、SQL和事务顺序
1. 查看死锁信息
sql
-- 脚本4：查看死锁历史记录
SET LINESIZE 200
-- 查看当前实例的死锁历史
SELECT * FROM v$lock_history;
-- 从AWR历史中查找死锁
SELECT sample_time,
session_id AS sid,
blocking_session,
event,
current_obj#,
object_name,
sql_id,
DBMS_LOB.SUBSTR(sql_text, 500) AS sql_text
FROM dba_hist_active_sess_history
WHERE event LIKE '%deadlock%'
OR wait_class = 'Application' AND state = 'WAITING'
ORDER BY sample_time DESC;
-- 查看最近的死锁记录（适用于Oracle 19c+）
SELECT * FROM dba_deadlocks
ORDER BY deadlock_timestamp DESC;2. 检查告警日志
sql
-- 脚本5：查找告警日志位置并查看死锁信息
-- 查找告警日志位置
SELECT value AS alert_log_path
FROM v$diag_info
WHERE name = 'Diag Alert';
-- 在Linux上查看告警日志中的死锁信息
-- grep -i deadlock <alert_log_path>/alert_<SID>.log
-- 在Windows上查看告警日志中的死锁信息
-- Get-Content -Path "<alert_log_path>\alert_<SID>.log" | Select-String -Pattern "deadlock" -CaseSensitive -Context 5, 103. 启用死锁追踪
sql
-- 脚本6：启用死锁追踪
-- 系统级启用死锁追踪（级别3：包含错误栈和进程状态）
ALTER SYSTEM SET events '60 trace name errorstack level 3';
-- 或在会话级别启用
ALTER SESSION SET events '60 trace name errorstack level 3';
-- 查看追踪文件位置
SELECT value AS trace_file
FROM v$diag_info
WHERE name = 'Default Trace File';
-- 禁用死锁追踪
ALTER SYSTEM SET events '60 trace name errorstack off';4. 分析死锁追踪文件
死锁追踪文件包含详细的死锁信息，包括：
- 涉及的会话ID和进程ID
- 锁定的资源（表、行）
- 执行的SQL语句
- 锁的类型和模式
- 死锁图（显示会话之间的等待关系）
示例追踪文件内容：
ORA-00060: Deadlock detected while waiting for resource
...
Wait-For-Graph:
Grants
XID USN SLT SEQ SID Serial
Session 532:
TRANSID USN SLT SEQ SID Serial
0x000E.01C.000002B5 14 28 693 532 1804
Requested Lock Mode:
TM - Table Lock - Mode 3 - Row-X (SX)
Locked Objects:
Object Name : SCOTT.EMP
Object Type : TABLE
Object ID : 73210
Mode Held : 3 - Row-X (SX)
Session 641:
TRANSID USN SLT SEQ SID Serial
0x0012.00F.000003A1 18 15 929 641 1234
Requested Lock Mode:
TM - Table Lock - Mode 3 - Row-X (SX)
Locked Objects:
Object Name : SCOTT.DEPT
Object Type : TABLE
Object ID : 73215
Mode Held : 3 - Row-X (SX)5. 使用Oracle Enterprise Manager诊断
Oracle Enterprise Manager (OEM)提供了可视化的锁诊断工具：
- 性能页面：查看实时锁等待情况
- 锁页面：显示锁等待树，直观展示阻塞关系
- SQL监控：查看执行中的SQL语句和锁信息
- AWR报告：包含锁等待统计和Top SQL
6. 自动化诊断脚本
sql
-- 脚本7：完整的锁诊断脚本
SET LINESIZE 200
SET PAGESIZE 100
SET SERVEROUTPUT ON
DECLARE
v_lock_wait_count NUMBER;
BEGIN
-- 检查锁等待数量
SELECT COUNT(*) INTO v_lock_wait_count FROM v$session_wait WHERE blocking_session IS NOT NULL;
DBMS_OUTPUT.PUT_LINE('='||RPAD('-', 80, '-')||'=');
DBMS_OUTPUT.PUT_LINE('Oracle锁诊断报告 - '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
DBMS_OUTPUT.PUT_LINE('='||RPAD('-', 80, '-')||'=');
DBMS_OUTPUT.PUT_LINE('锁等待会话数量: '||v_lock_wait_count);
DBMS_OUTPUT.PUT_LINE('='||RPAD('-', 80, '-')||'=');
IF v_lock_wait_count > 0 THEN
DBMS_OUTPUT.PUT_LINE('详细锁等待信息:');
DBMS_OUTPUT.PUT_LINE('-'||RPAD('-', 80, '-'));
FOR r IN (
SELECT w.sid AS waiting_sid,
s1.username AS waiting_user,
w.event AS wait_event,
w.seconds_in_wait AS wait_seconds,
b.sid AS blocking_sid,
s2.username AS blocking_user,
s2.status AS blocking_status
FROM v$session_wait w
JOIN v$session s1 ON w.sid = s1.sid
JOIN v$session b ON w.blocking_session = b.sid
WHERE w.blocking_session IS NOT NULL
ORDER BY w.seconds_in_wait DESC
) LOOP
DBMS_OUTPUT.PUT_LINE('等待会话: '||r.waiting_sid||'('||r.waiting_user||') 等待事件: '||r.wait_event||' 等待时间: '||r.wait_seconds||'秒');
DBMS_OUTPUT.PUT_LINE('阻塞会话: '||r.blocking_sid||'('||r.blocking_user||') 状态: '||r.blocking_status);
DBMS_OUTPUT.PUT_LINE('-'||RPAD('-', 80, '-'));
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('当前没有锁等待会话。');
END IF;
-- 检查死锁历史
SELECT COUNT(*) INTO v_lock_wait_count FROM v$lock_history;
DBMS_OUTPUT.PUT_LINE('='||RPAD('-', 80, '-')||'=');
DBMS_OUTPUT.PUT_LINE('死锁历史记录数量: '||v_lock_wait_count);
DBMS_OUTPUT.PUT_LINE('='||RPAD('-', 80, '-')||'=');
END;
/锁等待与死锁的解决方案
解决方案工作流
当遇到锁等待或死锁问题时，应按照以下优先级采取解决方案：
- 紧急恢复：终止阻塞会话，恢复系统运行
- 快速缓解：使用锁提示、调整事务隔离级别等临时措施
- 根本解决：优化SQL语句、调整应用设计、修改事务逻辑
- 预防措施：实施监控、建立规范、培训开发人员
1. 紧急恢复：终止阻塞会话
终止阻塞会话是解决锁等待和死锁最直接的方法，但需要谨慎操作，避免影响关键业务。
sql
-- 脚本8：安全终止阻塞会话
SET LINESIZE 200
-- 1. 详细查看阻塞会话信息，确认可以终止
SELECT s.sid,
s.serial#,
s.username,
s.program,
s.machine,
s.status,
s.sql_id,
DBMS_LOB.SUBSTR(t.sql_text, 500) AS sql_text
FROM v$session s
LEFT JOIN v$sql t ON s.sql_id = t.sql_id
WHERE s.sid IN (SELECT DISTINCT blocking_session FROM v$session_wait WHERE blocking_session IS NOT NULL);
-- 2. 生成终止会话的SQL语句
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_command
FROM v$session
WHERE sid IN (SELECT DISTINCT blocking_session FROM v$session_wait WHERE blocking_session IS NOT NULL);
-- 3. 执行终止命令（手动复制执行）
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
-- 4. 如果ALTER SYSTEM KILL SESSION无效，使用OS级终止
SELECT 'kill -9 ' || p.spid AS os_kill_command -- Linux
FROM v$process p
JOIN v$session s ON p.addr = s.paddr
WHERE s.sid = &blocking_sid;
-- 或Windows版本
SELECT 'taskkill /F /PID ' || p.spid AS os_kill_command
FROM v$process p
JOIN v$session s ON p.addr = s.paddr
WHERE s.sid = &blocking_sid;注意事项：
- 优先终止非核心业务会话
- 避免同时终止大量会话，防止系统抖动
- 记录被终止的会话信息，便于后续分析
2. 快速缓解：使用锁提示
锁提示可以帮助减少锁冲突，提高并发性能。
sql
-- 脚本9：常用锁提示示例
-- 1. NOWAIT提示：避免锁等待，立即返回错误
SELECT * FROM employees WHERE department_id = 10 FOR UPDATE NOWAIT;
-- 2. SKIP LOCKED提示：跳过已锁定的行，只处理可用行
-- 适用于批量处理场景，如订单分配、任务调度
SELECT * FROM orders WHERE status = 'PENDING' FOR UPDATE SKIP LOCKED FETCH FIRST 100 ROWS ONLY;
-- 3. READ ONLY提示：避免获取写锁，提高并发读性能
SELECT /*+ READONLY */ * FROM large_table WHERE created_date > SYSDATE - 7;
-- 4. NOLOCK提示：等同于READ ONLY，提高读性能
SELECT /*+ NOLOCK */ * FROM transactions;
-- 5. ROWLOCK提示：强制使用行级锁，避免表级锁
UPDATE /*+ ROWLOCK */ employees SET salary = salary * 1.1 WHERE department_id = 10;
-- 6. USE_NL/USE_HASH提示：控制连接方式，减少锁持有时间
SELECT /*+ USE_HASH(e, d) */ e.*, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;3. 调整事务隔离级别
事务隔离级别直接影响锁的行为和并发性能。
sql
-- 脚本10：调整事务隔离级别
-- 查看当前事务隔离级别
SELECT s.sid, s.username, s.isolation_level
FROM v$session s;
-- 修改会话级别的事务隔离级别
-- READ COMMITTED：默认级别，读已提交，并发性能最好
ALTER SESSION SET isolation_level = READ COMMITTED;
-- SERIALIZABLE：可串行化，并发性能最差，但数据一致性最高
ALTER SESSION SET isolation_level = SERIALIZABLE;
-- READ ONLY：只读事务，不获取写锁
ALTER SESSION SET isolation_level = READ ONLY;
-- 全局设置（需要重启数据库）
ALTER SYSTEM SET transactions_isolation_level = READ_COMMITTED SCOPE=SPFILE;隔离级别选择建议：
- OLTP系统：优先使用READ COMMITTED，兼顾性能和一致性
- 财务系统：关键事务可使用SERIALIZABLE，确保数据绝对一致
- 报表查询：使用READ ONLY，提高并发性能
4. SQL优化：减少锁持有时间
优化SQL语句是解决锁问题的根本方法之一，通过减少锁持有时间和锁定范围，可以显著降低锁冲突。
优化策略：
添加适当索引：将全表扫描改为索引扫描，减少锁定的行数
sql-- 优化前：全表扫描，锁定所有行 UPDATE orders SET status = 'SHIPPED' WHERE order_date = SYSDATE; -- 优化后：使用索引，只锁定符合条件的行 CREATE INDEX idx_orders_date ON orders(order_date); UPDATE orders SET status = 'SHIPPED' WHERE order_date = SYSDATE;缩小事务范围：将大事务拆分为多个小事务
sql-- 优化前：单个大事务，锁定时间长 BEGIN UPDATE table1 SET ... WHERE ...; -- 中间可能有其他操作，增加锁持有时间 UPDATE table2 SET ... WHERE ...; COMMIT; END; -- 优化后：拆分为多个小事务 BEGIN UPDATE table1 SET ... WHERE ...; COMMIT; -- 中间操作 UPDATE table2 SET ... WHERE ...; COMMIT; END;避免长事务：在事务中避免用户交互、网络调用等耗时操作
使用批量提交：对于大量数据操作，使用批量提交减少锁持有时间
sql-- 使用批量提交处理大量数据 DECLARE CURSOR c_data IS SELECT id FROM large_table WHERE status = 'ACTIVE'; TYPE id_list IS TABLE OF large_table.id%TYPE; v_ids id_list; BEGIN OPEN c_data; LOOP FETCH c_data BULK COLLECT INTO v_ids LIMIT 1000; EXIT WHEN v_ids.COUNT = 0; FORALL i IN 1..v_ids.COUNT UPDATE large_table SET status = 'INACTIVE' WHERE id = v_ids(i); COMMIT; -- 每1000行提交一次 END LOOP; CLOSE c_data; END; /
5. 应用设计优化
应用程序设计是锁问题的根源，合理的设计可以从根本上减少锁冲突。
设计原则：
统一资源访问顺序：所有事务按相同顺序访问表和行，避免循环等待
sql-- 事务1（正确）：先更新表A，再更新表B UPDATE tableA SET ... WHERE ...; UPDATE tableB SET ... WHERE ...; -- 事务2（正确）：同样先更新表A，再更新表B UPDATE tableA SET ... WHERE ...; UPDATE tableB SET ... WHERE ...;使用乐观锁：对于读多写少的场景，使用版本号或时间戳实现乐观锁
sql-- 乐观锁实现示例 UPDATE products SET quantity = quantity - 1, version = version + 1 WHERE product_id = 123 AND version = 5; -- 检查更新是否成功 IF SQL%ROWCOUNT = 0 THEN -- 版本不匹配，处理并发冲突 END IF;避免热点数据：热点数据是锁冲突的高发区，可通过以下方式优化：
- 数据分片：将热点数据分散到多个表或分区
- 批量处理：将热点操作集中在低峰期执行
- 缓存机制：使用缓存减少数据库访问
- 异步处理：将热点操作异步化
合理使用外键约束：外键约束会导致父表获取共享锁，可能引发锁冲突
- 考虑使用应用级约束替代数据库外键
- 或使用延迟约束，减少锁持有时间
6. 数据库配置优化
合理的数据库配置可以提高并发性能，减少锁冲突。
sql
-- 脚本11：数据库配置优化
-- 查看锁相关的初始化参数
SHOW PARAMETER lock;
SHOW PARAMETER processes;
SHOW PARAMETER sessions;
-- 调整锁的最大数量（根据实际需求）
ALTER SYSTEM SET processes = 500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions = 555 SCOPE=SPFILE;
ALTER SYSTEM SET transactions = 610 SCOPE=SPFILE;
-- 启用并行DML，提高批量操作性能
ALTER SYSTEM SET parallel_degree_policy = 'AUTO' SCOPE=BOTH;
ALTER SYSTEM SET parallel_max_servers = 32 SCOPE=BOTH;
-- 调整回滚段大小，避免长事务导致的锁问题
ALTER SYSTEM SET undo_retention = 3600 SCOPE=BOTH; -- 1小时
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE=BOTH;
-- 重启数据库使参数生效（根据实际情况决定）
-- SHUTDOWN IMMEDIATE;
-- STARTUP;7. 使用资源管理器限制长事务
Oracle资源管理器可以限制长事务的执行时间，避免单个事务占用锁资源过长。
sql
-- 脚本12：使用资源管理器限制长事务
-- 1. 创建资源计划
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
plan => 'LONG_TRANSACTION_PLAN',
comment => '限制长事务的资源计划'
);
-- 为OLTP事务分配更多资源
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'LONG_TRANSACTION_PLAN',
group_or_subplan => 'OLTP_GROUP',
comment => 'OLTP事务组',
cpu_p1 => 80,
max_idle_time => 30, -- 空闲30分钟自动终止
switch_time => 180, -- 运行超过3分钟切换到低优先级
switch_group => 'LOW_PRIORITY_GROUP'
);
-- 低优先级组
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
plan => 'LONG_TRANSACTION_PLAN',
group_or_subplan => 'LOW_PRIORITY_GROUP',
comment => '低优先级事务组',
cpu_p1 => 20,
max_idle_time => 10
);
END;
/
-- 2. 激活资源计划
ALTER SYSTEM SET resource_manager_plan = 'LONG_TRANSACTION_PLAN' SCOPE=BOTH;锁等待与死锁的预防措施
预防措施框架
锁等待和死锁的预防需要从多个层面入手，包括应用设计、数据库配置、监控管理和开发规范等。以下是一个全面的预防措施框架：
| 层面 | 关键措施 | 责任人 | 频率 |
|---|---|---|---|
| 应用设计 | 事务优化、资源访问顺序、乐观锁 | 开发团队 | 需求设计阶段 |
| 数据库设计 | 索引优化、分区设计、约束管理 | DBA + 开发 | 数据库设计阶段 |
| SQL编写 | 高效SQL、锁提示使用、批量操作 | 开发人员 | 编码阶段 |
| 数据库配置 | 资源限制、并行度调整、回滚段配置 | DBA | 数据库部署和维护阶段 |
| 监控管理 | 实时监控、定期分析、告警设置 | DBA | 持续进行 |
| 开发规范 | SQL审核、培训、最佳实践 | DBA + 架构师 | 定期更新和培训 |
1. 应用设计最佳实践
事务优化
保持事务简短：事务执行时间控制在秒级，避免长时间持有锁
sql-- 优化前：事务包含耗时操作 BEGIN UPDATE orders SET status = 'PAID' WHERE order_id = 123; -- 调用外部API（耗时操作） -- 发送邮件通知（耗时操作） COMMIT; END; -- 优化后：拆分事务，耗时操作移到事务外 BEGIN UPDATE orders SET status = 'PAID' WHERE order_id = 123; COMMIT; END; -- 外部API调用 -- 邮件通知避免在事务中进行用户交互：用户思考时间会导致锁长时间持有
使用批量提交：对于大量数据操作，每处理一定数量的记录就提交一次
统一资源访问顺序
按固定顺序访问表：所有事务按相同顺序更新表，避免循环等待
sql-- 所有事务都先更新父表，再更新子表 UPDATE parent_table SET ... WHERE ...; UPDATE child_table SET ... WHERE ...;按主键顺序访问行：对于同一表的多行更新，按主键顺序处理
sql-- 按主键升序更新，减少行锁冲突 UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10 ORDER BY employee_id;
合理使用锁机制
- 优先使用乐观锁：对于读多写少的场景，使用版本号或时间戳实现乐观锁
- 避免显式锁：尽量减少使用
SELECT ... FOR UPDATE等显式锁 - 使用适当的锁提示：根据业务需求选择合适的锁提示
2. 数据库设计优化
索引设计
- 为频繁更新的列创建索引：减少锁定的行数
- 避免过度索引：过多索引会增加DML操作的锁开销
- 使用覆盖索引：减少回表操作，提高查询性能
- 考虑索引顺序：将过滤性高的列放在前面
分区表设计
对大表进行分区：减少锁的范围，提高并发性能
sql-- 按时间分区，减少锁定范围 CREATE TABLE orders ( order_id NUMBER, order_date DATE, ... ) PARTITION BY RANGE (order_date) ( PARTITION p2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')), PARTITION p2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) );使用本地索引：减少分区表的锁冲突
约束管理
合理使用外键约束：考虑使用延迟约束或应用级约束
sql-- 使用延迟约束，减少锁持有时间 ALTER TABLE child_table ADD CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id) DEFERRABLE INITIALLY DEFERRED;避免使用CHECK约束：考虑使用触发器或应用验证替代
3. SQL编写规范
高效SQL编写
- 避免全表扫描：为WHERE条件添加适当索引
- 减少锁定的行数：使用精确的WHERE条件
- 避免不必要的排序和分组：减少资源消耗
- 使用绑定变量：减少硬解析，提高并发性能
锁提示使用
- 根据业务需求选择锁提示：
- 批量处理：使用
SKIP LOCKED - 实时数据：使用
NOWAIT - 报表查询：使用
READ ONLY或NOLOCK
- 批量处理：使用
批量操作最佳实践
- 使用FORALL语句：提高批量操作性能
- 控制批量大小：根据系统资源调整批次大小（建议1000-5000行）
- 在低峰期执行：避免影响正常业务
4. 数据库配置优化
资源限制
sql
-- 脚本13：配置合理的资源限制
-- 设置会话超时
ALTER PROFILE default LIMIT IDLE_TIME 30; -- 30分钟空闲超时
-- 设置事务超时（Oracle 19c+）
ALTER SYSTEM SET transactions_timeout = 300 SCOPE=BOTH; -- 5分钟事务超时
-- 设置锁等待超时
ALTER SYSTEM SET ddl_lock_timeout = 60 SCOPE=BOTH; -- 60秒DDL锁等待超时并行度配置
sql
-- 脚本14：优化并行度配置
-- 设置自动并行度
ALTER SYSTEM SET parallel_degree_policy = 'AUTO' SCOPE=BOTH;
-- 限制最大并行度
ALTER SYSTEM SET parallel_max_servers = 32 SCOPE=BOTH;
-- 设置语句级并行度
ALTER SYSTEM SET parallel_degree_limit = 8 SCOPE=BOTH;5. 监控和管理
实时监控
sql
-- 脚本15：创建锁等待监控作业
-- 创建监控表
CREATE TABLE lock_wait_history (
id NUMBER GENERATED ALWAYS AS IDENTITY,
sample_time TIMESTAMP DEFAULT SYSTIMESTAMP,
waiting_sid NUMBER,
waiting_serial NUMBER,
waiting_user VARCHAR2(30),
wait_event VARCHAR2(64),
wait_seconds NUMBER,
blocking_sid NUMBER,
blocking_serial NUMBER,
blocking_user VARCHAR2(30),
blocking_sql_id VARCHAR2(13),
PRIMARY KEY (id)
);
-- 创建监控存储过程
CREATE OR REPLACE PROCEDURE monitor_lock_waits IS
BEGIN
INSERT INTO lock_wait_history (
waiting_sid, waiting_serial, waiting_user, wait_event, wait_seconds,
blocking_sid, blocking_serial, blocking_user, blocking_sql_id
)
SELECT w.sid, w.serial#, s1.username, w.event, w.seconds_in_wait,
b.sid, b.serial#, s2.username, s2.sql_id
FROM v$session_wait w
JOIN v$session s1 ON w.sid = s1.sid
JOIN v$session b ON w.blocking_session = b.sid
WHERE w.blocking_session IS NOT NULL;
COMMIT;
END;
/
-- 每5分钟执行一次监控
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MONITOR_LOCK_WAITS_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN monitor_lock_waits; END;',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
enabled => TRUE
);
END;
/定期分析
- 每日：查看锁等待历史记录，分析高频锁冲突
- 每周：生成AWR报告，分析Top SQL和等待事件
- 每月：进行一次全面的锁分析，包括索引使用情况、锁等待模式等
告警设置
- 设置锁等待告警：当锁等待时间超过阈值时触发告警
- 设置死锁告警：当出现死锁时立即通知DBA
- 多种告警渠道：邮件、短信、钉钉、微信等
6. 开发规范和培训
SQL审核机制
- 建立SQL审核流程：所有SQL在上线前必须经过DBA审核
- 使用自动化工具：如Oracle SQL Developer、Toad等进行SQL分析
- 制定审核标准：
- 禁止全表扫描（除非表数据量小于1万行）
- 禁止没有WHERE条件的UPDATE/DELETE语句
- 必须使用绑定变量
- 事务执行时间不超过5秒
定期培训
- 培训内容：锁机制原理、SQL优化、最佳实践
- 培训频率：每季度至少一次
- 培训对象：开发人员、测试人员、架构师
建立最佳实践文档
- 编写详细的锁管理最佳实践文档
- 包含常见问题和解决方案
- 定期更新和维护
7. Oracle 19c/21c新特性利用
Oracle 19c：
- 自动索引：减少索引管理负担
- 实时统计信息：提高执行计划准确性
- SQL计划管理：稳定执行计划，减少锁冲突
Oracle 21c：
- 多主复制：提高读写并发性能
- 区块链表：减少传统锁的使用
- 混合分区表：更灵活的分区策略，减少锁范围
Oracle 19c vs 21c 锁管理差异
Oracle 19c和21c在锁管理方面有显著差异，21c引入了多项新特性和优化，旨在提高并发性能和减少锁冲突。以下是详细对比：
| 特性 | Oracle 19c | Oracle 21c |
|---|---|---|
| 锁监控增强 | 基础视图：v$lock、v$session_wait、v$lock_history | 新增视图：dba_deadlocks、v$session_blockers 增强视图：v$session_wait添加更多锁信息字段 |
| 死锁检测 | 自动死锁检测，默认启用 | 优化死锁检测算法，检测速度提高30%以上 支持死锁图可视化展示 |
| 锁等待超时 | 支持DDL锁等待超时 不支持DML锁等待超时 | 支持DML锁等待超时（transactions_timeout参数） 增强DDL锁等待超时功能 |
| 锁管理优化 | 基本锁管理，无自动优化 | 自动锁优化，减少不必要的锁持有 增强的行锁管理，减少锁冲突 |
| 并行执行锁 | 自动并行度，基本并行锁管理 | 智能并行度调整，减少并行执行的锁冲突 并行服务器池管理，提高资源利用率 |
| 分区锁 | 支持分区锁，基本分区锁管理 | 增强分区锁功能，支持分区级锁粒度 减少分区表的锁冲突 |
| 自动索引 | 无 | 自动创建和管理索引，减少因缺少索引导致的锁冲突 |
| 区块链表 | 无 | 引入区块链表，减少传统锁的使用 适合不可篡改数据场景 |
| 多主复制 | 有限支持 | 增强多主复制功能，提高读写并发性能 减少分布式锁冲突 |
| 事务管理 | 基本事务管理 | 增强事务管理，支持更灵活的事务隔离级别 优化长事务处理 |
Oracle 19c 锁管理最佳实践
虽然Oracle 19c的锁管理功能相对基础，但通过合理配置和优化，仍能有效减少锁冲突：
sql
-- Oracle 19c锁管理优化
-- 1. 启用死锁追踪
ALTER SYSTEM SET events '60 trace name errorstack level 3';
-- 2. 调整DDL锁等待超时
ALTER SYSTEM SET ddl_lock_timeout = 60 SCOPE=BOTH;
-- 3. 优化并行度
ALTER SYSTEM SET parallel_degree_policy = 'AUTO' SCOPE=BOTH;
ALTER SYSTEM SET parallel_max_servers = 32 SCOPE=BOTH;
-- 4. 监控锁等待
CREATE OR REPLACE VIEW dba_lock_waits AS
SELECT w.sid AS waiting_sid,
s1.username AS waiting_user,
w.event AS wait_event,
w.seconds_in_wait AS wait_seconds,
b.sid AS blocking_sid,
s2.username AS blocking_user,
s2.sql_id AS blocking_sql_id
FROM v$session_wait w
JOIN v$session s1 ON w.sid = s1.sid
JOIN v$session b ON w.blocking_session = b.sid
WHERE w.blocking_session IS NOT NULL;
-- 5. 创建定期监控作业
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'CHECK_LOCK_WAITS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
IF (SELECT COUNT(*) FROM dba_lock_waits WHERE wait_seconds > 60) > 0 THEN
-- 发送告警
NULL;
END IF;
END;',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=10',
enabled => TRUE
);
END;
/Oracle 21c 锁管理新特性
Oracle 21c引入了多项新特性，显著提升了锁管理的效率和易用性：
1. 增强的死锁检测
sql
-- Oracle 21c：查看详细的死锁信息
SELECT * FROM dba_deadlocks ORDER BY deadlock_timestamp DESC;
-- 查看死锁的SQL语句
SELECT d.deadlock_timestamp,
d.deadlock_id,
d.blocking_session,
d.waiting_session,
d.object_name,
d.sql_text
FROM dba_deadlocks d;2. DML锁等待超时
sql
-- Oracle 21c：设置DML事务超时
ALTER SYSTEM SET transactions_timeout = 300 SCOPE=BOTH; -- 5分钟
-- 会话级别设置
ALTER SESSION SET transactions_timeout = 60; -- 1分钟3. 自动索引
sql
-- Oracle 21c：启用自动索引
ALTER SYSTEM SET optimizer_auto_index_mode = 'IMPLEMENT' SCOPE=BOTH;
-- 查看自动创建的索引
SELECT index_name, table_name, status, created
FROM dba_auto_indexes;
-- 自动索引可以减少因缺少索引导致的全表扫描和表级锁4. 区块链表
sql
-- Oracle 21c：创建区块链表
CREATE BLOCKCHAIN TABLE transactions (
transaction_id NUMBER GENERATED ALWAYS AS IDENTITY,
from_account VARCHAR2(50),
to_account VARCHAR2(50),
amount NUMBER,
transaction_date TIMESTAMP DEFAULT SYSTIMESTAMP
) NO DROP UNTIL 365 DAYS IDLE
NO DELETE UNTIL 365 DAYS AFTER INSERT
HASHING USING SHA2_512 VERSION V1;
-- 区块链表使用特殊的锁机制，减少传统锁冲突
INSERT INTO transactions (from_account, to_account, amount) VALUES ('A', 'B', 100);迁移建议：从19c到21c
对于计划从Oracle 19c迁移到21c的DBA，以下是锁管理方面的迁移建议：
- 逐步迁移：先在测试环境验证21c的锁管理特性，再逐步迁移生产系统
- 利用新特性：启用自动索引、增强死锁检测等新特性
- 调整配置：根据21c的特性调整锁相关参数
- 更新监控脚本：修改基于v$lock等视图的监控脚本，利用新视图
- 培训开发人员：培训开发人员使用21c的新特性，如区块链表
- 测试并发性能：重点测试高并发场景下的锁冲突情况
版本选择建议
- OLTP系统：优先选择Oracle 21c，利用其增强的锁管理和并发性能
- 数据仓库：Oracle 19c或21c均可，21c在并行执行锁管理方面有优势
- 混合负载：Oracle 21c更适合混合负载，能更好地处理不同类型的锁需求
- 成本敏感：如果预算有限，Oracle 19c也是一个稳定的选择，通过合理优化可满足大部分需求
无论使用哪个版本，定期监控和优化锁管理都是确保数据库性能的关键。DBA应该根据系统特点和业务需求，选择合适的Oracle版本，并实施相应的锁管理策略。
常见问题（FAQ）
1. 如何在高并发环境下快速识别死锁？
答：在高并发环境下，死锁的识别需要结合多种方法：
- 实时监控：使用
v$session_wait视图查找长时间的enq: TX - row lock contention等待事件 - 告警日志：查找"ORA-00060: Deadlock detected while waiting for resource"错误信息
- 死锁历史：查询
v$lock_history（19c）或dba_deadlocks（21c）视图 - 自动告警：设置监控系统，当出现死锁时立即发送告警
- 性能指标：监控CPU使用率、活跃会话数、事务响应时间等指标，死锁会导致这些指标异常
快速诊断脚本：
sql
-- 高并发下快速识别死锁
SELECT '死锁历史数量' AS metric, COUNT(*) AS value FROM v$lock_history
UNION ALL
SELECT '当前锁等待数量', COUNT(*) FROM v$session_wait WHERE blocking_session IS NOT NULL
UNION ALL
SELECT 'TOP等待事件', event FROM (
SELECT event, COUNT(*) AS cnt FROM v$session_wait WHERE blocking_session IS NOT NULL GROUP BY event ORDER BY cnt DESC
) WHERE ROWNUM = 1;2. 如何安全地终止阻塞会话？
答：终止阻塞会话需要谨慎操作，避免影响关键业务：
- 确认阻塞会话：详细查看阻塞会话的信息，包括SQL语句、用户名、程序等
- 评估影响：判断终止该会话对业务的影响，优先终止非核心业务会话
- 生成终止命令：使用脚本生成终止命令，避免手动输入错误
- 执行终止命令：先尝试
ALTER SYSTEM KILL SESSION，如果无效再使用OS级终止 - 记录操作：记录被终止的会话信息，便于后续分析
安全终止脚本：
sql
-- 安全终止阻塞会话的完整流程
-- 1. 查看阻塞会话详情
SELECT s.sid, s.serial#, s.username, s.program, s.machine, s.sql_id,
DBMS_LOB.SUBSTR(t.sql_text, 500) AS sql_text
FROM v$session s
LEFT JOIN v$sql t ON s.sql_id = t.sql_id
WHERE s.sid IN (SELECT DISTINCT blocking_session FROM v$session_wait WHERE blocking_session IS NOT NULL);
-- 2. 生成终止命令
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_command
FROM v$session
WHERE sid = &blocking_sid;
-- 3. 执行终止命令（手动复制）
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;3. 如何区分锁等待和长事务？
答：锁等待和长事务是不同的概念，但可能相互影响：
| 特性 | 锁等待 | 长事务 |
|---|---|---|
| 定义 | 会话等待其他会话持有的锁 | 事务执行时间过长 |
| 等待事件 | 有明确的等待事件，如enq: TX - row lock contention | 通常没有等待事件，或等待事件为SQL*Net message from client |
| 会话状态 | 状态为"WAITING" | 状态为"ACTIVE"或"INACTIVE" |
| 处理方法 | 终止阻塞会话或等待锁释放 | 优化事务，缩短执行时间 |
| 影响范围 | 影响多个会话 | 主要影响单个会话，但可能阻塞其他会话 |
区分方法：
sql
-- 区分锁等待和长事务
SELECT s.sid, s.serial#, s.username, s.status, s.event, s.seconds_in_wait,
CASE
WHEN s.event LIKE '%enq: TX%' THEN '锁等待'
WHEN s.seconds_in_wait > 300 AND s.event = 'SQL*Net message from client' THEN '长事务'
ELSE '其他'
END AS issue_type
FROM v$session s
WHERE s.status IN ('ACTIVE', 'WAITING')
AND s.username IS NOT NULL;4. 如何优化Oracle 19c中的锁等待问题？
答：Oracle 19c中优化锁等待的方法：
- 索引优化：添加适当索引，将表级锁改为行级锁
- 事务优化：缩短事务执行时间，减少锁持有时间
- 锁提示：使用
NOWAIT、SKIP LOCKED等锁提示 - 并行度调整：优化并行度设置，减少并行执行的锁冲突
- 死锁追踪：启用死锁追踪，便于分析死锁原因
- 定期监控：建立定期监控机制，及时发现锁等待问题
19c锁优化脚本：
sql
-- Oracle 19c锁优化
ALTER SYSTEM SET parallel_degree_policy = 'AUTO' SCOPE=BOTH;
ALTER SYSTEM SET ddl_lock_timeout = 60 SCOPE=BOTH;
ALTER SYSTEM SET events '60 trace name errorstack level 3';5. 如何使用Oracle 21c的新特性优化锁管理？
答：Oracle 21c提供了多项新特性来优化锁管理：
自动索引：减少因缺少索引导致的锁冲突
sqlALTER SYSTEM SET optimizer_auto_index_mode = 'IMPLEMENT' SCOPE=BOTH;DML锁等待超时：避免会话无限期等待锁
sqlALTER SYSTEM SET transactions_timeout = 300 SCOPE=BOTH;增强的死锁检测：更快地检测和处理死锁
sqlSELECT * FROM dba_deadlocks ORDER BY deadlock_timestamp DESC;区块链表：减少传统锁的使用
sqlCREATE BLOCKCHAIN TABLE secure_data (...);
6. 如何防止热点数据导致的锁冲突？
答：热点数据是锁冲突的高发区，可通过以下方法优化：
数据分片：将热点数据分散到多个表或分区
sql-- 按哈希分区分散热点数据 CREATE TABLE orders ( order_id NUMBER, customer_id NUMBER, ... ) PARTITION BY HASH (order_id) PARTITIONS 16;批量处理：将热点操作集中在低峰期执行
缓存机制：使用缓存减少数据库访问，如Redis缓存热点数据
异步处理：将热点操作异步化，如使用消息队列
乐观锁：对于读多写少的热点数据，使用乐观锁替代悲观锁
7. 如何设计高效的事务来减少锁冲突？
答：设计高效事务的原则：
- 保持事务简短：事务执行时间控制在秒级
- 最小化锁持有时间：将耗时操作移到事务外
- 统一资源访问顺序：所有事务按相同顺序访问表和行
- 使用适当的隔离级别：根据业务需求选择合适的隔离级别
- 避免不必要的锁：尽量减少使用显式锁
- 批量操作优化：使用批量提交减少锁持有时间
高效事务示例：
sql
-- 高效事务设计
BEGIN
-- 只包含必要的数据库操作
UPDATE orders SET status = 'PAID' WHERE order_id = 123;
INSERT INTO order_payments (order_id, amount, payment_date)
VALUES (123, 100, SYSTIMESTAMP);
COMMIT;
END;
-- 耗时操作移到事务外
-- 调用外部API
-- 发送邮件通知8. 如何监控和分析锁等待的历史数据？
答：监控和分析锁等待历史数据的方法：
- 创建监控表：创建表存储锁等待历史数据
- 定期收集数据：使用定时作业定期收集锁等待信息
- 生成报表：定期生成锁等待报表，分析趋势
- AWR报告：分析AWR报告中的锁等待统计信息
- ASH报告：使用ASH报告分析特定时间段的锁等待情况
历史监控实现：
sql
-- 创建锁等待历史表
CREATE TABLE lock_wait_history (
id NUMBER GENERATED ALWAYS AS IDENTITY,
sample_time TIMESTAMP DEFAULT SYSTIMESTAMP,
waiting_sid NUMBER,
waiting_user VARCHAR2(30),
wait_event VARCHAR2(64),
wait_seconds NUMBER,
blocking_sid NUMBER,
blocking_user VARCHAR2(30),
blocking_sql_id VARCHAR2(13),
PRIMARY KEY (id)
);
-- 创建收集作业（每5分钟执行一次）
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'COLLECT_LOCK_WAITS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
INSERT INTO lock_wait_history (
waiting_sid, waiting_user, wait_event, wait_seconds,
blocking_sid, blocking_user, blocking_sql_id
)
SELECT w.sid, s1.username, w.event, w.seconds_in_wait,
b.sid, s2.username, s2.sql_id
FROM v$session_wait w
JOIN v$session s1 ON w.sid = s1.sid
JOIN v$session b ON w.blocking_session = b.sid
WHERE w.blocking_session IS NOT NULL;
COMMIT;
END;',
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
enabled => TRUE
);
END;
/9. 如何处理DDL锁等待问题？
答：DDL锁等待通常发生在执行DDL语句时，可通过以下方法处理：
设置DDL锁等待超时：避免无限期等待
sqlALTER SYSTEM SET ddl_lock_timeout = 60 SCOPE=BOTH;在低峰期执行DDL：减少对业务的影响
使用ONLINE关键字：对于支持的DDL语句，使用ONLINE关键字减少锁持有时间
sqlALTER INDEX idx_orders_customer_id REBUILD ONLINE;查看DDL锁持有者：识别并处理持有DDL锁的会话
sqlSELECT s.sid, s.serial#, s.username, s.program, s.machine FROM v$session s WHERE s.sid IN (SELECT sid FROM v$lock WHERE type = 'TM' AND lmode = 6);
10. 如何建立锁管理的最佳实践规范？
答：建立锁管理最佳实践规范需要从多个层面入手：
开发规范：
- 制定SQL编写规范，包括锁提示使用、事务设计等
- 建立SQL审核机制，确保SQL质量
- 培训开发人员，提高锁意识
监控规范：
- 设置锁等待和死锁告警
- 定期生成锁等待报表
- 建立锁等待分析流程
应急处理规范：
- 制定锁等待和死锁的应急处理流程
- 建立阻塞会话终止的审批流程
- 定期演练应急处理流程
优化规范：
- 定期进行锁等待分析和优化
- 建立索引优化机制
- 定期审查事务设计
规范文档示例：
# Oracle锁管理最佳实践规范
## 1. SQL编写规范
- 所有UPDATE/DELETE语句必须包含WHERE条件
- 避免使用全表扫描，必须为WHERE条件添加索引
- 事务执行时间不超过5秒
- 优先使用乐观锁，减少显式锁的使用
## 2. 监控告警规范
- 锁等待时间超过60秒发送告警
- 出现死锁立即发送告警
- 每小时生成锁等待报表
## 3. 应急处理流程
1. 收到锁等待告警，立即进行诊断
2. 确认阻塞会话，评估影响
3. 如影响核心业务，执行终止操作
4. 记录操作，后续分析原因
5. 优化相关SQL或应用设计最佳实践
锁管理最佳实践框架
锁管理是一个系统工程，需要从多个层面入手，以下是一个全面的最佳实践框架：
| 层面 | 最佳实践 | 优先级 |
|---|---|---|
| 应用设计 | 事务优化、资源访问顺序、乐观锁 | 高 |
| SQL编写 | 高效SQL、锁提示使用、绑定变量 | 高 |
| 数据库设计 | 索引优化、分区设计、约束管理 | 中 |
| 数据库配置 | 资源限制、并行度调整、回滚段配置 | 中 |
| 监控管理 | 实时监控、定期分析、告警设置 | 高 |
| 开发规范 | SQL审核、培训、最佳实践 | 中 |
关键最佳实践
保持事务简短高效
- 事务执行时间控制在秒级
- 避免在事务中进行用户交互、外部API调用等耗时操作
- 使用批量提交处理大量数据，减少锁持有时间
统一资源访问顺序
- 所有事务按相同顺序访问表和行
- 对同一表的多行操作按主键顺序处理
- 避免循环等待条件的产生
优化SQL语句
- 为WHERE条件添加适当索引，减少锁定的行数
- 避免全表扫描，除非表数据量很小
- 使用绑定变量，减少硬解析
- 优化JOIN操作，减少中间结果集
合理使用锁机制
- 优先使用乐观锁，减少显式锁的使用
- 根据业务需求选择合适的锁提示：
- 批量处理：
SKIP LOCKED - 实时数据：
NOWAIT - 报表查询：
READ ONLY或NOLOCK
- 批量处理：
实施监控和告警
- 设置锁等待和死锁告警
- 定期生成锁等待报表
- 使用AWR、ASH报告分析锁等待趋势
- 建立锁等待分析流程
数据库配置优化
- 设置合理的资源限制，包括会话超时、事务超时等
- 优化并行度配置，减少并行执行的锁冲突
- 调整回滚段大小，避免长事务导致的问题
建立开发规范
- 制定SQL编写规范，包括锁提示使用、事务设计等
- 建立SQL审核机制，确保SQL质量
- 定期培训开发人员，提高锁意识
- 制定数据库访问规范，包括事务管理规范
定期维护和优化
- 定期分析锁等待历史数据，识别高频锁冲突
- 优化索引使用情况，删除未使用的索引
- 调整表结构和分区设计，减少锁冲突
- 定期审查事务设计，优化长事务
应急处理最佳实践
- 快速响应：收到锁等待或死锁告警后，立即进行诊断
- 分级处理：根据业务重要性，优先处理核心业务的锁冲突
- 安全终止：谨慎终止阻塞会话，避免影响关键业务
- 记录操作：详细记录应急处理过程，便于后续分析
- 根因分析：事件处理完成后，进行全面的根因分析
- 持续改进：根据分析结果，优化应用设计和SQL语句
总结
死锁与锁等待是Oracle数据库中常见的性能问题，对数据库的性能和可用性造成严重影响。处理这些问题需要DBA掌握锁的类型、模式和诊断方法，能够快速定位和解决锁冲突。
核心要点
- 快速诊断：使用实时监控工具和诊断脚本，快速识别锁等待和死锁
- 安全处理：谨慎终止阻塞会话，避免影响关键业务
- 根本解决：通过优化SQL语句、调整应用设计、修改事务逻辑等方式，从根本上解决锁问题
- 预防为主：实施监控和告警机制，建立开发规范，定期培训开发人员，预防锁问题的发生
- 版本差异：Oracle 19c和21c在锁管理方面有显著差异，21c提供了更多的自动化和优化功能
未来发展趋势
随着Oracle数据库的不断发展，锁管理功能也在不断增强。未来的Oracle版本将更加智能化，能够自动优化锁的使用，减少锁冲突，提高并发性能。DBA需要不断学习新的锁管理特性，适应数据库技术的发展。
最终建议
锁管理是Oracle数据库性能优化的重要组成部分，需要DBA和开发团队密切配合。DBA需要建立完善的锁管理机制，包括监控、告警、分析和优化流程。开发团队需要遵循锁管理最佳实践，编写高效的SQL语句，设计合理的事务。只有这样，才能有效减少锁等待和死锁的发生，提高数据库的性能和可用性。
通过实施本手册中的最佳实践，DBA可以全面提升锁管理能力，快速定位和解决锁相关问题，确保数据库的稳定运行，为业务提供可靠的支撑。