记一次Oracle死锁/阻塞排查

1. 检查数据库确定 是否 真实存在死锁，若有 哪台机器哪个程序。

select username, lockwait, status, machine, program
from v$session
where sid in (select session_id from v$locked_object);

--Username：死锁语句所用的数据库用户；
--Lockwait   ：死锁的状态，如果有内容表示被死锁。
--Status       ：状态，active表示被死锁
--Machine   ：死锁语句所在的机器。
--Program   ：产生死锁的语句主要来自哪个应用程序。

2. 确定死锁后，还可以检查是哪个语句产生死锁等待。

select sql_text
from v$sql
where hash_value in
      (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));

3. 查询未提交事务的SQL，大概率是其引起。

select s.sid,
       s.username,
       s.osuser,
       s.program,
       to_char(s.LOGON_TIME, 'yyyymmdd hh24:mi:ss')       as LOGON_TIME,
       to_char(t.START_DATE, 'yyyymmdd hh24:mi:ss')       as START_DATE,
       s.status,
       (select q.SQL_TEXT from v$sql q where q.LAST_ACTIVE_TIME = t.START_DATE
                                         and rownum <= 1) as SQL_TEXT
from v$session s,
     v$transaction t
where s.sADDR = t.SES_ADDR;

4. 若找不到对应的User，则可以通过kill掉死锁的session进程

SELECT l.SESSION_ID, l.OS_USER_NAME, s.USERNAME, l.OBJECT_ID, l.ORACLE_USERNAME
FROM v$locked_object l,
     v$session s
WHERE l.SESSION_ID = s.SID;

5. 根据SessionID查询锁表语句

select sql_text
from v$sql
where hash_value in
      (select sql_hash_value from v$session where sid in (208));

6. 查看死锁

select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode, SESS.machine

from v$locked_object lo,
     dba_objects ao,
     v$session sess

where ao.object_id = lo.object_id
  and lo.session_id = sess.sid;

对v$locked_object被锁对象进行查询：

      SELECT l.session_id sid,
             s.serial#,
             l.locked_mode,
             l.oracle_username,
             l.os_user_name,
             s.machine,
             s.terminal,
             o.object_name,
             s.logon_time
        FROM v$locked_object l, all_objects o, v$session s
       WHERE l.object_id = o.object_id AND l.session_id = s.sid
       ORDER BY sid, s.serial#;

锁表sql

分析上图结果：

第1个Session的id为139对LN_DUE进行了锁表操作，第2个session的id为141对LN_DUE进行了锁表操作，于是死锁产生了。原因为代码中使用了两个Connection，前一个Connection执行完update语句未提交，导致行级锁未释放，第2个Connection又去对同一个表进行update，于是只能等待前一个connection释放行级锁。

解决：

alter system kill session '139,6151';

139是查询结果中的SID字段，6151是查询结果中的SERIAL