??????Oracle??????????row lock,??ORACLE????????????????????,row lock???????????????????????????????,??Server Process?pin????block buffer?????????
  ????????,?process A ??update???????? Z?????????, ???????rollback???commit;??Process B??????DML??, ???????rowid???? Z???, ???????????process A????????ITL???,????????cleanout??,????????row???????????commit, ???????Process B????”enq: TX – row lock contention”??????? ????Process B?????????????
   
  ?????????Process A???????row,??Process B???????”enq: TX – row lock contention”????
   
  ????????  ????????:
   
   
  SESSION A:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.oracledatabase12g.com
SQL> create table maclean_lock(t1 int);
Table created.
SQL> insert into maclean_lock values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_relative_fno(rowid) from maclean_lock;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
------------------------------------ ------------------------------------
                               67642                                    1
SQL>  select distinct sid from v$mystat;
       SID
----------
       142
SQL> select pid,spid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));
       PID SPID
---------- ------------
        17 15636
??SESSION A ????savepoint ,?update ?????????        
SQL>  savepoint NONLOCK;
Savepoint created.
SQL> select * From v$Lock where sid=142;
no rows selected
SQL> set linesize 140 pagesize 1400
SQL>  update maclean_lock set t1=t1+2;
1 row updated.
SQL> select * From v$Lock where sid=142;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000091FC69F0 0000000091FC6A18        142 TM      55829          0          3          0          6          0
00000000914B4008 00000000914B4040        142 TX     393232        609          6          0          6          0        
SQL> select dump(3,16) from dual;
DUMP(3,16)
--------------------------------------------------------------------------------
Typ=2 Len=2: c1,4
ALTER SYSTEM DUMP DATAFILE 1 BLOCK 67642;
 Object id on Block? Y
 seg/obj: 0xda16  csc: 0x00.234718  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.00f.000001e0  0x00800075.02a6.29  C---    0  scn 0x0000.00234711
0x02   0x0007.018.000001fe  0x0080065c.017a.02  ----    1  fsc 0x0000.00000000
data_block_dump,data header at 0x81d185c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x081d185c
bdba: 0x0041083a
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [ 2]  c1 04
end_of_block_dump
?? BLOCK DUMP ???? ??????XID=0x0007.018.000001fe ?transaction?? lb:0x1
??SESSION B ,?????UPDATE?? ???enq: TX - row lock contention ??
SQL> select distinct sid from v$mystat;
       SID
----------
       140
SQL> select pid,spid from v$process where addr = ( select paddr from v$session where sid=(select distinct sid from v$mystat));
       PID SPID
---------- ------------
        24 15652
SQL> alter system set "_trace_events"='10000-10999:255:24';
System altered.        
SQL> update maclean_lock set t1=t1+2;
select * From v$Lock where sid=142 or sid=140 order by sid;
SESSION C:
SQL> select * From v$Lock where sid=142 or sid=140 order by sid;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
0000000091FC6B10 0000000091FC6B38        140 TM      55829          0          3          0         84          0
00000000924F4A58 00000000924F4A78        140 TX     458776        510          0          6         84          0
00000000914B51E8 00000000914B5220        142 TX     458776        510          6          0        312          1
0000000091FC69F0 0000000091FC6A18        142 TM      55829          0          3          0        312          0
???? SESSION B SID=140 ?SESSION A ?TX ENQUEUE ?X mode?REQUEST
SQL> oradebug dump systemstate 266;
Statement processed.
SESSION B waiter's enqueue lock
      SO: 0x924f4a58, type: 5, owner: 0x92bb8dc8, flag: INIT/-/-/0x00
      (enqueue) TX-00070018-000001FE    DID: 0001-0018-00000022
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
      req: X, lock_flag: 0x0, lock: 0x924f4a78, res: 0x925617c0
      own: 0x92b76be0, sess: 0x92b76be0, proc: 0x92a737a0, prv: 0x925617e0
TX-00070018-000001FE=> TX 458776 510
SESSION A owner's enqueue lock 
      SO: 0x914b51e8, type: 40, owner: 0x92b796d0, flag: INIT/-/-/0x00
      (trans) flg = 0x1e03, flg2 = 0xc0000, prx = 0x0, ros = 2147483647 bsn = 0xed5 bndsn = 0xee7 spn = 0xef7
      efd = 3
      file:xct.c lineno:1179
      DID: 0001-0011-000000C2
      parent xid: 0x0000.000.00000000
      env: (scn: 0x0000.00234718  xid: 0x0007.018.000001fe  uba: 0x0080065c.017a.02  statement num=0  parent xid: xid: 0x0000.000.00000000  scn: 0x00
00.00234718 0sch: scn: 0x0000.00000000)
      cev: (spc = 7818  arsp = 914e8310  ubk tsn: 1 rdba: 0x0080065c  useg tsn: 1 rdba: 0x00800069
            hwm uba: 0x0080065c.017a.02  col uba: 0x00000000.0000.00
            num bl: 1 bk list: 0x91435070)
            cr opc: 0x0 spc: 7818 uba: 0x0080065c.017a.02
      (enqueue) TX-00070018-000001FE    DID: 0001-0011-000000C2
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00  res_flag: 0x6
      mode: X, lock_flag: 0x0, lock: 0x914b5220, res: 0x925617c0
      own: 0x92b796d0, sess: 0x92b796d0, proc: 0x92a6ffd8, prv: 0x925617d0
       xga: 0x8b7c6d40, heap: UGA
      Trans IMU st: 2 Pool index 65535, Redo pool 0x914b58d0, Undo pool 0x914b59b8
      Redo pool range [0x86de640 0x86de640 0x86e0e40]
      Undo pool range [0x86dbe40 0x86dbe40 0x86de640]
        ----------------------------------------
        SO: 0x91435070, type: 39, owner: 0x914b51e8, flag: -/-/-/0x00
        (List of Blocks) next index = 1
        index   itli   buffer hint   rdba       savepoint
        -----------------------------------------------------------
            0      2   0x647f1fc8    0x41083a     0xee7
?SESSION A? ROLLBACK ?savepoint:
SQL> rollback to NONLOCK;
Rollback complete.
????savepoint ??update??????? ??UPDATE???????? ROLLBACK:
SQL> select * From v$Lock where sid=142 or sid=140;
ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000000924F4A58 00000000924F4A78        140 TX     458776        510          0          6        822          0
0000000091FC6B10 0000000091FC6B38        140 TM      55829          0          3          0        822          0
00000000914B51E8 00000000914B5220        142 TX     458776        510          6          0       1050          1
???? SESSION A 142 ???SAVEPOINT ???????TM LOCK 
????? ROLLBACK TO SAVEPOINT?????SESSION???TX LOCK!!!!
??????SESSION 142???TX ID1=458776 ID2=510, ????ROLLBACK TO SAVEPOINT?????????ABORT TRANSACTION
?? SESSION B  SID=140??  SESSION A ?? , ?????????SESSION B? update???HANG??
?????????CACHE?????:
 Object id on Block? Y
 seg/obj: 0xda16  csc: 0x00.2347b7  itc: 2  flg: O  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.00f.000001e0  0x00800075.02a6.29  C---    0  scn 0x0000.00234711
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
data_block_dump,data header at 0x745d85c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x0745d85c
bdba: 0x0041083a
     76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f9a
avsp=0x1f83
tosp=0x1f83
0xe:pti[0]      nrow=1  offs=0
0x12:pri[0]     offs=0x1f9a
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 2]  c1 02
end_of_block_dump
???? ITL=0x02? ?????????,col  0: [ 2]  c1 02 ?????????
?????????SESSION D ,??????row lock?? ?UPDATE????????
SESSION D:
SQL> update maclean_lock set t1=t1+2;
1 row updated.
SQL> rollback;
Rollback complete.
??SESSION B ???????????
?????ORACLE????????, ??????????? TX lock?? row lock , ????????2???
row lock?????????, ?TX lock????????ENQUEUE LOCK????
?????????PROCESS K?DML???????????????????????,??????????TX LOCK, 
????PROCESS Z?????????????????????????ROW LOCK????????, 
???????OLTP?????????????????????? 
??ROW LOCK?Release ??????TX?ENQUEUE LOCK,?????????Process J ????????????,
Process K??????????? ,Process K?????????,???row piece?lb??0x0 ,?????ITL,
Process Z???ITL???????Process J????XID,?????Process J?????TX lock,
PROCESS K ???TX resource?Enqueue Waiter Linked List?????X mode(exclusive)?enqueue lock?
???Process J??TX lock?,Process J?????TX resource?Enqueue Waiter Linked List
???Process K??????,??POST?????Process K? TX lock??????,
???????row lock???????,?????????
?????????? ?????:
SESSION A ???PID =17 ??????????????????
SESSION B ???PID =24 
??????? "_trace_events"='10000-10999:255:24';  KST trace ??????? Server Process???
SESSION A PID=17  ?? acqure?SX mode???TM Lock ,?? ????Transaction?????UNDO SEGMENT 7,???XID 7.24.510,
?acquire ?X mode? TX-00070018-000001fe ?
?????? 00070018-000001fe ???? 7- 24 - 510? XID ?
781F4B8A:007A569C    17   142 10704  83 ksqgtl: acquire TM-0000da15-00000000 mode=SX flags=GLOBAL|XACT why="contention"
781F4B92:007A569D    17   142 10704  19 ksqgtl: SUCCESS
781F4BB3:007A569E    17   142 10812   2 0x000000000041083A 0x0000000000000000 0x0000000000234717
781F4BBA:007A569F    17   142 10812   3 0x0000000000000000 0x0000000000000000 0x0000000000000000
781F4BC0:007A56A0    17   142 10812   4 0x0000000000000000 0x0000000000000000 0x0000000000000000
781F4BD3:007A56A1    17   142 10812   5 0x000000000041083A 0x0000000000000000 0x0000000000000000
781F4BFE:007A56A2    17   142 10811   1 0x000000000041083A 0x0000000000000000 0x0000000000234711 0x0000000000000002
781F4C06:007A56A3    17   142 10811   2 0x000000000041083A 0x0000000000000000 0x0000000000234718 0x00007FA074EDA560
781F4C26:007A56A4    17   142 10813   1 ktubnd: Bind usn 7 nax 1 nbx 0 lng 0 par 0
781F4C43:007A56A5    17   142 10813   2 ktubnd: Txn Bound xid: 7.24.510
781F4C4A:007A56A6    17   142 10704  83 ksqgtl: acquire TX-00070018-000001fe mode=X flags=GLOBAL|XACT why="contention"
781F4C51:007A56A7    17   142 10704  19 ksqgtl: SUCCESS
?????????? ????????
781F4CBF:007A56A8    17   142 10005   1 KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0
781F4CCC:007A56A9    17   142 10005   2 KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 time=13
781F4CDE:007A56AA    17   142 10005   1 KSL WAIT BEG [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0
786BD85D:007A57E0    17   142 10005   2 KSL WAIT END [SQL*Net message from client] 1650815232/0x62657100 1/0x1 0/0x0 time=5016447
786BD966:007A57E1    17   142 10005   1 KSL WAIT BEG [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0
786BD96E:007A57E2    17   142 10005   2 KSL WAIT END [SQL*Net message to client] 1650815232/0x62657100 1/0x1 0/0x0 time=8
SESSION B ???PID =24  ,??????? SX mode? TM lock,??row lock? acquire X mode?TX-00070018-000001fe
ksqgtl: acquire TM-0000da15-00000000 mode=SX flags=GLOBAL|XACT why="contention"
ksqgtl: SUCCESS
0x000000000041083A 0x0000000000000000 0x00000000002354F8
0x0000000000000000 0x0000000000000000 0x0000000000000000
0x0000000000000000 0x0000000000000000 0x0000000000000000
0x000000000041083A 0x0000000000000000 0x00000000002354F8 0x0000000000000001
0x000000000041083A 0x0000000000000000 0x00000000002354F8 0x0000000008A63780
0x0000000000000001 0x0000000000800861 0x0000000000000241 0x0000000000000001
0x000000000041083A 0x0000000000000001 0x0000000000000001
0x000000000041083A 0x0000000000000000 0x00000000002354F9 0x0000000000000002
ksqgtl: acquire TX-00070018-000001fe mode=X flags=GLOBAL|LONG why="row lock contention"
C4048EBD:007F52B6    24   140 10005   2 KSL WAIT END [enq: TX - row lock contention] 1415053318/0x54580006 458776/0x70018 510/0x1fe time=2929879
C4048ED4:007F52B7    24   140 10005   1 KSL WAIT BEG [enq: TX - row lock contention] 1415053318/0x54580006 458776/0x70018 510/0x1fe
C43146CA:007F535E    24   140 10005   2 KSL WAIT END [enq: TX - row lock contention] 1415053318/0x54580006 458776/0x70018 510/0x1fe time=2930676
????????? ,PID=24 ??????ksqcmi???????? deadlock
C43146D9:007F535F    24   140 10704 134 ksqcmi: performing local deadlock detection on TX-00070018-000001fe
C43146F8:007F5360    24   140 10704 150 ksqcmi: deadlock not detected on TX-00070018-000001fe
?? ??? PID 17 ??ROLLBACK ???? ,????????:
PID 17 
ROLLBACK;
D7A495BB:007F9D3E    17   142 10005   4 KSL POST SENT postee=24 loc='ksqrcl' id1=0 id2=0 name=   type=0
D7A495D8:007F9D3F    17   142 10444  12 ABORT TRANSACTION - xid: 0x0007.018.000001fe
??  PID 17 ??? TX resource?Enqueue Waiter linked List ???PID 24???,????KSL POST SENT ?? PID 24,
???ksqrcl???ENQUEUE LOCK
?PID 24??????KSL POST (KSL POST RCVD poster=17), ?ksqgtl???? TX-00070018-000001fe ?? ksqrcl??,
??PID 24???????? TX lock?USN ,??????? USN 3 XID 3.11.582 ,???acquire TX-0003000b-00000246
D7A49616:007F9D41    24   140 10005   3 KSL POST RCVD poster=17 loc='ksqrcl' id1=0 id2=0 name=   type=0 fac#=0 facpost=1
D7A4961C:007F9D42    24   140 10704  19 ksqgtl: SUCCESS
D7A4967D:007F9D43    24   140 10704 117 ksqrcl: release TX-00070018-000001fe mode=X
D7A496A5:007F9D44    24   140 10813   1 ktubnd: Bind usn 3 nax 1 nbx 0 lng 0 par 0
D7A496C2:007F9D45    24   140 10813   2 ktubnd: Txn Bound xid: 3.11.582
D7A496C7:007F9D46    24   140 10704  83 ksqgtl: acquire TX-0003000b-00000246 mode=X flags=GLOBAL|XACT why="contention"
D7A496E4:007F9D47    24   140 10704  19 ksqgtl: SUCCESS
   
   
   
  ROW LOCK?Release ??????TX?ENQUEUE LOCK,?????????Process J ????????????, Process K??????????? ,Process K?????????,???row piece?lb??0×0 ,?????ITL,Process Z???ITL???????Process J????XID,?????Process J?????TX lock,PROCESS K ???TX resource?Enqueue Waiter Linked List?????X mode(exclusive)?enqueue lock? ???Process J??TX lock?,Process J?????TX resource?Enqueue Waiter Linked List ???Process K??????,??POST?????Process K? TX lock??????,???????row lock???????,?????????