ITPub博客

首页 > 应用开发 > IT综合 > about lock

about lock

原创 IT综合 作者:littledan 时间:2007-01-21 11:46:58 0 删除 编辑
1) Here is a small example showing how this happens. We will use three V$ tables in order 
to see how this works; V$TRANSACTION, V$SESSION, and V$LOCK. V$TRANSACTION contains an 
entry for every active transaction. V$LOCK contains an entry for all locks held as well 
as locks being waited on. V$SESSION shows us the sessions logged in. We'll start by 
starting a transaction in one session and looking at the state of the system at that 
point:

tkyte@TKYTE816> update dept set deptno = deptno+10;
4 rows updated.

tkyte@TKYTE816> 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
 12  /

USERNAME        SID        RBS       SLOT        SEQ      LMODE    REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE             8          2         46        160          6          0

tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
  2    from v$transaction
  3  /

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         2         46        160

The interesting things to note here are:

ć The LMODE is 6 in the V$LOCK table and the request is 0. If you refer to the 
definition of the V$LOCK table in the Oracle Server Reference, you will find that LMODE=6 
is an exclusive lock. A value of 0 in the request means we are not making a request ¡V we 
have the lock.
ć There is only one row in this table. This V$LOCK table is more of a queuing table than 
a lock table. Many people expect there would be four rows in V$LOCK since we have four 
rows locked. What you must remember however is that Oracle does not store a master list 
of every row locked anywhere. To find out if a row is locked, we must go to that row.
ć I took the ID1 and ID2 columns, and performed a bit of bit manipulation on them. 
Oracle needed to save three 16bit numbers, but only had two columns in order to do it. 
So, the first column ID1 holds two of these numbers. By dividing by 2^16 with 
trunc(id1/power(2,16)) rbs and by masking out the high bits with 
bitand(id1,to_number('ffff','xxxx'))+0 slot, I am able to get the two numbers that are 
hiding in that one number back out.
ć The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my 
transaction ID.

Now I'll start another session using the same user name, update some rows in EMP, and 
then try to update DEPT:

tkyte@TKYTE816> update emp set ename = upper(ename);
14 rows updated.

tkyte@TKYTE816> update dept set deptno = deptno-10;


I am now blocked in this session. If we run the V$ queries again, we see:

tkyte@TKYTE816> 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
 12  /

USERNAME        SID        RBS       SLOT        SEQ      LMODE    REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE             8          2         46        160          6          0
TKYTE             9          2         46        160          0          6
TKYTE             9          3         82        163          6          0

tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
  2    from v$transaction
  3  /

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         3         82        163
         2         46        160

What we see here is that a new transaction has begun ¡V (3,82,163) is the transaction ID. 
It has two rows in V$LOCK this time. One row represents the locks that it owns (where 
LMODE=6). It also has a row in there that shows a REQUEST with a value of 6. This is a 
request for an exclusive lock. The interesting thing to note here is that the 
RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock. 
We can easily see that the transaction with SID=8 is blocking the transaction with SID=9. 
Now, if we commit in SID = 8 the above changes:

tkyte@TKYTE816> 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, block
  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
 12  /

USERNAME        SID        RBS       SLOT        SEQ      LMODE    REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ---------- 
TKYTE             9          3         82        163          6          0

tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
  2    from v$transaction
  3  /

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
         3         82        163

that request row has gone ¡V it disappeared the instant the other session gave up its 
lock. That request row was the queuing mechanism. The database is able to wake up the 
blocked sessions the instant the transaction is completed. Note that the above gives us a 
very easy way to see blockers and waiters:

tkyte@TKYTE816> 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
-------- ---------- ------------- -------- ----------
TKYTE             8  is blocking  TKYTE             9

simply by doing a self-join of V$LOCK with itself (I ran this query before committing the 
session with SID=8). 

2) exclusive lock -- I updated a row.  no one else can update it until I commit.  I have 
an X lock on that row and only one person at a time can have an X lock.  an X lock 
provides serialization to a resource.

A shared lock -- when I update a table, I take a shared lock on the DEFINITION of the 
table.  Everyone else can do that as well (more then one session can get a shared lock on 
the table definition).  So, more than one person at a time can update the table.  If you 
wanted to ALTER the table, you would need an X lock on the defintion.  You cannot get an 
X lock when there are shared locks so you wait until there are no shared locks.

3) mystat has the statistics (cpu use, blocks read, cursors opened, etc) for your session 
only.  v$locked_object shows you all of the locks in the system.

4) no, never. 
the locks are all table level entries actually.  You will not see individual row level locks in 
v$lock.

Yes, Oracle has no shared row lock.  Not necessary.
[@more@]

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/412/viewspace-892045/,如需转载,请注明出处,否则将追究法律责任。

上一篇: tom 就是tom
下一篇: showsql.sql
请登录后发表评论 登录
全部评论

注册时间:2008-01-13

  • 博文量
    31
  • 访问量
    133646