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 解析鎖消失

oracle當前執行事務鎖

相關文章