oracle 锁

1.查看session

SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';' "Deadlock"
  FROM v$session
 WHERE sid IN (SELECT sid
                   FROM v$lock
                   WHERE block = 1);

查看 select sid ,serial# from v$session where sid in 

(select sid from v$lock where block = 1) ;

然后进行kill session

    执行alter system kill session '391,48398'(sid为391);

注意： 应当注意对于sid在100以下的应当谨慎，可能该进程对应某个application，如对应某个事务,可以kill.

--查看锁的情况

SQL>@LOCK.SQL

USER_NAME    sid,serial OBJECT_NAME     TY LMODE LOCK_STATUS       STATUS   SECOND OSUSER   SPID  PROCE PROGRAM                        CTIME LOGON_TIME

------------ ---------- --------------- -- ----- ----------------- -------- ------ -------- ----- ----- ------------------------- ---------- -------------------

SYSTEM       (145,169)  SYSTEM.TES      TX     6 blocked_N_R=0     INACTIVE    451 oracle   15300 15297 sqlplus@node1 (TNS V1-V3)       8154 2011-03-04 05:41:11

  --SCOTT    (159,22)   SYSTEM.TES      TX     0 waiting_Request_6 ACTIVE     8133 oracle   15337 15335 sqlplus@node1 (TNS V1-V3)       8133 2011-03-04 05:41:2

如果要查看159在执行的sql语句

--方式一通过sid

SQL>

select s.sid, s.serial#, s.status, s.username, t.sql_text

  from v$session s, v$sqltext t

 where s.sql_address = t.address

   and s.sid = &sid

 order by s.sid, t.piece;

 SID    SERIAL# STATUS   USERNAME  SQL_TEXT

----------------------------------------------

 159         22 ACTIVE   SCOTT          select * from system.test for update

--方式二 查询v$locked_object,得到undo号，槽号，序列号，通过查询v$transaction

--通过sid查询no row时，可以用该方法查询当前使用事务的sql_text

SQL> select xidusn,xidslot,xidsqn from v$locked_object where session_id=145;

 XIDUSN    XIDSLOT     XIDSQN

---------- ---------- ----------

         9         31        341

SQL>select e.sql_text, d.osuser,d.username

from v$transaction c, v$session d, v$sqlarea e

where d.taddr = c.addr and e.address = d.prev_sql_addr

and c.xidusn = 9

and c.xidslot =31

and c.xidsqn = 341;

SQL_TEXT                       OSUSER   USERNAME

------------------------------ -------- ------------

select /*+ rule*/ lpad('--',de oracle   SYSTEM

锁：TM锁，TX锁

TM锁：用来防止和事务冲突的DDL操作。

TX锁：为了保证数据的一致性。

Insert发生阻塞的唯一情况是用户拥有一个建有主健（唯一约束）的表。两个会话同时视图向表中插入相同数据，其中一个会话被阻塞，直到另外一个会话提交或回滚。

Update，delete

行级别排他锁（RX row exclusive）

当我们进行DML时会自动在被更新的表上加RX锁，或者也可以通过lock table t1 in row exclusive mode;命令显式添加RX锁。在该锁定模式下，允许其他的事务通过DML语句修改相同表里的其他数据行，或者通过lock命令对相同表添加RX锁定，但不允许其他事务对相同的表添加排他锁（X锁）。

行级别共享锁（RS row shared）

通常是通过select 。。。 for update语句添加的，同时该方法也是我们用来手工锁定某些记录的主要方法。比如，当我们在查询某些记录的过程中，不希望其他用户对查询的记录进行更新操作，则可以发出这样的语句。当数据使用完毕时，直接rollback命令将锁定解除。当表上添加了RS锁定以后，不允许其他事务对相同的表添加排他锁，但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行。

共享锁（S  share）

通过lock table in share mode 命令添加S锁，在锁定模式下，不允许任何用户更新表，但是允许其他用户发出select 。。。 from table for update 命令对表添加RS锁。（这里有点问题，10g的测试结果，select for update给出的是rx锁，并且在s锁已经添加的情况下无法添加rx锁）

排他锁（X exclusive）

通过lock table in exclusive mode命令添加X锁。在该锁定模式下，其他用户不能对表进行任何的DML和DDL操作，该表上只能查询。

共享行级别排他锁（SRX share row exclusive）

通过lock table in share row exclusive mode命令添加SRX锁。该锁定模式比行级别排他锁和共享锁的级别都要高，这时不能对相同的表进行DML操作，也不能添加共享锁。

Oracle数据库中的各SQL语句所参数的表级锁的情况

--产生在数据库中持有的锁的报表

--查看某个数据库对象是否被锁

--查询数据库阻塞其他会话的锁所持有的对象

--查看谁阻塞了谁

--所有TM阻塞情况

--oracle TX锁的等待序列

--用户锁等待阻塞信息报告

--持有锁的会话

-------------------

--latch阻塞的检索

--用于鉴别系统中闩性能的脚本

--列举用于闩竞争的信息

--检索闩睡眠率

--使用v$session wait视图来鉴别闩竞争

------------------------------------------脚本--------------------------------

select s.username, s.sid, l.type, l.id1, l.id2, l.lmode, l.request, p.spid PID
from v$lock l, v$session s, v$process p
where s.sid = l.sid
and p.addr = s.paddr
and s.username is not null
order by id1, s.sid, request;

--产生在数据库中持有的锁的报表

select b.sid, c.username, c.osuser, c.terminal,
       decode(b.id2, 0, a.object_name, 'Trans-' || to_char(b.id1)) object_name,
       b.type,
       decode(b.lmode, 0, '-Waiting-',
                       1, 'Null',
                       2, 'Row Share',
                       3, 'Row Excl',
                       4, 'Share',
                       5, 'Sha Row Exc',
                       6, 'Exclusive', 'Other') "Lock Mode",
       decode(b.request, 0, ' ',
                         1, 'Null',
                         2, 'Row Share',
                         3, 'Row Excl',
                         4, 'Share',
                         5, 'Sha Row Exc',
                         6, 'Exclusive', 'Other') "Req Mode"
from dba_objects a, v$lock b, v$session c
where a.object_id(+) = b.id1
and b.sid = c.sid
and c.username is not null
order by b.sid, b.id2;
--After Run

SID USERNAME OSUSER TERMINAL OBJECT_NAME TYPE Lock Mode Req Mode
----------------------------------------------------------------------

2611 AICHNL weblogic Trans-47841323 TX Exclusive
2613 AICHNL weblogic Trans-47710247 TX Exclusive




--查看某个数据库对象是否被锁

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set wrap off
set linesize 200
col username for a10
col logon_time for a20
col lock_level for a10
col owner for a10
col object_name for a30
col object_type for a15
col status for a10
col program for a30
col osuser for a10
SELECT /*+ rule */ s.username,s.logon_time,
decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) "LOCK_LEVEL",
o.owner,o.object_name,o.object_type,
s.sid,s.serial#,p.spid,s.status,s.program,s.osuser
FROM v$session s,v$lock l,dba_objects o,v$process p
WHERE l.sid=s.sid AND p.addr=s.paddr
and o.owner='&object_owner'
and o.object_name='&object_name'
and l.id1=o.object_id(+)
AND s.username is NOT Null;

--After Run,object_owner=SCOTT,onject_name=TTT

USERNAME LOGON_TIME LOCK_LEVEL OWNER OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------

SCOTT 2011-02-21 21:34:25 TABLE LOCK SCOTT TTT TABLE
--下接

SID SERIAL# SPID STATUS PROGRAM OSUSER
--------------------------------------------------------------------------

159 9 9994 INACTIVE sqlplus@node2 (TNS V1-V3) oracle


--查询数据库阻塞其他会话的锁所持有的对象

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set wrap off
set linesize 200
col username for a10
col logon_time for a20
col lock_level for a10
col owner for a10
col object_name for a30
col object_type for a15
col status for a10
col program for a30
col osuser for a10
SELECT /*+ rule */
s.username,s.sid,s.serial#,s.status,s.program,s.osuser,s.logon_time,
decode(l.type,'TM','TABLE LOCK','TX','ROW LOCK',NULL) "LOCK_LEVEL",
o.owner "object_owner",o.object_name,o.object_type
FROM v$session s,v$lock l,v$locked_object lo,dba_objects o
WHERE l.sid=s.sid
AND lo.session_id=s.sid
AND l.block=1
AND lo.object_id=o.object_id
AND s.username is NOT Null;

--测试

--Session A

SQL>sqlplus scott/tiger
SQL>update ttt set id=7;
--Session B

SQL>sqlplus / as sysdba
SQL>update scott.ttt set id = 9;
--Session C 观察者

--Run script

--After run,B无法运行，A持有的对象（TTT）上的锁，阻塞了B的运行

USERNAME SID SERIAL# STATUS PROGRAM
-----------------------------------------------------------------------

SCOTT 159 9 INACTIVE sqlplus@node2 (TNS V1-V3)
--下接

OSUSER LOGON_TIME LOCK_LEVEL object_owner OBJECT_NAME OBJECT_TYPE
--------------------------------------------------------------------------------

oracle 2011-02-21 21:34:25 ROW LOCK SCOTT TTT TABLE

--查看谁阻塞了谁

SELECT s1.username
         || '@'
         || s1.machine        
         || ' ( SID='
         || s1.sid
         || ',serial#='
         || s1.serial#
         || ' )is blocking '        
         || s2.username
         || '@'
         || s2.machine
         || ' ( SID='
         || s2.sid
         || ' ,serial#='
         || s2.serial#
         || ')'
            AS blocking_status
  FROM v$lock l1,
         v$session s1,
         v$lock l2,
         v$session s2
 WHERE s1.sid = l1.sid
         AND s2.sid = l2.sid
         AND l1.BLOCK = 1
         AND l2.request > 0
         AND l1.id1 = l2.id1
         AND l2.id2 = l2.id2;
        
--After run

BLOCKING_STATUS
---------------------------------------------------------------------------

SCOTT@node2 ( SID=159,serial#=9 )is blocking SYS@node2 ( SID=139 ,serial#=7)
SCOTT@node2 ( SID=159,serial#=9 )is blocking SYS@node2 ( SID=142 ,serial#=36)
SYSTEM@node2 ( SID=133,serial#=10 )is blocking SYS@node2 ( SID=145 ,serial#=32)
--继续查看(补充)

--单实例

select a.username,
       a.sid,
       a.serial#,
       c.spid,
       a.osuser,
       a.program,
       a.machine,
       a.process client_pid,
       a.status,
       b.ctime
  from v$session a, v$lock b, v$process c
 where a.sid = b.sid
   and a.paddr = c.addr
   and b.block = 1
 order by b.ctime;
--After run

USERNAME SID SERIAL# SPID OSUSER PROGRAM MACHINE CLIENT_PID STATUS CTIME
--------------------------------------------------------------------------------------------

SYSTEM 133 10 2799 oracle sqlplus@node2 (TNS V1-V3) node2 2784 INACTIVE 2480
SCOTT 159 9 9994 oracle sqlplus@node2 (TNS V1-V3) node2 9992 INACTIVE 4369

--RAC里，被阻塞的会话block=0

select a.sid,a.serial#,c.spid,a.username,a.status,a.program,a.machine,
b.id1,b.id2,b.ctime
from v$session a,v$lock b,v$process c
where a.sid=b.sid and a.paddr=c.addr and b.block=0
order by b.ctime;



--所有TM阻塞情况

select /*+ rule*/
  lpad('--',decode(b.block,1,0,4))||s.USERNAME user_name,
  b.TYPE,o.owner||'.'||o.object_name object_name,
  s.SID,s.SERIAL#,decode(b.REQUEST,0,'blocked','waiting') status
from dba_objects o,v$session s,v$lock v,v$lock b
where v.ID1 = o.object_id
  and v.SID = b.SID
  and v.SID = s.SID
  and (b.BLOCK = 1 or b.REQUEST > 0)
  and v.TYPE = 'TM'
order by b.ID2,v.ID1,user_name desc;

--After run

USER_NAME TY OBJECT_NAM SID SERIAL# STATUS
---------- -- ---------- ---------- ---------- -------

SCOTT TX SCOTT.TTT 159 9 blocked
  --SYS TX SCOTT.TTT 142 36 waiting

  --SYS TX SCOTT.TTT 139 7 waiting

  

--oracle TX锁的等待序列

SELECT /*+ choose */
         bs.username "Blocking User", bs.username "DB User",
         ws.username "Waiting User", bs.sid "SID", ws.sid "WSID",
         bs.serial# "Serial#", bs.sql_address "address",
         bs.sql_hash_value "Sql hash", bs.program "Blocking App",
         ws.program "Waiting App", bs.machine "Blocking Machine",
         ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
         ws.osuser "Waiting OS User", bs.serial# "Serial#",
         ws.serial# "WSerial#",
         DECODE (
            wk.TYPE,
            'MR', 'Media Recovery',
            'RT', 'Redo Thread',
            'UN', 'USER Name',
            'TX', 'Transaction',
            'TM', 'DML',
            'UL', 'PL/SQL USER LOCK',
            'DX', 'Distributed Xaction',
            'CF', 'Control FILE',
            'IS', 'Instance State',
            'FS', 'FILE SET',
            'IR', 'Instance Recovery',
            'ST', 'Disk SPACE Transaction',
            'TS', 'Temp Segment',
            'IV', 'Library Cache Invalidation',
            'LS', 'LOG START OR Switch',
            'RW', 'ROW Wait',
            'SQ', 'Sequence Number',
            'TE', 'Extend TABLE',
            'TT', 'Temp TABLE',
            wk.TYPE
         ) lock_type,
         DECODE (
            hk.lmode,
            0, 'None',
            1, 'NULL',
            2, 'ROW-S (SS)',
            3, 'ROW-X (SX)',
            4, 'SHARE',
            5, 'S/ROW-X (SSX)',
            6, 'EXCLUSIVE',
            TO_CHAR (hk.lmode)
         ) mode_held,
         DECODE (
            wk.request,
            0, 'None',
            1, 'NULL',
            2, 'ROW-S (SS)',
            3, 'ROW-X (SX)',
            4, 'SHARE',
            5, 'S/ROW-X (SSX)',
            6, 'EXCLUSIVE',
            TO_CHAR (wk.request)
         ) mode_requested,
         TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
         DECODE (
            hk.BLOCK,
            0, 'NOT Blocking', /* Not blocking any other processes */
            1, 'Blocking', /* This lock blocks other processes */
            2, 'Global', /* This lock is global, so we can't tell */
            TO_CHAR (hk.BLOCK)
         )
               blocking_others
    FROM v$lock hk, v$session bs, v$lock wk, v$session ws
   WHERE hk.BLOCK = 1
     AND hk.lmode != 0
     AND hk.lmode != 1
     AND wk.request != 0
     AND wk.TYPE(+) = hk.TYPE
     AND wk.id1(+) = hk.id1
     AND wk.id2(+) = hk.id2
     AND hk.sid = bs.sid(+)
     AND wk.sid = ws.sid(+)
     AND (bs.username IS NOT NULL)
     AND (bs.username <> 'SYSTEM')
     AND (bs.username <> 'SYS')
ORDER BY 1
/

----------------------------------------------------------------------------

--用户锁等待阻塞信息报告

 SELECT sn.username,
           nvl(sn.terminal, 'None') terminal,
           sn.SERIAL#,
           m.TYPE,
           DECODE (m.lmode,
                   0,
                   'None_0',
                   1,
                   'Null_1',
                   2,
                   'Row Share_2',
                   3,
                   'Row Excl_3',
                   4,
                   'Share_4',
                   5,
                   'S/Row Excl_5',
                   6,
                   'Exclusive_6',
                   lmode,
                   LTRIM (TO_CHAR (lmode, '990')))
              lmode,
           DECODE (m.request,
                   0,
                   'None_0',
                   1,
                   'Null_1',
                   2,
                   'Row Share_2',
                   3,
                   'Row Excl_3',
                   4,
                   'Share_4',
                   5,
                   'S/Row Excl_5',
                   6,
                   'Exclusive_6',
                   request,
                   LTRIM (TO_CHAR (m.request, '990')))
              request,
           m.id1,
           m.id2
    FROM v$session sn, v$lock m
   WHERE (sn.SID = m.SID AND m.request != 0) --存在锁请求，即被阻塞

           OR (sn.SID = m.SID --不存在锁请求，但是锁定的对象被其他会话请求锁定

                             AND m.request = 0 AND lmode != 4
               AND (id1, id2) IN
                        (SELECT s.id1, s.id2
                           FROM v$lock s
                          WHERE request != 0
                                  AND s.id1 = m.id1
                                  AND s.id2 = m.id2))
ORDER BY id1, id2, m.request;
 
--After run

Username Term SERIAL# TY Lock Held Lock Requested ID1 ID2
---------- ------ ------- -- ------------- -------------- ---- --------

SYSTEM pts/6 10 TX Exclusive_6 None_0 458760 231
SYS pts/7 32 TX None_0 Exclusive_6 458760 231
SCOTT pts/1 9 TX Exclusive_6 None_0 589844 307
SYS pts/3 36 TX None_0 Exclusive_6 589844 307
           pts/4 7 TX None_0 Exclusive_6 589844 307
        
--持有锁的会话

set linesize 132 pagesize 66
break on Kill on username on terminal
column Kill heading 'Kill String' fromat a13
column res heading 'Resource Type' format 999
column id1 format 9999990
column id2 format 9999990
column lmode heading 'Lock Held' format a20
column request heading 'Lock Requested' format a20
column serial# format 99999
column username format a10 heading "Username"
column terminal heading Term format a6
column tab format a35 heading "Table Name"
column owner format a9
column Address format a18
column Kill for a10
select nvl(s.username, 'Internal') username,
       nvl(s.terminal, 'None') terminal,
       l.sid || ',' || s.serial# Kill,
       u1.name || '.' || substr(t1.name, 1, 20) tab,
       decode(l.lmode,
     0, 'None_0',
     1, 'No Lock_1',
                       2, 'Row Share_2',
                       3, 'Row Exclusive_3',
                       4, 'Share_4',
                       5, 'Share Row Exclusive_5',
                       6, 'Exclusive_6', null) lmode,
       decode(l.request,
     0, 'None_0',
     1, 'No Lock_1',
                         2, 'Row Share_2',
                         3, 'Row Exclusive_3',
                         4, 'Share_4',
                         5, 'Share Row Exclusive_5',
                         6, 'Exclusive_6', null) request,
        l.id1,
        l.id2
                         from v$lock l, v$session s, sys.user$ u1, sys.obj$ t1
where l.sid = s.sid
and t1.obj# = decode(l.id2, 0, l.id1, l.id2)
and u1.user# = t1.owner#
and s.type != 'BACKGROUND'
order by 1, 2,5,7;
--After Run

Username Term KILL Table Name Lock Held Lock Requested ID1 ID2
---------- ------ ---------- -------------- ---------- --------------- -------- --------

SCOTT pts/1 159,9 SYS.SYS_IL0000000305C000 Exclusive_6 None_0 589844 307
                             SCOTT.TTT Row Exclusive_3 None_0 52583 0
SYS pts/3 142,36 SYS.SYS_IL0000000305C000 None_0 Exclusive_6 589844 307
                             SCOTT.TTT Row Exclusive_3 None_0 52583 0
SYS pts/4 139,7 SYS.SYS_IL0000000305C000 None_0 Exclusive_6 589844 307
                             SCOTT.TTT Row Exclusive_3 None_0 52583 0
SYS pts/7 145,32 SYS.I_REG_SNAP1 None_0 Exclusive_6 458760 231
                             SYSTEM.T2 Row Exclusive_3 None_0 52586 0
SYSTEM pts/6 133,10 SYS.I_REG_SNAP1 Exclusive_6 None_0 458760 231
                             SYSTEM.T2 Row Exclusive_3 None_0 52586 0
                


 

--latch阻塞的检索

select sql_text
from v$sqlarea s
where (s.ADDRESS,s.HASH_VALUE ) in
      (
        select sql_address,sql_hash_value
        from v$session
        where sid in
          (
            select sid
            from v$session a,sys.x$kglpn b
            where a.SADDR = b.kglpnuse
              and b.kglpnmod <> 0
              and b.kglpnhdl in
                  (
                    select p1raw
                    from v$session_wait
                    where sid = &sid
                      and event like 'library%'
                  )
          )
      )

--用于鉴别系统中闩性能的脚本

column name heading "Name" format a20
column pid heading "HSid" format a3
column gets heading "Gets" format 999999990
column misses heading "Miss" format 99990
column im_gets heading "ImG" format 99999990
column im_misses heading "ImM" format 999990
column sleeps heading "Sleeps" format 99990
select n.name name, h.pid pid, l.gets gets, l.misses misses,
       l.immediate_gets im_gets, l.immediate_misses im_misses, l.sleeps sleeps
from v$latchname n, v$latchholder h, v$latch l
where l.latch# = n.latch#
and l.addr = h.laddr(+);

--列举用于闩竞争的信息

ttitle center 'Latch Contention Report' skip 3
col name form a25
col gets form 999,999,999
col misses form 999.99
col spins form 999.99
col igets form 999,999,999
col imisses form 999.99
select name, gets,
       misses * 100 / decode(gets, 0, 1, gets) misses,
       spin_gets * 100 / decode(misses, 0, 1, misses) spins,
       immediate_gets igets,
       immediate_misses * 100 / decode(immediate_gets, 0, 1, immediate_gets) imisses
from v$latch
order by gets + immediate_gets;
/


--检索闩睡眠率

col name form a18 trunc
col gets form 999,999,990
col miss form 90.9
col cspins form a6 heading 'spin | sl06'
col csleep1 form a5 heading 'sl01 | sl07'
col csleep2 form a5 heading 'sl02 | sl08'
col csleep3 form a5 heading 'sl03 | sl09'
col csleep4 form a5 heading 'sl04 | sl10'
col csleep5 form a5 heading 'sl05 | sl11'
col Interval form a12
set recsep off
select a.name, a.gets gets,
       a.misses * 100 / decode(a.gets, 0, 1, a.gets) miss,
       to_char(a.spin_gets * 100 / decode(a.misses, 0, 1, a.misses), '990.9') ||
       to_char(a.sleep6 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') cspins,
       to_char(a.sleep1 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep7 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep1,
       to_char(a.sleep2 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep8 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep2,
       to_char(a.sleep3 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep9 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep3,
       to_char(a.sleep4 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep10 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep4,
       to_char(a.sleep5 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') ||
       to_char(a.sleep11 * 100 / decode(a.misses, 0, 1, a.misses), '90.9') csleep5
from v$latch a
where a.misses <> 0
order by 2 desc;


--使用v$session wait视图来鉴别闩竞争

select event, p1text, p1, p2text, p2, seq#, wait_time, state
from v$session_wait
where sid = '&&1'
and event = 'latch free';