[20161208]理解enq TX - row lock contention.txt
>SELECT * FROM v$event_name WHERE name = 'enq: TX - row lock contention';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
------ ---------- ----------------------------- ----------- --------------- ---------- ------------- ----------- --------------------
241 310662678 enq: TX - row lock contention name|mode usn<<16 | slot sequence 4217450380 1 Application
--同事不理解P1,P2,P3的含义,做一个例子说明一下:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
create table deptx as select * from dept;
SCOTT@book> select * from deptx ;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2.测试:
--session 1:
SCOTT@book(232,11)> update deptx set dname='A' where deptno=10;
1 row updated.
--session 2:
SCOTT@book(62,199)> update deptx set loc='B' where deptno=10;
--挂起!
SCOTT@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ --------------- ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
62 199 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 89351 0 SCOTT TABLE DEPTX No 00000000851E9C50
62 199 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction None Exclusive 589843 26935 No 00000000851E9C50
232 11 SCOTT oracle gxqyydg4 SQL*Plus TX Transaction Exclusive None 589843 26935 Yes
232 11 SCOTT oracle gxqyydg4 SQL*Plus TM DML(TM) Row-X (SX) None 89351 0 SCOTT TABLE DEPTX No
SCOTT@book> @ &r/wait
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ------ ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00 1650815232 1 0 73 57 36 SQL*Net message to client WAITED SHORT TIME 2 0
0000000054580006 0000000000090013 0000000000006937 1415053318 589843 26935 62 199 40 enq: TX - row lock contention WAITING 94270097 94
3.如何理解参数P1,P2,P3:
--回到session 1:
SCOTT@book(232,11)> @ &r/xid
XIDUSN_XIDSLOT_XIDSQN
------------------------------
9.19.26935
C70 XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS USED_UBLK USED_UREC XID ADDR START_DATE
---------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- -------------------
ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU9_1650507775$' XID 9 19 26935; 9 19 26935 3 14571 1375 15 ACTIVE 1 1 0900130037690000 00000000818B4670 2016-12-08 15:06:04
ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU9_1650507775$';
ALTER SYSTEM DUMP DATAFILE 3 BLOCK 14571;
--P3=26935,对应的事务的XIDSQN.
--P2=589843.
select 589843,trunc(589843/65536) XIDUSN,mod(589843,65536) XIDSLOT from dual
589843 XIDUSN XIDSLOT
---------- ---------- ----------
589843 9 19
--从这里可以看出前16位就是XIDUSN,后16位就是XIDSLOT.
4.剩下P1=1415053318.
--name|mode??
select 1415053318,trunc(1415053318/65536) XIDUSN,mod(1415053318,65536) XIDSLOT from dual
1415053318 XIDUSN XIDSLOT
---------- ---------- ----------
1415053318 21592 6
--表示mode=6
SCOTT@book> @ &r/10to16 21592
10 to 16 HEX REVERSE16
-------------- ------------------
0000000005458 0x58540000
SCOTT@book> @ &r/16to10 54
16 to 10 DEC
------------
84
SCOTT@book> @ &r/16to10 58
16 to 10 DEC
------------
88
SCOTT@book> select chr(84)||chr(88) c10 from dual ;
C10
----------
TX
--表示name='TX'.
从这个视图也可以看出来:
SCOTT@book> select * from v$lock where type='TX';
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ------ ----- ------- ---------- ---------- ---------- ---------- --------------
00000000851E9BF8 00000000851E9C50 62 TX 589843 26935 0 6 634 0
00000000818B4670 00000000818B46E8 232 TX 589843 26935 6 0 675 1
--这里ID1,ID2对应就是前面的P2,P3.