[锁机制相关] TX事务锁

-- 事务锁查询
-- 每个事务只能有一个TX锁
select
    username,
    v$lock.sid,
    trunc(id1/power(2,16)) rbs,
    bitand(id1,to_number('ffff','xxxx'))+0 slot,
    id2 seq,
    lmode,
    request,
    block,
    v$lock.type
from
    v$lock,
    v$session
where
    v$lock.type = 'TX'
and v$lock.sid = v$session.sid
and v$session.username = USER
/

-- 事务查询
select XIDUSN, XIDSLOT, XIDSQN from v$transaction;

-- 锁定会话与被锁定会话
column blocker format a20
column blockee format a20
column isblocking format a10
select
(
    select
        username
    from
        v$session
    where
        sid=a.sid
) blocker,
a.sid,
' is blocking ',
(
    select
        username
    from
        v$session
    where sid=b.sid
) blockee,
b.sid
from
    v$lock a,
    v$lock b
where
    a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2
/