首页 > 数据库技术 > 详细

Oracle 几条查询TX锁的语句

时间:2021-07-29 22:30:47      阅读:10      评论:0      收藏:0      [点我收藏+]

TX称为事务锁或者行级锁,控制数据库并发访问的一项重要技术,也是数据完整性和一致性的重要保证。

遇到TX锁的时候,查询v$lock和v$session视图,定位LMODE和REQUEST类型的互斥会话进行查杀,可以直接使用locking_session、blocking_instance、final_blocking_instance和final_blocking_session字段进行定位。对于锁层次的排查可以重复查询v$session来确定,但是如果锁有很多层,则过于低效。

可以使用Oracle的SYS_CONNECT_BY_PATH函数,9i开始DBA可以通过SYS_CONNECT_BY_PATH函数将父节点到当前行的内容以“路径”或者层次的形式显示出来。该功能刚好符合我们递归查询锁层次的需求。
使用以下的语句查询锁信息

SQL> select 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,
            sys_connect_by_path(a.SID || a.inst_id, ‘<-‘) tree,
            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);

a.inst_id,   会话所在的节点号
a.process,   客户端进程号,与v$process 中的spid不是同一个。
a.sid,       会话信息
a.serial#,    会话信息
a.sql_id,    会话信息
a.event,      会话信息
a.status,  会话信息
a.program,   会话信息
a.machine,   会话信息  
connect_by_isleaf as isleaf  是否源头,0代表否,1代表是。
TREE  树形结构,例如 <- 143@2 <-142@2 <-171@1  ,从左到右依次表示为 节点2 的143会话被节点2的142阻塞,而节点2的152会话又被节点1的会话171阻塞。所以节点1的171是锁的源头。
TREE_LEVEL,树的层次。

锁的源头查杀方法有两种:
1)通过ISLEAF进行筛选,直接查杀锁源头:语句如下

select ‘alter system kill session ‘‘‘||s.sid||‘, ‘||s.serial#||‘‘‘;‘ as standalone_kill_session
       ,‘alter system kill session ‘‘‘||s.sid||‘, ‘||s.serial#||‘, @‘||s.inst_id||‘‘‘;‘ as rac_kill_session
       ,s.* 
from gv$session s
where 1=1
and s.schemaname <> ‘SYS‘
and s.status <> ‘INACTIVE‘
;

select ‘alter system kill session ‘‘‘ || sid || ‘‘ || ‘,‘ || serial# || ‘,@‘ ||
       inst_id || ‘‘‘ immediate;‘ db_kill_session
  from (select 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,
               sys_connect_by_path(a.SID || ‘@‘ || a.inst_id, ‘ <- ‘) tree,
               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))
 where isleaf = 1
 order by tree_level asc;

select inst_id, ‘kill -9 ‘ || spid os_kill_session
  from (select p.inst_id,
               p.spid,
               a.sid,
               a.serial#,
               a.sql_id,
               a.event,
               a.status,
               a.program,
               a.machine,
               connect_by_isleaf as isleaf,
               sys_connect_by_path(a.SID || ‘@‘ || a.inst_id, ‘ <- ‘) tree,
               level as tree_level
          from gv$session a, gv$process p
         where a.inst_id = p.inst_id
           and a.paddr = p.addr
         start with a.blocking_session is not null
        connect by (a.sid || ‘@‘ || a.inst_id) = prior
                   (a.blocking_session || ‘@‘ || a.blocking_instance))
 where isleaf = 1
 order by tree_level asc; 

2)借助v$session中的final_blocking_instance和final_blocking_session定位锁源头,语句如下:

SQL> select ‘alter system kill session ‘‘‘ || ss.sid || ‘‘ || ‘,‘ || ss.serial# || ‘,@‘ ||
       ss.inst_id || ‘‘‘ immediate;‘ db_kill_session
  from gv$session s, gv$session ss
 where s.final_blocking_session is not null
   and s.final_blocking_instance = ss.inst_id
   and s.final_blocking_session = ss.sid
   and s.sid <> ss.sid;

select p.inst_id, ‘kill -9 ‘ || p.spid os_kill_session
  from gv$session s, gv$session ss, gv$process p
 where s.final_blocking_session is not null
   and s.final_blocking_instance = ss.inst_id
 and s.final_blocking_session = ss.sid
   and ss.paddr = p.addr
   and ss.inst_id = p.inst_id
   and s.sid <> ss.sid;

注意:杀语句的时候一定要带上immeidate,如果不带,有可能会出现session状态为killed的状态。

Oracle 几条查询TX锁的语句

原文:https://blog.51cto.com/lishiyan/3227385

(0)
(0)
   
举报
评论 一句话评论(0
分享档案
最新文章
教程昨日排行
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!