记一次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#;
分析上图结果:
第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