Know More About Oracle Row Lock
- by Liu Maclean(???)
??????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???????,?????????
