【Oracle】Oracle 阻塞，锁表的排查处理方法

1.阻塞的排查sql

select 'alter system kill session ''' || sid || ',' || serial# || ',@' ||
       inst_id || ''' immediate;' sqlstr,
       sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree, -- tree最后一个为最终阻塞者.
       a.inst_id,
       a.process,
       a.sid,
       a.serial#,
       a.sql_id,
       a.event,
       a.status,
       a.program,
       a.machine,
       connect_by_isleaf as isleaf,
       level as tree_level
  from gv$session a
 start with a.blocking_session is not null
connect by (a.sid || '@' || a.inst_id) = prior
           (a.blocking_session || '@' || a.blocking_instance);

字段解释：

字段名	作用
sqlstr	用于生成杀死会话的 ALTER SYSTEM KILL SESSION 命令字符串
tree	以树形结构表示会话之间的阻塞关系
inst_id	实例ID，表示会话所在的数据库实例
process	进程ID
sid	会话ID
serial#	会话的序列号
sql_id	当前正在执行的 SQL 语句的标识符
event	会话正在等待的事件
status	会话的状态
program	连接到数据库的程序名称
machine	执行会话的计算机名称
isleaf	表示是否为阻塞会话的叶子节点（1表示是叶子节点，0表示不是叶子节点）
tree_level	表示会话在阻塞关系树中的层级

 2.死锁的排查sql

--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、死锁的解决办法
--1）查找死锁的进程：
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,
l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS
FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
 
--2）kill掉这个死锁的进程：
alter system kill session 'sid,serial#'; （其中sid=l.session_id）
 
--3）如果还不能解决：
select pro.spid from v$session ses,v$process pro where ses.sid=XX and ses.paddr=pro.addr;
其中sid用死锁的sid替换
ps -ef|grep spid
其中spid是这个进程的进程号，kill掉这个Oracle进程

示例：

我在一台电脑上执行FOR UPDATE表，在另一台电脑对同张表执行UPDATE某行数据，造成阻塞。

执行阻塞排查sql，可以看到是SID为607的会话造成了SID为149的会话阻塞了。我们可以选择执行SQLSTR拼接出来的处理命令来解决这个阻塞。