lock to deteck in oracle

 

 

0,5,10 0-23 * * * /home/oracle/utility/blocker/detect_blocker.sh db 120 > /home/oracle/utility/trace/detect_blocker_db.log 2>&1


file 1:

 echo_usage()
{
        echo "Usage : `basename $0` [SID] <sleep time>"
        echo $*
        echo "Cause: no SID or Database is not running on server"
        exit 0
}

if [ $# -lt 2 ]
then
        echo_usage
fi

#
# See whether the DB is actually running on this machine or not
#

export ORACLE_SID=$1
ps -ef | grep -v grep | grep ora_smon_${ORACLE_SID} > /dev/null
if [ $? -ne 0 ]
then
        echo_usage
fi

export PATH=$PATH:/usr/local/bin
export ORAENV_ASK=NO

. oraenv >/dev/null
export ORAENV_ASK=

export ROOT_DIR=/home/oracle
export SCRIPT_DIR=$ROOT_DIR/utility/blocker
export LOGFILE_DIR=$ROOT_DIR/utility/log
export TMP_DIR=$ROOT_DIR/utility/tmp

EXT="`date '+%y%m%d%H%M%S'`"

FILE1=/tmp/detect_blocker1_$ORACLE_SID.$EXT
FILE2=/tmp/detect_blocker2_$ORACLE_SID.$EXT

OPCMsgCmd=/opt/OV/bin/OpC/opcmsg  


SLEEPTIME=$2

${SCRIPT_DIR}/check_blocker  $ORACLE_SID | grep -v 'DO NOT KILL' | grep KILL > $FILE1;
sleep $SLEEPTIME;
${SCRIPT_DIR}/check_blocker  $ORACLE_SID | grep -v 'DO NOT KILL' | grep KILL > $FILE2;

while read LINE; do
COMPSTR=`echo $LINE | awk '{print $2}'`
if [ `grep $COMPSTR $FILE2 | wc -l` -eq 1 ]; then
   echo 'Blocker detected as : ';
   echo
   echo 'Oracle Session ID/Serial#      : ' $COMPSTR
   OVOMSG='DB :'${ORACLE_SID}'-Blocker Detected Oracle Session ID/Serial#      : '${COMPSTR}', Please keep monitoring system
and donnot kill the blocker until warning messages is exceeding at least 3 times with the same blocker.'
   echo $OVOMSG
   $OPCMsgCmd s=critical a=db  o=blocker  msg_grp=db  msg_t="${OVOMSG}"
fi;
done < $FILE1

#####
file 2:

dbngd3[/home/oracle/utility/blocker][dbpr] >more check_blocker
#!/usr/bin/sh
#
# This script check whether there is blockers inside the
# database. The script accept DB name as parameter
# and do the checking.
#

echo_usage()
{
        echo "Usage : `basename $0` [SID]"
        echo $*
        exit 1
}

if [ $# -lt 1 ]
then
        echo_usage
fi

ORACLE_SID=$1
BASEDIR=/home/oracle/utility/blocker

ORAENV_ASK=NO
PATH=/usr/local/bin:$PATH
. oraenv > /dev/null 2>&1
#
# See whether the DB is actually running on this machine or not
#

EXT="`date '+%y%m%d%H%M%S'`"
echo "Calling check_blocker.sh $1 at time  $EXT" >> ${BASEDIR}/check_blocker.log
ps -ef | grep -v grep | grep ora_smon_${ORACLE_SID} > /dev/null
if [ $? -ne 0 ]
then
        echo_usage
fi

#
# For support use
#
EXT="`date '+%y%m%d%H%M%S'`"
nohup sqlplus -s oper/oper123 @${BASEDIR}/lck_sql TRUE >/tmp/locktree.$ORACLE_SID.$EXT 2>&1 &

#
# Call light weight check_blocker
sqlplus -s oper/oper123 @${BASEDIR}/lck_sql_lite
#

# Add process listing to log file
nohup ps -elf >> /tmp/oralocktree.$ORACLE_SID.$EXT 2>&1 &


#####
file 3:

Rem LCK_SQL.SQL
Rem ===========

Rem This script reports on conflicting locks using the
Rem procedure LOCK_CONFLICTS created by teh script LCK_PROC.BLD
Rem
Rem It also reports the SQL statements currently in use
Rem by the processes holding / requiring the locks

set serveroutput on size 1000000
set lines 70 echo off trim on;
timing start conflicts;
rem spool $SCRIPT_DIR/lck_sql;
rem spool lck_sql.log
rem execute lock_conflicts('TRUE');
execute lock_conflict('&1');
timing stop;
rem spool off;
set lines 78;
exit

#####
file 4:
Rem LCK_SQL_LITE.SQL
Rem ================

Rem This script reports on conflicting locks using the
Rem procedure LOCK_CONFLICTS created by teh script LCK_PROC.BLD
Rem
Rem It also reports the SQL statements currently in use
Rem by the processes holding / requiring the locks

set serveroutput on size 1000000
set lines 70 echo off trim on;
timing start conflicts;
spool /home/dba/oracle/utility/blocker/lck_sql;
--alter session set events '10046 trace name context forever, level 12' ;
execute lock_conflict_lite;
timing stop;
spool off;
set lines 78;
exit

####
file 5:


create or replace procedure sys.lock_conflict_lite is

  level_cnt     integer;
  indent        varchar2(10);

  type sid_tab is table of integer index by binary_integer;
  type serial_tab is table of integer index by binary_integer;
  type dep_cnt_tab is table of integer index by binary_integer;
  type kill_nokill_tab is table of varchar2(11) index by binary_integer;
  type cpid_tab is table of varchar2(6) index by binary_integer;
  type spid_tab is table of varchar2(6) index by binary_integer;
  type machine_tab is table of varchar2(64) index by binary_integer;

  kill_sid      sid_tab;
  kill_serial   serial_tab;
  kill_dep_cnt  dep_cnt_tab;
  kill_kill_nokill      kill_nokill_tab;
  kill_cpid     cpid_tab;
  kill_spid     spid_tab;
  kill_machine  machine_tab;

  kill_array    integer;


  cursor top_level_lock_cur is
     select req_sid sid
       from v_db_lock
     minus                                  -- MINUS
     select wait_2.sid
--       from v_db_lock_tab wait_2                   -- I am waiting for something
       from v_$lock wait_2                   -- I am waiting for something
      where wait_2.request > wait_2.lmode;

  procedure report_sid
        ( p_sid in      integer
        ) is

  cursor session_cur
        ( p_sid in number
        ) is
        select s.sid kill_sid
             , s.serial# kill_serial
             , s.sid      ora_sid
             , s.username ora_username
             , s.osuser   os_username
             , s.process  os_process
             , s.machine  os_machine
             , s.sql_address sql_address
             , s.sql_hash_value  sql_hash_value
             , p.spid     os_shadow_process
             , decode( s.username , 'REPLOG'    , 'DO NOT KILL'
                                  , 'DBV_MONTR' , 'DO NOT KILL'
                                  , 'SYS'       , 'DO NOT KILL'
                                  , NULL        , 'DO NOT KILL'
                                                , 'KILL' ) kill_nokill
          from v$process p
             , v$session s
         where s.sid = p_sid
           and p.addr (+) = s.paddr;

  session_rec session_cur%ROWTYPE;

  begin
    open session_cur ( p_sid );
    fetch session_cur into session_rec;
    close session_cur;

    if level_cnt = 1 then
       kill_array := kill_array + 1;
       kill_sid ( kill_array ) := session_rec.kill_sid;
       kill_serial ( kill_array ) := session_rec.kill_serial;
       kill_kill_nokill ( kill_array ) := session_rec.kill_nokill;
       kill_dep_cnt ( kill_array ) := 0;
       kill_cpid ( kill_array ) := session_rec.os_process;
       kill_spid ( kill_array ) := session_rec.os_shadow_process;
       kill_machine ( kill_array ) := session_rec.os_machine;
    elsif kill_array > 0 then
          kill_dep_cnt ( kill_array ) := kill_dep_cnt ( kill_array ) + 1;
    end if;


  end report_sid;

begin

   kill_array := 0;

   for top_level_lock_rec in top_level_lock_cur
   loop
    level_cnt := 1;
    indent := rpad(' ',level_cnt,chr(9));
    report_sid ( top_level_lock_rec.sid );
   end loop;

  dbms_output.put_line(indent);
  dbms_output.put_line('>> **************************************************************** <<');
  dbms_output.put_line('>>                                                                  <<');
  dbms_output.put_line('>> The following is a list of the sessions that hold top level locks<<');
  dbms_output.put_line('>> and therefore most likely to clear the blockage along with the   <<');
  dbms_output.put_line('>> number of sessions currently being blocked by the holder.        <<');
  dbms_output.put_line('>>                                                                  <<');
  dbms_output.put_line('>>  SID,Serial#    Client,Shadow OSPID  DependentCount  Kill Advice <<');
  dbms_output.put_line('>>                                                                  <<');
  for kill_occurance in 1 .. kill_array
  loop
    dbms_output.put   ('>>');
    dbms_output.put   (    lpad(kill_sid(kill_occurance),5,' '));
    dbms_output.put   (        ',');
    dbms_output.put   (          rpad(kill_serial(kill_occurance),7,' '));
    dbms_output.put   (                 ' ');
    dbms_output.put   (          lpad(kill_cpid(kill_occurance)||'('||kill_machine(kill_occurance)||')',16,' '));
    dbms_output.put   (        ',');
    dbms_output.put   (          rpad(kill_spid(kill_occurance),5,' '));
    dbms_output.put   (                 '           ');
    dbms_output.put   (                       lpad(kill_dep_cnt(kill_occurance),4,' '));
    dbms_output.put   (                                     '   ');
    dbms_output.put   (                                         rpad(kill_kill_nokill(kill_occurance),11,' '));
    dbms_output.put   (                                                   ' <<');
    dbms_output.new_line;
  end loop;

  dbms_output.put_line('>>                                                                  <<');
  dbms_output.put_line('>> **************************************************************** <<');

end;


--
file 6
create or replace view sys.v_yict_lock as
select req_0.sid req_sid
          , wait_1.sid wait_sid
--       from v_yict_lock_tab req_0                    -- people who have a lock
--         , v_yict_lock_tab wait_1                   -- and someone waiting for this lock
       from v_$lock req_0                    -- people who have a lock
         , v_$lock wait_1                   -- and someone waiting for this lock
      where wait_1.type = req_0.type
        and wait_1.sid != req_0.sid
        and wait_1.request > wait_1.lmode   -- What I want is more than what I have
        and wait_1.id1 = req_0.id1
        and wait_1.id2 = req_0.id2
        and ( ( req_0.lmode != 0 and
                not exists ( select null
                              from lock_compatibility_mode  -- What you have I want
                             where lmode1 = req_0.lmode
                               and lmode2 = wait_1.request )
              )
            or
              ( not exists ( select null
                               from lock_compatibility_mode  -- What you want I want
                              where lmode1 = req_0.request
                                and lmode2 = wait_1.request ) and
                req_0.request > wait_1.request               -- You want more than I do
              )
            )
       --
       -- exclude info of blocker on IT_ENTITY_LOCK
       --
       and wait_1.sid not in
           (select s.sid
            from   v$session s
            where  s.row_wait_obj# in
                 (select o.object_id
                  from   dba_objects o
                  where  o.object_name = 'IT_ENTITY_LOCK'))

---file 7
select * from sys.LOCK_COMPATIBILITY_MODE


lmode1 lmode2
0    0
0    1
0    2
0    3
0    4
0    5
0    6
1    0
1    1
1    2
1    3
1    4
1    5
1    6
2    0
2    1
2    2
2    3
2    4
2    5
3    0
3    1
3    2
3    3
4    0
4    1
4    2
4    4
5    0
5    1
5    2
6    0
6    1

 

sample 1:

 ORACLE关于锁和V$LOCK的分析 

 

    为了实现并发，oracle数据库使用了锁机制。要了解锁，首先要了解视图v$lock。

v$lock这个视图列出 Oracle 服务器当前拥有的锁以及未完成的锁请求。如果你觉着 session 处于等待事件队列当中，那你应该检查视图v$lock。

 

v$lock中的常用列有以下列：

     sid：持有锁的会话SID，通常与v$session关联。

 

     type：锁的类型，其中TM表示表锁或DML锁，TX表示行锁或事务锁，UL表示用户锁。我们主要关注TX和TM两种型的锁，其它均为系统锁，会很快自动释放，不用关注。当 Oracle执行 DML 语句时，系统自动在所要操作的表上申请 TM 类型的锁。当 TM锁获得后，系统再自动申请 TX 类型的锁，并将实际锁定的数据行的锁标志位进行置位。TM 锁包括了SS 、 SX、 S 、X 等多种模式，在数据库中用 0 －6 来表示。不同的 SQL 操作产生不同类型的 TM锁。

 

     lmode：会话保持的锁的模式。

          0＝None；

          1＝Null ;

          2＝Row-S (SS,行级共享锁，其他对象SQL语句只能查询这些数据行)，sql操作有select for update、lock for update、lock row share；

          3＝Row-X (SX,行级排它锁,在提交前不允许做DML操作)，sql操作有insert、update、delete、lock row share；

          4＝Share(共享锁)，sql操作有create index、lock share；

          5＝S/Row-X (SSX,共享行级排它锁)，sql操作有lock share row exclusive；？？

          6＝Exclusive(排它锁)，alter table、drop table、drop index、truncate table、look exclusive等DDL

     注释：Row-S (SS,行级共享锁）和Row-X (SX,行级排它锁）中的第一个S 表示表被共享，如果表不被共享，就谈不上其下级的行级锁是否被共享还是排他了。表级别为X，行级锁就不被其他会话访问，所以也就没XX或XS模式，只有X模式。

 

     ID1,ID2:  ID1,ID2的取值含义根据type的取值而有所不同，对于TM 锁ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息，ID2 值为0（即表示type=TM时，用不到ID2列，故将其置为零）；对于TX 锁ID1以十进制数值表示该事务所占用的回滚段号和事务槽slot number号，其组形式: 0xRRRRSSSS,RRRR=RBS/UNDO NUMBER,SSSS=SLOT NUMBER，ID2 以十进制数值表示环绕wrap的次数，即事务槽被重用的次数。实际上这两个字段构成了事务在回滚段中的位置。

 

当锁产生时，以下图为例说明v$lock：

1、图中存在两个session分别是133和135，session135的BLOCK=1意味着该session拥有一个锁，并阻塞了其他session的对该锁的请求。该锁的类型由TY定义，模式由LMODE字段定义；

2、session133的request=6说明该session正在等待一个lmode为6的锁，而该锁的拥有者正是session135。

3、对于TM锁，ID1Z值就是加锁的段对象，可以是表或者表分区，此时ID2一般为0；对于TX锁，这两个字段构成该事务在回滚段中的位置。

 

对于死锁的处理流程：

1,查找锁：

select ls.osuser os_user_name,

ls.username user_name,

ls.type lock_type,

o.object_name object,

decode(ls.lmode,1,null,2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null) lock_mode,

o.owner,

ls.sid,

ls.serial# serial_num,

ls.id1,ls.id2,

ls.paddr

 from sys.dba_objects o,

(select s.osuser,s.username,l.type,s.paddr,l.lmode,s.sid,s.serial#,l.id1,l.id2  from v$session s,v$lock l where s.sid=l.sid) ls

where o.object_id=ls.id1 and o.owner<>'SYS' order by o.owner,o.object_name

2，查找spid

select a.spid,a.username,b.program  from v$process a,v$session b where a.addr=b.paddr and a.addr='000007FF2DC8E578';

3、kill进程

alter system kill session 'sid,serial#' immediate;

orakill sid spid

sid：表示要杀死的进程属于的实例名

thread：是要杀掉的线程号，即第2步查询出的spid。     

(

ALTER SYSTEM KILL SESSION

关于KILL SESSION Clause ，如下官方文档描述所示，alter system kill session实际上不是真正的杀死会话，它只是将会话标记为终止。等待PMON进程来清除会话。

    select sid,serial# from v$session where username='DEMO';查询用户的 sid,serial# 

 

可以使用ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE 来快速回滚事物、释放会话的相关锁、立即返回当前会话的控制权。

)

 

参考：

http://blog.csdn.net/haiross/article/details/52703419