[大数据] Oracle-在线重定义dbms_redefinition.sync_interim

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> Oracle-在线重定义dbms_redefinition.sync_interim_table增量同步引发TX行锁问题 -> 正文阅读

[大数据]Oracle-在线重定义dbms_redefinition.sync_interim_table增量同步引发TX行锁问题

前言:

近期处理了一起用户使用在线重定义dbms_redefinition增量同步操作引发TX行锁的问题，用户在使用dbms_redefinition.sync_interim_table进行数据增量同步时，在线重定义的原表SQL语句出现了TX行锁等待问题

后面经过分析，发现产生TX行锁问题的原因为在线重定义在完成增量同步之后，会对增量MLOG$_XXX日志进行删除，删除期间为锁定维护MLOG$的原表数据，同时由于原表进行的dml操作会记录到MLOG$_XXX增量日志，也需要维护MLOG$的原表数据，导致操作了MLOG$的同一行数据，出现TX行锁等待问题。

问题:

在Oracle11.2.0.4版本，执行dbms_redefinition.sync_interim_table进行数据增量同步时，产生TX行锁问题

BEGIN
 dbms_redefinition.sync_interim_table(
 uname => 'TEST',
 orig_table => 'TEST_OLD',
 int_table => 'TEST_NEW');
 END;
 /

原因:

产生TX行锁问题的原因为dbms_redefinition.sync_interim_table在线重定义在完成增量同步之后，会对增量MLOG$_TEST_OLD物化视图日志进行删除，删除期间为锁定维护MLOG$的原表数据，同时由于源表进行的dml操作会记录到MLOG$_TEST_OLD物化视图日志，也需要维护MLOG$的原表数据，导致多个会话操作了MLOG$的同一行数据，出现TX行锁等待问题

问题分析:

查询问题时间的TX锁堵塞情况,会话44被会话34所堵塞，堵塞的锁类型为TX行锁，级别为6，独占模式

会话的执行语句为begin p1;end;?存储过程里面的操作语句为insert into test.test_old values

set linesize 400
set pagesize 400
col sample_time for 40
col sql_text for a60
col event for a40
col program for a30 
col sql_text for a20
col CURRENT_OBJECT for a20
col object_type for a15
SELECT  
    TO_CHAR(D.SAMPLE_TIME, 'YYYYMMDD HH24:MI:SS') SAMPLE_TIME,
    D.SESSION_ID,
    D.program,
      D.current_row#,D.EVENT,
      CHR(BITAND(P1, -16777216) / 16777215) ||
      CHR(BITAND(P1, 16711680) / 65535) "Lock",
      BITAND(P1, 65535) "Mode",
      D.BLOCKING_SESSION,
      b.sql_text
 FROM DBA_HIST_ACTIVE_SESS_HISTORY D,dba_hist_sqltext b 
WHERE D.SAMPLE_TIME between to_date('2022-11-12 21:30:00','yyyy-mm-dd hh24:mi:ss') and to_date('2022-11-12 22:30:00','yyyy-mm-dd hh24:mi:ss')
  AND D.EVENT = 'enq: TX - row lock contention'  and d.sql_id=b.sql_id(+)
order by D.BLOCKING_SESSION;

查看会话34的执行语句为删除物化视图日志MLOG$_TEST_OLD：delete from "TEST"."MLOG$_TEST_OLD"

delete from "TEST"."MLOG$_TEST_OLD" 
where rowid in  ( select mas$.rowid      from "TEST"."MLOG$_TEST_OLD" mas$, sys.snap_xcmt$ map$      
where mas$.xid$$ map$.xid and map$.commit_scn <= :1 )

被堵塞会话与堵塞会话涉及的两个表TEST_OLD与MLOG$_TEST_OLD，MLOG$_TEST_OLD为原表TEST_OLD的物化视图日志，TEST_OLD发生DML操作，会往MLOG$_TEST_OLD里面插入增量信息，但insert?MLOG$_TEST_OLD与delete?MLOG$_TEST_OLD应该是操作不同的行数据，存在行锁冲突的可能很小，发生行锁冲突的表应该是其他表，但由于数据库记录的信息有限，我们无法进一步去获取锁信息

我们需要重现当时的行锁问题，并记录当时的锁信息，才能进一步的确认原因，跟用户再次确认当时产生行锁的情况，确定当时只是执行在线重定义的增量同步dbms_redefinition.sync_interim_table之后就出现的TX行锁情况

在测试环境，我们对问题进行了重现，并使用10704对锁信息进行跟踪

--为了更好的追踪锁信息，我们对数据库开启了全局的10704跟踪，这样可以更快的跟踪锁和队列的使用情况
alter system set events '10704 trace name context forever, level 12'; 
alter system set events '10704 trace name context off';

在会话A,我们执行了在线重定义的增量同步dbms_redefinition.sync_interim_table

BEGIN
dbms_redefinition.sync_interim_table(
uname => 'TEST', 
orig_table => 'TEST_OLD',
int_table => 'TEST_NEW');
END;
/

同时，在会话B，我们执行关于原表TEST_OLD的DML操作

exec p1;

很快，在会话A的dbms_redefinition.sync_interim_table执行完成之后，我们观察到了会话B出现了TX锁等待，问题得到复现

接下来，分析10704跟踪的会话B的锁信息，可以看到在申请完表锁TM-0000028a-00000000-mode 3之后，申请行锁TX-00030016-000006da-mode 6出现了3分多钟的等待，从08:51:03到08:54:22申请才返回

继续分析会话A的锁信息，可以看到会话A在08:50:39申请了表锁TM-0000028a-00000000-mode 3以及行锁TX-00030016-000006da-mode 6，之后就再也没有释放

会话A直到08:54:22才分别释放了行锁TX,30016,6da以及表锁TM,28a,0

到这里，我们可以确认行锁的堵塞信息为会话A长期持有表TM,28a,0的行锁TX,30016,6da，导致会话B出现了等待，而发生锁的对象object_id(16进制)为0000028a，我们可以查到对象为表SYS.MLOG$，MLOG$为数据库字典物化视图的信息维护表，记录每个物化视图日志的信息

SQL> set linesize 400
SQL> r
  1  select owner,object_name,object_type
  2  from dba_objects
  3* where object_id =to_number('28a','xxx')
?
OWNER                OBJECT_NAME                              OBJECT_TYPE
-------------------- ---------------------------------------- -------------------
SYS                  MLOG$                                    TABLE

接下来，我们还要分析会话A和会话B分别执行了哪些操作语句产生了TX行锁问题，这一步我们会开启10046来跟踪两个会话的SQL执行情况

--为了更好的追踪锁信息，我们对数据库开启了全局的10046跟踪，这样可以更好的跟踪sql的执行情况
alter system set events '10046 trace name context forever, level 12'; 
alter system set events '10046 trace name context off';

分析会话A的sql执行情况

在开始清理"TEST"."MLOG$_TEST_OLD"之前，会执行一个for update的查询锁定操作，锁定表MLOG$的master='TEST_OLD' and?mowner='TEST'这行数据

select log, sysdate, youngest, youngest+1/86400,  oldest, oldest_pk, oldest_oid, oldest_new, oldest_seq,  oscn, oscn_pk, oscn_oid, oscn_new, oscn_seq, flag, purge_job  
from sys.mlog$  where master = :2 and mowner = :1 for update
PARSE #140104910940200:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1551139906,tim=1668303489973102
BINDS #140104910940200:
 Bind#0
  oacdty=01 mxl=32(08) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7f6cb7729328  bln=32  avl=08  flg=05
  value="TEST_OLD"
 Bind#1
  oacdty=01 mxl=32(04) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7f6cb7729360  bln=32  avl=04  flg=05
  value="TEST"

之后开始清理"TEST"."MLOG$_TEST_OLD"表数据

delete from "TEST"."MLOG$_TEST_OLD" where rowid in    ( select mas$.rowid      
from "TEST"."MLOG$_TEST_OLD" mas$, sys.snap_xcmt$ map$      
where mas$.xid$$ = map$.xid and            map$.commit_scn <= :1 )

清理完之后，再更新表MLOG$的master='TEST_OLD' and?mowner='TEST'这行数据

update sys.mlog$ 
set oscn = :1, oscn_pk = :2, oscn_oid = :3,  oscn_new = :4, oscn_seq = :5,  last_purge_date = sysdate, last_purge_status = 0, rows_purged = :8,     purge_job = :9                               
where master = :6 and mowner = :7

分析会话B的sql执行情况

执行对表TEST.TEST_OLD的插入

INSERT INTO TEST.TEST_OLD VALUES(TEST_SEQ.NEXTVAL,:B1 +1,'aaaaaaaaa','bbbbbbbbbbbbbbbb','cccccccccccccc')

将TEST_OLD的增量数据插入物化视图日志"TEST"."MLOG$_TEST_OLD"

INSERT /*+ IDX(0) */ INTO "TEST"."MLOG$_TEST_OLD" (dmltype$$,old_new$$,change_vector$$,xid$$,"ID") VALUES (:d,:o,:c,:x,:1)

更新表MLOG$的master='TEST_OLD' and?mowner='TEST'这行数据

update sys.mlog$ 
set flag=flag+131072 
where mowner=:1 and master=:2 and bitand(flag,131072)=0

结合锁信息以及执行的SQL绘制出当时行锁的堵塞情况

T1时刻，会话A执行了对于表MLOG$的for update操作，并一直持有TM,28a,0,TX,30016,6da锁

T2时刻，会话A开始执行删除MLOG$_TEST_OLD的操作delete?MLOG$_TEST_OLD,会话B开始执行表TEST_OLD,MLOG$_TEST_OLD的插入操作

T3时刻，会话A继续执行删除MLOG$_TEST_OLD的操作，会话B在执行完表TEST_OLD,MLOG$_TEST_OLD的插入之后，需要更新MLOG$的信息，申请TM,28a,0,TX,30016,6da锁，由于会话A持有，会话B进入等待

T4时刻，会话A执行完成删除MLOG$_TEST_OLD的操作，更新MLOG$的信息，会话B继续等待TM,28a,0,TX,30016,6da锁

T5时刻，会话A释放TM,28a,0,TX,30016,6da锁，会话B获得TM,28a,0,TX,30016,6da锁

问题总结:

综上，执行在线重定义dbms_redefinition.sync_interim_table同步增量数据时，产生TX行锁问题的原因为dbms_redefinition.sync_interim_table在线重定义在完成增量同步之后，会对增量MLOG$_TEST_OLD物化视图日志进行删除，删除期间为锁定维护MLOG$的原表数据，同时由于源表进行的dml操作会记录到MLOG$_TEST_OLD物化视图日志，也需要维护MLOG$的原表数据，导致多个会话操作了MLOG$的同一行数据，出现TX行锁等待问题

问题建议:

1 使用dbms_redefinition.sync_interim_table发生TX锁问题，这个目前在Oracle的11.2.0.4，12.2.0.1版本上都有发现，在19c版本没有发现，因为19c版本使用dbms_redefinition.sync_interim_table并不会清理MLOG$_XXXXX数据，所以，使用在线重定义的方式，最好在19c的版本上

2 TX锁的时间主要取决于清理MLOG$_XXXX物化视图日志的时长，所以要控制每次增量的数据，减少TX锁的锁定时间

3 在原表DML操作较为空闲的时间段进行增量数据的同步，并监控TX锁的情况，一旦发现大面积的TX锁堵塞，可以kill了清理MLOG$_XXXX物化视图日志的操作