oracle中的阻塞

oracle的锁是放在块上的。

-- 当前锁定的对象：

SELECT *
FROM v$locked_object;

-- 被死锁的语句

select sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in
(select session_id from v$locked_object))

-- 锁类型

SELECT *
FROM v$lock_type lt
WHERE lt.TYPE = 'TX';

--锁的信息
SELECT vl.*,TRUNC(id1/POWER(2,16)) usn,
BITAND(id1,to_number('FFFF','xxxx'))+0 slot ,
vl.ID2 sequence
FROM v$lock vl
WHERE vl.TYPE = 'TX';

-- 查出当前锁定资源的事务是那一个以及session 信息。

SELECT vs.*
  FROM V$TRANSACTION T, V$SESSION VS
 WHERE T.ADDR = VS.TADDR
   AND EXISTS (SELECT 1
          FROM V$LOCK VL
         WHERE VL.TYPE = 'TX'
           AND TRUNC(ID1 / POWER(2, 16)) = T.XIDUSN
           AND BITAND(ID1, TO_NUMBER('FFFF', 'xxxx')) + 0 = T.XIDSLOT
           AND VL.ID2 = T.XIDSQN);

-- kill session.

 ALTER SYSTEM KILL SESSION '78,14848' ;

-- 谁阻塞了谁
SELECT 'sid:', blk.SID ,',is block sid :',req.SID
 FROM v$lock blk,v$lock req
 WHERE blk.TYPE = 'TX'
 AND blk.BLOCK = 1
 AND blk.type = req.TYPE
 AND req.REQUEST > 0
 AND blk.ID1 = req.ID1
 AND blk.ID2 = req.ID2 ;
 
 -- 日前等待最多的是那个锁。
 SELECT blk.SID ,COUNT(req.SID)
 FROM v$lock blk,v$lock req
 WHERE blk.TYPE = 'TX'
 AND blk.BLOCK = 1
 AND blk.type = req.TYPE
 AND req.REQUEST > 0
 AND blk.ID1 = req.ID1
 AND blk.ID2 = req.ID2
 GROUP BY blk.SID
 ORDER BY 2;