Know More About Oracle Row Lock（二、解决疑惑，说明行级锁和T
程序员文章站
2022-05-21 12:32:43
...
我们都知道在Oracle中实现了细粒度的行锁row lock，且在ORACLE的内部实现中没有使用基于内存的行锁管理器，row lock是依赖于数据块本身实现的。换句话说判定一行数据究竟有没有没锁住，要求Server Process去pin住相应的block buffer并检查才能够发现。 但是
我们都知道在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 就会在（属于Process J这个事务的TX lock的 不是的 应该是各个TX lock共同拥有一个）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也已经被我释放，你可以继续工作了。
- Know Oracle Lock Mode
- How to trouble shooting Library cache lock/pin
- Row Cache lock Problem
- Oracle Enqueue Lock Type Reference including 11g new locks
- 11gR2新特性:LMHB Lock Manager Heart Beat后台进程