与锁有关的视图

环境
在会话1中更新
  1. SQL> update test set name='user' where id=3;
在会话2中更新
  1. SQL> update test set name='user' where id=3;
此时会产生TM和TX锁，视图v$lock
  1. SQL> select * from v$Lock;
  3. ADDR		 KADDR			 SID TY        ID1	  ID2	   LMODE    REQUEST	 CTIME	    BLOCK
  4. ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- --------
  5. 00007F120A181B80 00007F120A181BE0	   1 TM      88634	    0	       3	  0	    46		0
  6. 0000000076B80330 0000000076B803A8	   1 TX     524292	 9002	       6	  0	    46		1
  7. 000000007758F4E8 000000007758F540	   1 AE        100	    0	       4	  0	    84		0
  8. 000000007758E970 000000007758E9C8	   7 KD 	 0	    0	       6	  0	   427		0
  9. 000000007758F5C8 000000007758F620	   7 KT      12873	    0	       4	  0	   397		0
  10. 000000007758EC10 000000007758EC68	  10 MR 	 3	    0	       4	  0	   433		0
  11. 000000007758ECF0 000000007758ED48	  10 MR 	 4	    0	       4	  0	   433		0
  12. 000000007758EDE8 000000007758EE40	  10 MR 	 5	    0	       4	  0	   433		0
  13. 000000007758EEC8 000000007758EF20	  10 MR 	 6	    0	       4	  0	   433		0
  14. 000000007758E7B0 000000007758E808	  10 MR 	 7	    0	       4	  0	   433		0
  15. 000000007758E190 000000007758E1E8	  10 MR 	 8	    0	       4	  0	   433		0
  16. 000000007758EFA8 000000007758F000	  10 MR 	 9	    0	       4	  0	   433		0
  17. 000000007758EA50 000000007758EAA8	  10 MR 	 1	    0	       4	  0	   433		0
  18. 000000007758EB30 000000007758EB88	  10 MR 	 2	    0	       4	  0	   433		0
  19. 000000007758F088 000000007758F0E0	  10 MR        201	    0	       4	  0	   433		0
  20. 000000007758E6D0 000000007758E728	  10 PW 	 1	    0	       3	  0	   429		0
  21. 000000007758E430 000000007758E488	  11 RT 	 1	    0	       6	  0	   434		0
  22. 000000007758E0B0 000000007758E108	  12 RD 	 1	    0	       1	  0	   441		0
  23. 000000007758E510 000000007758E568	  12 RS 	25	    1	       2	  0	   434		0
  24. 000000007758DFD0 000000007758E028	  12 XR 	 4	    0	       1	  0	   441		0
  25. 000000007758E350 000000007758E3A8	  12 CF 	 0	    0	       2	  0	   439		0
  26. 000000007758F168 000000007758F1C0	  13 TS 	 3	    1	       3	  0	   428		0
  27. 000000007758F408 000000007758F460	  15 AE        100	    0	       4	  0	   420		0
  28. 000000007758FF80 000000007758FFD8	  32 AE        100	    0	       4	  0	     9		0
  29. 00007F120A181B80 00007F120A181BE0	  38 TM      88634	    0	       3	  0	    15		0
  30. 000000007758F6A8 000000007758F700	  38 TX     524292	 9002	       0	  6	    15		0
  31. 000000007758FDC0 000000007758FE18	  38 AE        100	    0	       4	  0	    21		0
在v$transaction视图也可以查看
  1. SQL> select XIDUSN ,XIDSLOT,XIDSQN from v$transaction;
  3.     XIDUSN    XIDSLOT	  XIDSQN
  4. ---------- ---------- ----------
  5. 	    8	    4	      9002
v$lock中的524292的值是十进制值，转换为十六进制就是XIDUSN XIDSLOT对应的值，LMODE即为XIDSQN




查看v$session

  1. SQL> select sid,event,p1,p2,p3 from v$session where WAIT_CLASS <> 'Idle';
  2.        SID EVENT								    P1	       P2	  P3
  3. ---------- ---------------------------------------------------------------- - 
  4. 	32 SQL*Net message to client					    1650815232		1	   0
  5. 	38 enq: TX - row lock contention				    1415053318	   524292	9002
这里的P2，P3和v$lock的ID1，ID2是一样的
  1. SQL> select * from v$lock where id1=524292 and id2=9002;
  3. ADDR		 KADDR			 SID TY        ID1	  ID2	   LMODE    REQUEST	 CTIME	    BLOCK
  4. ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- -------
  5. 000000007758F6A8 000000007758F700	  38 TX     524292	 9002	       0	  6	  1105		0
  6. 0000000076B80330 0000000076B803A8	   1 TX     524292	 9002	       6	  0	  1136		1
上图及SID为1的会话阻塞了SID为38的会话

会话1中回滚
  1. SQL> rollback;
  3. Rollback complete.
查看会话1的用户SID
  1. SQL> select userenv('sid') from dual;
  3. USERENV('SID')
  4. --------------
  5. 	     1
查看会话2的SID
  1. SQL> select userenv('sid') from dual;
  3. USERENV('SID')
  4. --------------
  5. 	    38





来自为知笔记(Wiz)


posted on 2016-06-22 14:46  侯志清  阅读(247)  评论(0)  编辑  收藏  举报

导航