并发事务对数据同步的影响

事故背景：

1.由于历史原因和业务需要，我们需要把A库的test表数据，同步到B库的test表中

2.因为A库是sqlserver，旧同步使用的timestamp版本字段，进行的新增、更新检测；后期我们都会迁移到mysql中，所以这部分改用updateTime字段进行新增、更新检测

3.又因为，updateTime字段的更新并没有被所有业务覆盖，即有的业务更新test表，但没有更新updateTime；且手动更新表的情况也不会总更新updateTime；且sqlserver并没有mysql的DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 的功能

4.使用sqlserver的触发器功能为updateTime赋值,触发器代码如下

<-- 触发器 -->
CREATE TRIGGER [dbo].[tgr_test_updtim]
ON [dbo].[test]
WITH EXECUTE AS CALLER
FOR UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.[test]
    SET updateTime=SYSDATETIME()
    WHERE id IN (SELECT DISTINCT id FROM inserted)
END

事故描述：

1.updateTime = '2023-03-02 15:11:59.995' 的数据记录没有同步过去，我们称为记录a

2.updateTime = '2023-03-02 15:12:00.006' 的数据记录同步过去了，我们称为记录b

3.且记录已经同步过去的数据的最大updateTime为 '2023-03-02 15:38:59.725'，这意味着a记录在同步过程中丢失了

4.同步的过程为，我们根据当前同步的最大updateTime，去A库的拍品表中查询大于等于的数据，同步到B库，并记录这部分数据中最大的updateTime，更新当前同步的最大updateTime

原因猜测：

1.A库主从延迟，导致同步查询从库时，没有查询到记录a；

          -- 但是记录b同步过来了，主从同步是根据binlog过来的，是严格的顺序执行，且在a记录之后的b记录已经同步过来了。==》否定

2.记录a的updateTime字段生成后并没有立即持久化到A库中，且在这个过程中记录b生成了updateTime且持久化了；这时候同步代码查询，并使用记录b的（或更大的）updateTime作为当前同步的最大updateTime；而在同步代码查询之后，记录a持久化成功了，导致记录a被同步代码遗漏

          -- 听起来合理，但是还要想办法验证；且如果该原因成立，旧代码中根据timestamp字段进行同步的代码也有一样的问题

验证思路：

1.当前表中有触发器、CDC；所以涉及到三个时间：触发器生成的updateTime，实际落库提交时间、CDC记录的时间

       1.1 触发器生成的updateTime：SET updateTime=SYSDATETIME()在编译期就会调用，即commit之前

        1.2 实际落库时间

        1.3 CDC记录时间

手动开启两个事务，并进行更新如下：

-- mysql的默认全局事务隔离级别是可重复度 - Repeatable read
    查询方式1(8以下)：SELECT @@GLOBAL.tx_isolation, @@tx_isolation;    
    查询方式2(8以上)：SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;
-- sqlserver的连接默认是读已提交-Read committed

-- 事务一  id = 123的sp=3.55，updateTime='2023-03-14 15:30:35.256'
BEGIN TRANSACTION
SELECT id,sp FROM test WHERE id = 123;
UPDATE test SET sp = 3.56 WHERE id = 123;
SELECT id,sp FROM test WHERE id = 123;
COMMIT;


-- 事务二  id = 234的sp=3.95,updateTime = '2023-01-05 19:03:33.266'
BEGIN TRANSACTION
SELECT id,sp FROM test WHERE id = 234
UPDATE test SET sp = 3.55 WHERE id = 234;
SELECT id,sp FROM test WHERE id = 234;
COMMIT;

执行顺序为：

1.开启事务一

2.事务一更新前查询

3.事务一更新 id为123的记录

4.事务一查询（隔离级别是读已提交，但事务内可读，是更新后的值）

5.事务二开启、更新前查询

6.更新（id为234的记录）、查询

7.事务二提交

6.事务一提交

查询CDC，id=123的记录如下

 select createTime,UpdateTime, sp,
 sys.fn_cdc_map_lsn_to_time(__$start_lsn) cdc_createTime,
 __$operation from cdc.test_CT where id = 123

 查询CDC，id=234的记录如下

 select createTime,UpdateTime, sp,
 sys.fn_cdc_map_lsn_to_time(__$start_lsn) cdc_createTime,
 __$operation from cdc.test_CT where id = 234

 我们的第二个猜想得到了验证！！！

我们来具体分析：

1.CDC表子段查询含义

         UpdateTime、sp 是 test表中字段的值

         cdc_createTime 是test_CT表中记录生成时间

        __$operation 是test_CT表中记录类型，1-删除，2新增，3更新前，4更新后

2.触发器在更新操作时触发，且和更新操作属于同一个事务

再次看执行顺序和对应结果

执行顺序为：

1.开启事务一

2.事务一更新前查询  ==》   

       (old) id = 123的sp=3.56,updateTime = '2023-03-14 15:30:35.256' 

3.事务一更新 id为123的记录  ==》

        3.1 id=123,sp=3.56，updateTime = '2023-03-14 15:30:35.256' (未提交/未持久化) ==》

        3.2 sp更新触发记录CDC记录（但是没有持久化）==》

                更新前：id=123,sp=3.55，updateTime = '2023-03-14 15:30:35.256'

                更新后：id=123,sp=3.56，updateTime = '2023-03-14 15:30:35.256'

        3.3 触发器触发，更新updateTime字段（和3.2的执行顺序没有先后）==》

                id=123,sp=3.56，updateTime = '2023-03-14 16:24:02.010'（未提交/未持久化）

        3.4 触发器更新updateTime字段触发CDC记录（没有持久化）

                更新前：id=123,sp=3.56，updateTime = '2023-03-14 15:30:35.256'

                更新后：id=123,sp=3.56，updateTime = '2023-03-14 16:24:02.010'

4.事务一查询（隔离级别是读已提交，但事务内可读，是更新后的值）

      （new）id=123,sp=3.56，updateTime = '2023-03-14 16:24:02.010'（未提交/未持久化）

5.事务二开启、更新前查询

        (old) id = 234的sp=3.95,updateTime = '2023-01-05 19:03:33.266' 

6.更新（id为234的记录）、查询

        6.1 id=234,sp=3.55，updateTime = '2023-01-05 19:03:33.266' (未提交/未持久化) ==》

        6.2 sp更新触发记录CDC记录（但是没有持久化）==》

                更新前：id=234,sp=3.95，updateTime = '2023-01-05 19:03:33.266'

                更新后：id=234,sp=3.55，updateTime = '2023-01-05 19:03:33.266'

        6.3 触发器触发，更新updateTime字段（和3.2的执行顺序没有先后）==》

                id=234,sp=3.55，updateTime = '2023-03-14 16:24:02.010'（未提交/未持久化）

        6.4 触发器更新updateTime字段触发CDC记录（没有持久化）

                更新前：id=234,sp=3.55，updateTime = '2023-01-05 19:03:33.266'

                更新后：id=234,sp=3.55，updateTime = '2023-03-14 16:24:41.893'

7.事务二提交 (提交时间：2023-03-14 16:24:32.758)

        CDC记录的创建时间为实际提交时间

6.事务一提交(提交时间：2023-03-14 16:25:01.346)

        CDC记录的创建时间为实际提交时间

时间顺序行为如下：

1.id=123的记录，《2023-03-14 16:24:02.010》更新sp, 3.55->3.56;

触发器触发更新updateTime，生成updateTime为《2023-03-14 16:24:02.010》

2.id=234的记录，《2023-03-14 16:24:41.893》更新sp, 3.55->3.56;

触发器触发更新updateTime，生成updateTime为《2023-03-14 16:24:41.893》

3.《2023-03-14 16:24:45.758》提交id=234的事务 ==>CDC这个时间生成四条记录，分别是是id = 234更新sp前、后的记录+更新updateTime字段前、后的记录

4.《2023-03-14 16:25:01.346》提交id=123的事务 ==>CDC这个时间生成四条记录，分别是id = 123的更新sp前、后的记录+更新updateTime字段前、后的记录

总结：

1.触发器和更新操作时同一个事务中执行的，触发器中SYSDATETIME()方法在编译执行时就获得了时间结果

2.先触发更新的提交时间可能晚于后触发更新的提交

3.CDC记录的sys.fn_cdc_map_lsn_to_time(__$start_lsn) cdc_createTime 是提交时的时间

问题分析、确认完成，解决方案就不再话下

同样的，sqlserver的timestamp作为同步数据条件也会有一样的问题

解决方案：同步时间查询时往前推30秒 + 手动补偿（快速解决问题）

不足：预警确实，问题感知被动