20161208理解enq TX - row lock contention

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