TX (transaction) lock

最新推荐文章于 2012-05-28 17:22:53 发布

Lijun_blue

最新推荐文章于 2012-05-28 17:22:53 发布

阅读量578

点赞数

分类专栏： CONCEPT 文章标签： session sql

本文链接：https://blog.csdn.net/Lijun_blue/article/details/7197736

版权

CONCEPT 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

A TX lock is acquired when a transaction initiates its first change,and it is held until the transaction performs acommit or rollback.

Oracle process lock like this:

1. find the address of the row you want to lock

2 goto the row

3 lock the row (waiting for the transaction that has it locked to end if it is already locked, unless you are using the NOWAIT option).

SQL> update dept set deptno = deptno + 10;

4 rows updated.

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 =USER;

USERNAME                              SID        RBS       SLOT        SEQ
------------------------------ ---------- ---------- ---------- ----------
     LMODE    REQUEST
---------- ----------
OPS                                   159          6         15       2564
         6          0


SQL> select XIDUSN,XIDSLOT,XIDSQN
  2  from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         6         15       2564


SQL> update dept set deptno = deptno + 10;

4 rows updated.


新开一个session执行

SQL> update emp set ename = upper(ename);

14 rows updated.

SQL> update dept set deptno = deptno - 10;

会话block在这里了。


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 =USER;


USERNAME          SID        RBS       SLOT        SEQ      LMODE    REQUEST
---------- ---------- ---------- ---------- ---------- ---------- ----------
OPS               159          8          3       2568          0          6
OPS               159         10          0       2572          6          0
OPS               153          8          3       2568          6          0


用v$lock表的self-join

SQL> select
  2     (select username from v$session where sid = a.sid) blocker,
  3     a.sid,
  4     'is blocking ',
  5     (select username from v$session where sid = b.sid) blockee,
  6     b.sid
  7  from v$lock a,v$lock b
  8  where a.block = 1
  9  and b.request > 0
 10  and a.id1 = b.id1
 11  and a.id2 = b.id2;

BLOCKER                               SID 'ISBLOCKING'
------------------------------ ---------- ------------
BLOCKEE                               SID
------------------------------ ----------
OPS                                   153 is blocking
OPS                                   159


SQL> col blocker for a10
SQL> col blockee for a10
SQL> /

BLOCKER           SID 'ISBLOCKING' BLOCKEE           SID
---------- ---------- ------------ ---------- ----------
OPS               153 is blocking  OPS               159