關於 TX 鎖的兩句sql

TX(Transaction) Lock是開發中涉及最多的鎖型別。下面兩句sql對於獲取TX鎖的相關資訊相當有用。

[@more@]

 

SQL> update dept set dname='RTS' where deptno=40;

 1 row updated

 SQL> update dept set dname='ccc' where deptno=40;

 SQL> select username,

  2         v$lock.sid,

  3         trunc(id1 / power(2, 16)) rbs,

  4         bitand(id1, to_number('ffff', 'xxxx')) + 0 slot,

  5         id2 seq,

  6         lmode,

  7         request

  8    from v$lock, v$session

  9   where v$lock.type = 'TX'

 10     and v$lock.sid = v$session.sid

 11     and v$session.username = 'SCOTT'

 12  /

 

USERNAME        SID     RBS    SLOT    SEQ      LMODE    REQUEST

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

SCOTT                 10          5       9           5958          6          0

SCOTT                 13          5       9           5958          0          6

  

SQL>   select (select username from v$session where sid = a.sid) blocker,

  2           a.sid,

  3           ' is blocking ',

  4           (select username from v$session where sid = b.sid) blockee,

  5           b.sid

  6            from v$lock a, v$lock b

  7           where a.block = 1

  8             and b.request > 0

  9             and a.id1 = b.id1

 10             and a.id2 = b.id2

 11  /

 

BLOCKER           SID 'ISBLOCKING'  BLOCKEE         SID

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

SCOTT                   10  is blocking  SCOTT                        13