一、实时死锁检测

1. 自动检测机制

Oracle每3秒自动检测死锁，会生成以下信息：

• 自动处理：选择代价最小的事务进行回滚
• 报错日志：ORA-00060错误写入alert.log
• 跟踪文件：生成包含死锁详细信息的trace文件

2. 查看实时死锁信息

-- 查看当前被回滚的会话 
SELECT s.sid, s.serial#, s.username, s.program, s.sql_id 
FROM v$session s 
JOIN v$transaction t ON s.saddr = t.ses_addr 
WHERE t.status = 'DEAD';

---

二、死锁日志分析

1. 定位alert.log

-- 查找alert.log路径 
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

在对应目录下使用命令：

tail -500 alert_$ORACLE_SID.log | grep "ORA-00060"

2. 解析跟踪文件

找到包含以下标识的跟踪文件：

*** 2025-02-11T11:44:00.123456 
*** SESSION ID:(25.12345) 2025-02-11T11:44:00.123456 
*** CLIENT ID:(APPSERVER_USER) 
*** SERVICE NAME:(SYS$USERS) 
*** MODULE NAME:(JDBC Thin Client) 
*** ACTION NAME:(Transaction Commit) 
DEADLOCK DETECTED ( ORA-00060 )

---

三、手动分析死锁链

1. 查询被阻塞会话

SELECT 
  l1.sid || ',' || s1.serial# blocker,
  l2.sid || ',' || s2.serial# waiter,
  o.owner || '.' || o.object_name locked_object,
  l1.type lock_type,
  decode(l1.lmode,1,'NULL',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') mode_held,
  decode(l2.request,1,'NULL',2,'RS',3,'RX',4,'S',5,'SRX',6,'X','NONE') mode_requested 
FROM v$lock l1 
JOIN v$lock l2 ON l1.id1 = l2.id1 AND l1.id2 = l2.id2 
JOIN v$session s1 ON l1.sid = s1.sid 
JOIN v$session s2 ON l2.sid = s2.sid 
JOIN dba_objects o ON l1.id1 = o.object_id 
WHERE l1.block = 1 AND l2.request > 0;

2. 生成死锁图（需诊断包）

-- 生成死锁报告 
SELECT dbms_deadlock.report_deadlock() FROM dual;

---

四、解除死锁操作

1. 强制终止会话

-- 找到阻塞会话 
SELECT sid, serial#, status, sql_id 
FROM v$session 
WHERE sid IN (SELECT sid FROM v$lock WHERE block > 0);
 
-- 终止会话（立即终止）
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

2. 级联终止（针对分布式死锁）

BEGIN 
  DBMS_SYSTEM.KILL_SESSION_DELAYED(
    sid => 25,
    serial => 54321,
    delay_seconds => 0);
END;

---

五、死锁预防策略

1. 事务优化原则

• 访问顺序：统一对多个表的DML操作顺序
• 批量提交：减少单事务锁持有时间
• 索引优化：确保通过索引访问数据

-- 检查缺失索引 
SELECT * FROM v$sql_plan 
WHERE operation = 'TABLE ACCESS' 
AND options = 'FULL' 
AND object_owner = 'SCOTT';

2. 锁超时设置

-- 设置事务级锁等待超时（单位：秒） 
ALTER SESSION SET ddl_lock_timeout = 30;

---

六、高级诊断工具

1. 使用AWR分析历史死锁

-- 查询历史死锁事件 
SELECT snap_id, instance_number, event, total_waits 
FROM dba_hist_system_event 
WHERE event_name = 'enq: TX - row lock contention'
ORDER BY snap_id DESC;

2. SQL Monitor分析

-- 查看导致死锁的SQL执行详情 
SELECT dbms_sqltune.report_sql_monitor(
  sql_id => 'g54fvx7r2kz3b',
  type => 'TEXT') 
FROM dual;

---

七、典型死锁场景解决方案

场景1：主键/唯一索引冲突

现象：并发插入相同唯一值
方案：

-- 使用SEQUENCE缓存优化 
ALTER SEQUENCE scott.emp_seq CACHE 1000;

场景2：外键未索引

现象：子表DML操作阻塞父表更新
方案：

-- 为外键字段添加索引 
CREATE INDEX scott.dept_fk_idx ON emp(deptno);

---

操作注意事项

1. 生产环境终止会话前 必须确认会话的业务影响
2. 跟踪文件分析 需关注row_wait_obj#和row_wait_file#定位具体数据块
3. 定期检查 使用DBMS_LOCK.REQUEST检测潜在锁冲突
4. 应用层优化 使用SELECT FOR UPDATE NOWAIT减少锁等待

通过以上方法，90%以上的Oracle死锁问题可在10分钟内定位并解决。对于复杂分布式死锁，建议启用10046跟踪进行深度分析。