oracle当前运行事务锁

select a1.sid as "被堵塞用户SID",a1.blocking_session as "堵塞用户SID",a1.SERIAL#, a1.username, a1.logon_time, a2.type
  from v$session a1, v$lock a2
 where a1.sid = a2.sid
   and a2.type in ('ST', 'UL', 'TX', 'TM')
   and a1.blocking_session in
       (select c.blocking_session
          from v$session c
         where c.blocking_session > 0)
 order by a1.logon_time;

select a1.sid as "被堵塞用户SID",a1.blocking_session as "堵塞用户SID",a1.SERIAL#, a1.username, a1.logon_time, a1.event
  from v$session a1 where a1.sid in (select a2.sid from v$lock a2 where a2.type in ('ST', 'UL', 'TX', 'TM')) and a1.blocking_session in
       (select c.blocking_session
          from v$session c
         where c.blocking_session > 0)
 order by a1.logon_time;


 select a.SID,
       a.SERIAL#,
       a.USERNAME,
       a.status,
       a.STATE,
       e.SPID,
       e.PNAME,
       a.MACHINE,
       b.OS_USER_NAME,
       c.OBJECT_NAME,
       c.OBJECT_TYPE,
       decode(B.LOCKED_MODE,1,'none',2,'行共享(RS)',3,'行独占(RX)',4,'共享锁(S)',5,'共享行独占(SRX)',6,'独占(X)') AS LOCKED_MODE,
       d.SQL_TEXT
  from v$session a inner join
  V$LOCKED_OBJECT b on a.PROCESS = b.PROCESS inner join  
  DBA_OBJECTS c on b.OBJECT_ID = c.OBJECT_ID  left join
  v$sql d on a.SQL_ID=d.SQL_ID left join
  v$process e on a.PADDR=e.ADDR

---------------------------------------------------
-- Script. Function: Query the lock info         --
-- Script. Name:     lock.sql                    --
-- Author:           secooler                    --
-- Date:             2008.3.6                    --
---------------------------------------------------

set pages 1000 lin 126
col kaddr heading 'lock|address'
col username heading 'lock|holder|username' for a18
col sid heading 'lock|holder|session id' format 9999999999
col type heading 'lock|type' format a6
col id1 heading 'id1' format 9999999999
col id2 heading 'id2' format 9999999999
col lmode heading 'lock|mode' format 99999999
col request heading 'request|mode' format 99999999
col blocking_sid format 999999 heading 'blocked|session id'
select /*+rule*/
--     a.kaddr, --
     (select username from v$session where sid = a.sid) username,
     a.sid,
     (select serial# from v$session where sid = a.sid) serial#,
--     (select ctime from v$lock where KADDR = a.kaddr) ctime, --
     a.type,
     a.id1,
     a.id2,
     a.lmode,
     a.request,
     a.block,
     b.sid blocking_sid
from v$lock a,
     ( select * from v$lock
       where request > 0
       and type <> 'MR'
     ) b
where a.id1 = b.id1(+)
  and a.id2 = b.id2(+)
  and a.lmode > 0
  and a.type <> 'MR'
order by username,a.sid,serial#,a.type
/

column sid clear
column type clear
column request clear
column username clear

锁大致分为:

 DML锁 数据锁

 DDL锁 数据字典锁

 内部锁 或 LATCH锁

其中DML锁分为:

 表级别锁 TM   Table dMl

 行级别锁 TX   Transaction eXclusive

 行级别锁:

  exclusive

 表级别锁:

  rs  rx  s  srx  x

  rs:   同时会持有 tx 的 x 锁 select ... for update(9i)

       lock table table_name in row share mode( 只有表级别锁不产生行级别 x 锁 )

       与 x 级锁 冲突

 rx:   同时会持有 tx 的 x 锁 insert update delete select ... for update(10g)

       lock table table_name in row exclusive mode( 只有表级别锁 不产生行级别 x 锁 )

       与 s srx x rx(行级x锁) 级锁 冲突

 s:    显示设置的共享锁

       lock table table_name in share mode

       与 rx srx x 锁 冲突

 srx:  显示设置的串行锁

       lock table table_name in share row exclusive mode

       与 rx s srx x 锁 冲突

 x:    显示设置的排他锁 和 行级别的排他锁

       行级别由 DML 生成

       表级别: lock table table_name in exclusive mode ( 不产生行级别 x 锁 )

       与 rs rx s srx x 锁 冲突

DDL 锁又分:

 DDL 专有锁:

  DDL 操作产生的 DDL 排他锁   防止 DDL 操作时 其他事物对其进行操作

  DDL 锁 在 DDL 操作内持有 操作结束 DDL 锁释放

  DDL 操作结束后 会自动 COMMIT

  DDL 转有锁与另一个 DDL 专有锁 DDL共享锁 和 DML 锁 均冲突

 DDL 共享锁:

  同一张表上 同时创建存储过程 视图 等操作 会产生 DDL 共享锁

  DDL 共享锁 与 DDL 排他锁 冲突

 DDL 可中断解析锁:

  null: 基于会话 解析锁 cursor 执行时(存储过程) 其依赖的表上产生了DDL

        那么 cursor 和与之相关的会话和存储过程 都会被级联通知

        它本身不会阻止 DDL 操作

        只要存储过程一直在内存 不被重新编译 DDL null锁将一直持有

        例:

        create or replace procedure p as begin null; end;   

        /                                                   

        exec p;

        创建 执行存储过程 

        select session_id sid, owner, name, type,

          mode_held held, mode_requested request

         from dba_ddl_locks                       

         where name = 'P';

        查询 DDL 锁

        alter procedure p compile;    

        重新编译存储过程后 DDL 解析锁消失

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29065182/viewspace-1146790/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29065182/viewspace-1146790/