Oracle TX 锁快速查杀处理办法

JiekeXu_DBA
707 阅读3分钟

根据保护的数据不同，ORACLE的数据库锁分为以下几大类：

1.DML锁（data locks数据锁），用于保护数据的完整性；

2.DDL锁（dictionary locks字典锁），用于保护数据库对象的结构，例如表、索引的结构定义；

3.内部锁或闩（internal locks or latches），用于保护内部结构

在 Oracle 数据库中，DML锁主要包括 TM 锁和 TX 锁，其中 TM 锁称为表级锁，TX 锁称为事务锁或行级锁。

当 Oracle 执行 DML 语句时，系统自动在所要操作的表上申请 TM 类型的锁。当 TM 锁获得后，系统再自动申请 TX 类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查 TX 锁相容性时就不用再逐行检查锁标志，而只需检查 TM 锁模式的相容性即可，大大提高了系统的效率。TM 锁包括了 SS、SX、S、X 等多种模式，在数据库中用 0－6 来表示。不同的 SQL 操作产生不同类型的 TM 锁。

在数据行上只有 X 锁（排他锁）。在 Oracle 数据库中，当一个事务首次发起一个 DML 语句时就获得一个 TX 锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML 语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX 锁被释放，其他会话才可以加锁。

当 Oracle 数据库发生 TX 锁等待时，如果不及时处理常常会引起 Oracle 数据库挂起，或导致死锁的发生，产生 ORA-60 的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

ORACLE里锁有以下几种模式: 

0：none 
1：null 空 
2：Row-S 行共享(RS)：共享表锁，sub share  
3：Row-X 行独占(RX)：用于行的修改，sub exclusive  
4：Share 共享锁(S)：阻止其他DML操作，share 
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive  
6：exclusive 独占(X)：独立访问使用，exclusive

上面提到 TX 锁也称事务锁或者行级锁，是控制数据库并发访问的一项重要技术，也是数据完整性和一致性的重要保证。下面就一起看看如何快速查杀锁。

方案 1

--行锁查看

col LOCK_ID1 for a10
col "Locked Mode" for a15
col MACHINE for a20 
set line 345 pages 345
col "Kill" for a40 
select s.SID,s.SERIAL#,s.MACHINE,s.TYPE,l.TYPE,l.CTIME,l.BLOCK,l.REQUEST,l.LMODE,
       decode(l.lmode,
              0,
              'None',
              1,
              'Null',
              2,
              'Row-S (SS)',
              3,
              'Row-X (SX)',
              4,
              'Share',
              5,
              'S/Row-X (SSX)',
              6,
              'Exclusive',
              substr(to_char(l.lmode), 1, 13)) as "Locked Mode",
       DECODE(L.TYPE,
              'MR',
              'File_ID:' || L.ID1,
              'TM',
              t.NAME,
              'TX',
              'USN:' || to_char(TRUNC(L.ID1 / 65536)) || 'RWO:' ||
              nvl(r.NAME, 'None'),
              L.ID1) as LOCK_ID1,
       'alter system kill session ''' || s.SID || ',' || s.SERIAL# || '''immediate;' as "Kill"
  from gv$process p
 inner join gv$session s
    on s.PADDR = p.ADDR
 inner join v$lock l
    on l.SID = s.SID
  left join sys.obj$ t
    on l.ID1 = t.obj#
  left join sys.obj$ r
    on s.ROW_WAIT_OBJ# = r.obj#
 where 1 = 1
   and l.TYPE != 'MR' 
 and l.TYPE  = 'TM'
   and l.lmode = 3
 order by s.SID;

通过上方 SQL 可定位锁以及查杀语句，但无法确定锁源头，可通过如下 SQL 查到 SID 定位锁源头信息，如查出的锁源头（SID 位于最左边）在上边结果集中出现，则可使用上面 SQL 直接查杀锁，方便快速。

set linesize 132
col sid format a12
col event format a30
col sql_text format a40
col object_name format a25
WITH sessions AS
(SELECT  /*+materialize*/
         sid
        ,sql_id
        ,event
        ,blocking_session
        ,row_wait_obj#
FROM gv$session)
SELECT LPAD(' ', LEVEL ) || sid sid
      ,sql_id
      ,event
      ,owner||decode(owner,null,null,'.')||object_name object_name
      ,substr(sql_text,1,40) sql_text
FROM sessions s
LEFT OUTER JOIN dba_objects ON (object_id = row_wait_obj#)
LEFT OUTER JOIN v$sql USING (sql_id)
WHERE sid IN (SELECT blocking_session FROM sessions)
OR blocking_session IS NOT NULL
CONNECT BY PRIOR sid = blocking_session
   START WITH blocking_session IS NULL;

方案 2

使用 v$session 中的 final_blocking_instance 和 final_blocking_session 定位锁源头，具体 SQL 如下：

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;
DB_KILL_SESSION
--------------------------------------------------
alter system kill session '161,5579,@1' immediate;
alter system kill session '161,5579,@1' immediate;

--如下，可通过操作系统层面直接 kill 查杀锁。
SQL> 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;
   INST_ID OS_KILL_SESSION
---------- --------------------------------
         1 kill -9 12349

最后在分享一些通用的杀会话的 SQL 脚本：

1、kill某个等待事件对应的spid：
set linesize 260 pagesize 10000
select 'kill -9 ' || a.spid
  from v$process a, v$session b
 where a.addr = b.paddr
   and a.background is null
   and b.type = 'USER'
   and b.event like '%' || '&eventname' || '%'
   and b.status = 'ACTIVE';

-- 对应的alter system kill session的语法：

set linesize 260 pagesize 1000
col machine for a50
col kill_session for a60;
select machine,
       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
       status
  from v$session
 where type='USER' and event like '%event_name%' and status = 'ACTIVE';

2、kill某个sql_id对应的spid：
set linesize 260 pagesize 10000
select 'kill -9 ' || a.spid
  from v$process a, v$session b
 where a.addr = b.paddr
   and a.background is null
   and b.type = 'USER'
   and b.sql_id = '&sql_id'
   and b.status = 'ACTIVE';

-- 对应的alter system kill session的语法：

set linesize 260 pagesize 10000
col machine for a60
select machine,
       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;',
       status
  from v$session
 where sql_id = '&sql_id' and type='USER' and status='ACTIVE';

3、被kill会话的类型：
set linesize 260 pagesize 10000
select b.osuser,b.machine,b.program,b.sql_id,b.PREV_SQL_ID,a.spid,to_char(LAST_CALL_ET) as seconds,b.BLOCKING_SESSION,b.BLOCKING_INSTANCE
  from v$process a, v$session b
 where a.addr = b.paddr
   and a.inst_id=b.inst_id
   and a.background is null
   and b.type = 'USER'
   and b.event='&event_name'
   and b.status = 'ACTIVE';

4、blocking会话类型和kill blocking会话：
set linesize 260 pagesize 10000
col machine for a50
col kill_session for a60
SELECT
    blocking_instance,
    blocking_session,
    BLOCKING_SESSION_STATUS,
    FINAL_BLOCKING_INSTANCE,
    FINAL_BLOCKING_SESSION,
    COUNT(*)
FROM
    v$session
WHERE
    upper(event) LIKE '%&cursor%'
GROUP BY
    blocking_instance,
    blocking_session,
    BLOCKING_SESSION_STATUS,
    FINAL_BLOCKING_INSTANCE,
    FINAL_BLOCKING_SESSION
    order by blocking_instance,count(*);

-- kill blocking会话

select 
       inst_id,
       machine,
       'alter system kill session ' || ''''||sid|| ',' || serial# ||''''|| 'immediate;' kill_session,
       status
  from gv$session a
where a.type='USER' and (a.inst_id,a.sid) in 
(
select 
BLOCKING_INSTANCE,
BLOCKING_SESSION 
from v$session
where upper(event) like '%&cursor%'
)
order by inst_id;

5、所有含有关键字“LOCAL=NO”的进程是Oracle数据库中远程连接进程的共同特点，因此通过以下命令可以kill掉所有的进程
ps -ef|grep -v grep|grep LOCAL=NO|awk '{print $2}'|xargs kill -9
avatar
文章
阅读
粉丝