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

短命女 2024-05-01 16:04 8阅读 0赞

**事故背景：**

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

![ba29b7b84f6e47cca3d16847149efd0a.png][]

查询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

![6ec4f0bde90d46f0b6e4742e5426d8c7.png][]

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

我们来具体分析：

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秒 + 手动补偿（快速解决问题）

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

[ba29b7b84f6e47cca3d16847149efd0a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/01/9ab15468e5dd4053b57dda8cdfc15fa0.png
[6ec4f0bde90d46f0b6e4742e5426d8c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/05/01/0da88213d9e94d5787d924232b55d4db.png