Know More About Oracle Row Lock（二、解决疑惑，说明行级锁和T

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

 

 

 

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

1. Know Oracle Lock Mode
2. How to trouble shooting Library cache lock/pin
3. Row Cache lock Problem
4. Oracle Enqueue Lock Type Reference including 11g new locks
5. 11gR2新特性:LMHB Lock Manager Heart Beat后台进程