Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1448350
  • 博文数量: 239
  • 博客积分: 5909
  • 博客等级: 大校
  • 技术积分: 2715
  • 用 户 组: 普通用户
  • 注册时间: 2010-07-24 20:19
文章分类

全部博文(239)

文章存档

2014年(4)

2013年(22)

2012年(140)

2011年(14)

2010年(59)

我的朋友

分类: Oracle

2012-10-29 17:35:22


点击(此处)折叠或打开

  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:

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

  4. tkyte@TKYTE816> select username,
  5.   2 v$lock.sid,
  6.   3 trunc(id1/power(2,16)) rbs,
  7.   4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
  8.   5 id2 seq,
  9.   6 lmode,
  10.   7 request
  11.   8 from v$lock, v$session
  12.   9 where v$lock.type = 'TX'
  13.  10 and v$lock.sid = v$session.sid
  14.  11 and v$session.username = USER
  15.  12 /

  16. USERNAME SID RBS SLOT SEQ LMODE REQUEST
  17. -------- ---------- ---------- ---------- ---------- ---------- ----------
  18. TKYTE     8   2  46   160    6    0

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

  22.     XIDUSN XIDSLOT XIDSQN
  23. ---------- ---------- ----------
  24.          2    46    160

  25. The interesting things to note here are:

  26. 1.The LMODE is 6 in the V$LOCK table and the request is 0. If you refer to the
  27. 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.
  28. 2. 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.
  29. 3. I took the ID1 and ID2 columns, and performed a bit of bit manipulation on them.
  30. 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
  31. trunc(id1/power(2,16)) rbs and by masking out the high bits with
  32. 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.
  33. 4. The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my
  34. transaction ID.

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

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

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


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

  40. tkyte@TKYTE816> select username,
  41.   2 v$lock.sid,
  42.   3 trunc(id1/power(2,16)) rbs,
  43.   4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
  44.   5 id2 seq,
  45.   6 lmode,
  46.   7 request
  47.   8 from v$lock, v$session
  48.   9 where v$lock.type = 'TX'
  49.  10 and v$lock.sid = v$session.sid
  50.  11 and v$session.username = USER
  51.  12 /

  52. USERNAME SID RBS SLOT SEQ LMODE REQUEST
  53. -------- ---------- ---------- ---------- ---------- ---------- ----------
  54. TKYTE     8    2   46  160  6    0
  55. TKYTE     9    2   46  160  0    6
  56. TKYTE     9    3   82  163  6    0

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

  60.     XIDUSN XIDSLOT XIDSQN
  61. ---------- ---------- ----------
  62.          3   82    163
  63.          2   46    160

  64. 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:

  65. tkyte@TKYTE816> select username,
  66.   2 v$lock.sid,
  67.   3 trunc(id1/power(2,16)) rbs,
  68.   4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
  69.   5 id2 seq,
  70.   6 lmode,
  71.   7 request, block
  72.   8 from v$lock, v$session
  73.   9 where v$lock.type = 'TX'
  74.  10 and v$lock.sid = v$session.sid
  75.  11 and v$session.username = USER
  76.  12 /

  77. USERNAME SID RBS SLOT SEQ LMODE REQUEST
  78. -------- ---------- ---------- ---------- ---------- ---------- ----------
  79. TKYTE     9    3  82  163  6     0

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

  83.     XIDUSN XIDSLOT XIDSQN
  84. ---------- ---------- ----------
  85.          3 82 163

  86. 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:

  87. tkyte@TKYTE816> select
  88.            (select username from v$session where sid=a.sid) blocker,
  89.   2 a.sid,
  90.   3 ' is blocking ',
  91.   4 (select username from v$session where sid=b.sid) blockee,
  92.   5 b.sid
  93.   6 from v$lock a, v$lock b
  94.   7 where a.block = 1
  95.   8 and b.request > 0
  96.   9 and a.id1 = b.id1
  97.  10 and a.id2 = b.id2
  98.  11 /

  99. BLOCKER SID 'ISBLOCKING' BLOCKEE SID
  100. -------- ---------- ------------- -------- ----------
  101. TKYTE    8   is blocking TKYTE    9

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

  103. 2)
  104. 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.

  105. 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.

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

另外查看死锁的方式还有?/RDBMS/ADMIN/utllockt.sql
查看哪个session block哪个session block的sql

点击(此处)折叠或打开

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

转自:
阅读(3229) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~