《DTC 2024 数据技术嘉年华》后续UPdate 重启动测试

背景

  在《DTC 2024 数据技术嘉年华》听到杨廷琨老师讲的UPDATE重启动的原理，手痒难耐的想自己测试一把补充一下自己的空白知识点。最近开发也一直反馈业务的UPDATE操作偶尔会超时，于是猜测是不是这么幸运的遇上了。

操作

1、准备表和数据

drop table t_update purge;
create table t_update(id number(4,2));
-- 开启补充日志
ALTER TABLE t_update ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
insert into t_update values(1);
insert into t_update values(2);
insert into t_update values(3);
commit;


2、执行SQL

• Session 1:

SQL> SET SQLP 'SQL1>'
SQL1>set time on
21:29:50 SQL1>update t_update set id = 4 where id >2;
1 row updated.
21:30:35 SQL1>commit;
Commit complete.


• Session 2:

SQL> SET SQLP 'SQL2>'
SQL2>set time on
21:30:04 SQL2>update t_update set id=id+0.1 where id>0;
4 rows updated.
21:30:37 SQL2>
21:30:41 SQL2>select * from t_update;
ID
----------
1.1
2.1
4.1
5.1

• Session 3:

SQL> SET SQLP 'SQL3>'
SQL3>set time on
21:30:22 SQL3>insert into t_update values(5);
1 row created.
21:30:23 SQL3>commit;
Commit complete.


• 截图
  

日志挖掘

SQL> col MEMBER for a100
SQL> select GROUP# ,MEMBER from v$logfile where GROUP#=7;
GROUP# MEMBER
---------- --------------------------------------------------------------------
7 /u01/oracle/oradata/two/redo07.log
-- 添加日志：
SQL> execute dbms_logmnr.add_logfile(logfilename=>'/u01/oracle/oradata/two/redo07.log',options=>DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed
-- 开始挖掘
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed
SQL> select
2 to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp,
3 sql_redo,
4 from v$logmnr_contents where seg_name='T_UPDATE' order by scn;
-- 结束挖掘
SQL> EXECUTE DBMS_LOGMNR.END_LOGMNR;
PL/SQL procedure successfully completed


未带 where 的更新：update t_update set id=id+0.1;

• 未发生UPDATE重启动
  

结语：

  和开发碰了一下业务update逻辑，并不符合重启动的条件。不过还是很幸运参加《数据技术嘉年华》真得是收获颇丰，感谢平台。