欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

ApexSQL Log中的Redo Script跟原始SQL不一致问题

程序员文章站 2022-06-22 16:30:59
最近遇到一个误更新数据的问题,使用ApexSQL Log做挖掘事务日志的时候,发现ApexSQL Log生成的Redo Script跟原始SQL是有区别的。它们并不是完全一致的。只是逻辑上等价而已。如下所示,我们做一个测试,如下所示,创建一个表后,并模拟忘记添加条件,出现误删除数据的情况 SELEC... ......

最近遇到一个误更新数据的问题,使用apexsql log做挖掘事务日志的时候,发现apexsql log生成的redo script跟原始sql是有区别的。它们并不是完全一致的。只是逻辑上等价而已。如下所示,我们做一个测试,如下所示,创建一个表后,并模拟忘记添加条件,出现误删除数据的情况

 

select * into kerry_test from sys.objects;
 
 
delete from kerry_test

 

 

然后我们用apexsql log挖掘事务日志,你会看到有很多对应的delete记录,而且对应的redo script跟原始sql是不一样的。如下所示,

 

 

 

 

下面是其中两个redo  script(为了方便查看,对脚本进行了格式化)

 

 

--delete (0003f73c:000065fc:000d) done at 2019-06-13 10:35:56.876 by xxx\xxxx in transaction 0000:0adf73ea (committed)
begin transaction;
delete  from [dbo].[kerry_test]
where   /*** warning: where clause for this statement was generated for a table with no primary key and no clustered index ***/
        [name] = n'sysfgfrag' collate chinese_prc_ci_as
        and [object_id] = 19
        and [principal_id] is null
        and [schema_id] = 4
        and [parent_object_id] = 0
        and [type] = n's ' collate latin1_general_ci_as_ks_ws
        and [type_desc] = n'system_table' collate latin1_general_ci_as_ks_ws
        and [create_date] = '20160101 10:46:55.060'
        and [modify_date] = '20160101 10:46:55.100'
        and [is_ms_shipped] = 1
        and [is_published] = 0
        and [is_schema_published] = 0;
if @@rowcount <= 1
    commit transaction;
else
    begin
        rollback transaction;
            print 'error: statement affected more than one row. all the changes were rolled back.';
    end;
 
 
 
--delete (0003f73c:000065fc:000a) done at 2019-06-13 10:35:56.876 by xxx\xxx in transaction 0000:0adf73ea (committed)
begin transaction;
delete  from [dbo].[kerry_test]
where   /*** warning: where clause for this statement was generated for a table with no primary key and no clustered index ***/
        [name] = n'sysseobjvalues' collate chinese_prc_ci_as
        and [object_id] = 9
        and [principal_id] is null
        and [schema_id] = 4
        and [parent_object_id] = 0
        and [type] = n's ' collate latin1_general_ci_as_ks_ws
        and [type_desc] = n'system_table' collate latin1_general_ci_as_ks_ws
        and [create_date] = '20160101 10:47:02.050'
        and [modify_date] = '20160101 10:47:02.057'
        and [is_ms_shipped] = 1
        and [is_published] = 0
        and [is_schema_published] = 0;
if @@rowcount <= 1
    commit transaction;
else
    begin
        rollback transaction;
            print 'error: statement affected more than one row. all the changes were rolled back.';
    end;
 

 

 

如果有聚集索引,你会看到没有where条件的update、delete操作都会生成很多个对应的redo script,对应的where条件则是聚集索引字段。如下测试所示:

select  *
into    kerry_test2
from    sys.objects;
 
create clustered index pk_kerry_test2 on kerry_test2(object_id);
 
update  kerry_test2
set     name = 'kerry';
 
 
delete  from kerry_test2;

 

 

 

下面是对应的update、delete操作的redo script

--update (0003f73c:0000700a:001f) done at 2019-06-13 11:27:26.036 by xxx\xxx in transaction 0000:0adf7404 (committed)
begin transaction; 
update  [dbo].[kerry_test2]
set     [name] = n'kerry' collate chinese_prc_ci_as
where   [object_id] = 3;
if @@rowcount <= 1
    commit transaction;
else
    begin
        rollback transaction;
            print 'error: statement affected more than one row. all the changes were rolled back.';
    end;
 
 
 
--    delete (0003f73c:00007482:00d9) done at 2019-06-13 11:27:35.406 by xxx\xxx in transaction 0000:0adf7405 (committed)
begin transaction;
delete  from [dbo].[kerry_test2]
where   [object_id] = 836302139;
if @@rowcount <= 1
    commit transaction;
else
    begin
        rollback transaction;
            print 'error: statement affected more than one row. all the changes were rolled back.';
    end;

 

这里只是简单记录一下这个事情,千万不要以为redo script跟原始sql是完全一致的。如果遇到这种问题,如何判断呢?可以查看transaction id,以及对应的begin time , end time,如下所示,你会发现它们是一致的