并发事务对数据同步的影响
事故背景：
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
BEGINSET 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秒 + 手动补偿（快速解决问题）
不足：预警确实，问题感知被动