与锁有关的视图

环境

在会话1中更新

SQL> update test set name='user' where id=3;

在会话2中更新

SQL> update test set name='user' where id=3;

此时会产生TM和TX锁，视图v$lock

SQL> select * from v$Lock;
ADDR		 KADDR			 SID TY        ID1	  ID2	   LMODE    REQUEST	 CTIME	    BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- --------
00007F120A181B80 00007F120A181BE0	   1 TM      88634	    0	       3	  0	    46		0
0000000076B80330 0000000076B803A8	   1 TX     524292	 9002	       6	  0	    46		1
000000007758F4E8 000000007758F540	   1 AE        100	    0	       4	  0	    84		0
000000007758E970 000000007758E9C8	   7 KD 	 0	    0	       6	  0	   427		0
000000007758F5C8 000000007758F620	   7 KT      12873	    0	       4	  0	   397		0
000000007758EC10 000000007758EC68	  10 MR 	 3	    0	       4	  0	   433		0
000000007758ECF0 000000007758ED48	  10 MR 	 4	    0	       4	  0	   433		0
000000007758EDE8 000000007758EE40	  10 MR 	 5	    0	       4	  0	   433		0
000000007758EEC8 000000007758EF20	  10 MR 	 6	    0	       4	  0	   433		0
000000007758E7B0 000000007758E808	  10 MR 	 7	    0	       4	  0	   433		0
000000007758E190 000000007758E1E8	  10 MR 	 8	    0	       4	  0	   433		0
000000007758EFA8 000000007758F000	  10 MR 	 9	    0	       4	  0	   433		0
000000007758EA50 000000007758EAA8	  10 MR 	 1	    0	       4	  0	   433		0
000000007758EB30 000000007758EB88	  10 MR 	 2	    0	       4	  0	   433		0
000000007758F088 000000007758F0E0	  10 MR        201	    0	       4	  0	   433		0
000000007758E6D0 000000007758E728	  10 PW 	 1	    0	       3	  0	   429		0
000000007758E430 000000007758E488	  11 RT 	 1	    0	       6	  0	   434		0
000000007758E0B0 000000007758E108	  12 RD 	 1	    0	       1	  0	   441		0
000000007758E510 000000007758E568	  12 RS 	25	    1	       2	  0	   434		0
000000007758DFD0 000000007758E028	  12 XR 	 4	    0	       1	  0	   441		0
000000007758E350 000000007758E3A8	  12 CF 	 0	    0	       2	  0	   439		0
000000007758F168 000000007758F1C0	  13 TS 	 3	    1	       3	  0	   428		0
000000007758F408 000000007758F460	  15 AE        100	    0	       4	  0	   420		0
000000007758FF80 000000007758FFD8	  32 AE        100	    0	       4	  0	     9		0
00007F120A181B80 00007F120A181BE0	  38 TM      88634	    0	       3	  0	    15		0
000000007758F6A8 000000007758F700	  38 TX     524292	 9002	       0	  6	    15		0
000000007758FDC0 000000007758FE18	  38 AE        100	    0	       4	  0	    21		0

在v$transaction视图也可以查看

SQL> select XIDUSN ,XIDSLOT,XIDSQN from v$transaction;
    XIDUSN    XIDSLOT	  XIDSQN
---------- ---------- ----------
	    8	    4	      9002

v$lock中的524292的值是十进制值，转换为十六进制就是XIDUSN XIDSLOT对应的值，LMODE即为XIDSQN

查看v$session

SQL> select sid,event,p1,p2,p3 from v$session where WAIT_CLASS <> 'Idle';
       SID EVENT								    P1	       P2	  P3
---------- ---------------------------------------------------------------- - 
	32 SQL*Net message to client					    1650815232		1	   0
	38 enq: TX - row lock contention				    1415053318	   524292	9002

这里的P2，P3和v$lock的ID1，ID2是一样的

SQL> select * from v$lock where id1=524292 and id2=9002;
ADDR		 KADDR			 SID TY        ID1	  ID2	   LMODE    REQUEST	 CTIME	    BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- -------
000000007758F6A8 000000007758F700	  38 TX     524292	 9002	       0	  6	  1105		0
0000000076B80330 0000000076B803A8	   1 TX     524292	 9002	       6	  0	  1136		1

上图及SID为1的会话阻塞了SID为38的会话

会话1中回滚

SQL> rollback;
Rollback complete.

查看会话1的用户SID

SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
	     1

查看会话2的SID

SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
	    38

来自为知笔记(Wiz)