Know More About Oracle Row Lock（二、解决疑惑，说明行级锁和T-tutoriel mysql-php.cn

我们都知道在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 

<span><strong>但是请注意 ROLLBACK TO SAVEPOINT并不会释放SESSION已获得TX LOCK！！！！</strong></span>
<span><strong>如上面的输出SESSION 142仍持有TX ID1=458776 ID2=510， 这是因为ROLLBACK TO SAVEPOINT并不会放弃整个事务ABORT TRANSACTION</strong></span>

但是 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实现的。

<span>问题在于若一个进程PROCESS K在DML过程中发现其所需要的数据行已经被其他进程锁定了，如果不依赖于内存中的TX LOCK， </span>
<span>这意味着PROCESS Z需要定期去读取检查该数据行锁在的数据块以发现相应的ROW LOCK是否已经被释放了， </span>
<span>可以想象如果在OLTP环境中这样去设计所造成的性能损失将是巨大的。 </span>

所以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

<span><strong>接着 我们对 PID 17 执行ROLLBACK 彻底回滚 ，真正放弃这个事务：</strong></span>

PID 17 

ROLLBACK;

D7A495BB:007F9D3E    17   142 10005   4 <span>KSL POST SENT postee=24 loc='ksqrcl'</span> 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 <span>KSL POST RCVD poster=17 loc='ksqrcl'</span> 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

Copier après la connexion

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也已经被我释放，你可以继续工作了。