国产一区二区三区久久精品,亚洲精品综合一区二区在线观看,久久成人精品视频

1、理論來(lái)源于實(shí)踐

現(xiàn)象：于2025-08-13 21:45:35，事實(shí)邏輯表將自身的指標(biāo)與維度同步到原子服務(wù)的實(shí)現(xiàn)時(shí)，出現(xiàn)同步過(guò)來(lái)的指標(biāo)與維度丟失。

核心原因：兩次重復(fù)的事實(shí)邏輯表同步時(shí)間非常相近，導(dǎo)致同步過(guò)來(lái)的指標(biāo)與維度丟失。

2、倒帶進(jìn)事故現(xiàn)場(chǎng)

邏輯表向原子服務(wù)同步的核心邏輯是 “先刪后增”：刪除舊數(shù)據(jù)→對(duì)比新老數(shù)據(jù)→插入新增數(shù)據(jù)，具體流程如下：

整體業(yè)務(wù)代碼精簡(jiǎn)邏輯如下：

@Transactional(rollbackFor = Exception.class)
public Map driveToAtomService(Map logicTableData, String erp) {
//獲得環(huán)境信息
String env = driveLogicTable.getString(DRIVE_LOGIC_TABLE_ENV);
//獲取/更新實(shí)現(xiàn)id
 Long logicTableId = getOrAddLogicTableId(atomicServiceId, driveLogicTable, erp, EnvType.of(env));
//刪除關(guān)聯(lián)指標(biāo)
 metricImplMapper.deleteByLogicTableIds(Collections.singletonList(logicId));
//獲取請(qǐng)求中的所有的指標(biāo)信息
List metricList = getMetricImpls(logicTableData, logicTableId);
//獲取需要新增的指標(biāo)實(shí)現(xiàn)(包含了查詢庫(kù)里現(xiàn)有的指標(biāo)實(shí)現(xiàn))
List metricImpls = metricImplMapper.getMetricImpls(logicTableId);
Set metricDefIdSet = metricImpls.stream()
            .map(MetricImplRelBO::getMetricDefId).collect(Collectors.toSet());
List addList = metricList.stream()
            .filter(s -> !metricDefIdSet.contains(s.getMetricDefId())).collect(Collectors.toList());
//將需要新增的指標(biāo)實(shí)現(xiàn)插入數(shù)據(jù)庫(kù)
 addMetricImpl(addList);

}

用一個(gè)請(qǐng)求進(jìn)行舉例：

{"header":{"appKey":null,"uuid":"ce7cef2d-c417-464a-a519-311599fddfca","serviceName":"driveToAtomService","context":{"PIN":"wanyue3"}},"body":{"dimList":[{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":72,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2501,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2484,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2502,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4591,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3822,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4523,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4524,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":76,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1767,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1907,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1598,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4620,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4621,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4622,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2504,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2485,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2486,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2487,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2488,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3077,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3080,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3081,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2483,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2482,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3082,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3083,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4851,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2503,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5070,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5044,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5087,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5144,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5145,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3089,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3680,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2223,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5428,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5101,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1315,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5247,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":3318,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5262,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":4646,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2252,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2254,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2959,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2958,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2728,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":2618,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":5061,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":6032,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":6375,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":6388,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":6389,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1316,"type":"ALL"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1081,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1351,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1082,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1499,"type":"COMBINE"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1596,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1606,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1083,"type":"FILTER"},{"isRequiredForFilter":0,"supportCrossTopic":0,"dimDefId":1108,"type":"FILTER"}],"dimCombineList":[],"metricList":[{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19872,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19873,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19875,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19945,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":17263,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"COUNT_DISTINCT","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":28017,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":20242,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":18450,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":20276,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":18452,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":18453,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":18456,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19866,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":21691,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"},{"lightDecorateIdList":"","aggregationType":"toSql","middleAggregationType":"DEFAULT","extendDecorateIdList":"4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867","staticDecorateIdListCombination":"","metricDefId":19871,"nameEnAtomic":null,"extendFunctionIdList":"25,6,5,7,23,22,20,21,19,18,4,1,2,3,24"}],"driveLogicTable":"{"dimensionType":"DETAIL","oldNameCn":"七鮮實(shí)時(shí)交易_for地推中間態(tài)新老標(biāo)志","atomicAliasProd":"prod","implServiceTypeKey":"realtime","originPhysicDataSourceId":0,"nameCn":"七鮮實(shí)時(shí)交易_for地推中間態(tài)新老標(biāo)志","description":"七鮮實(shí)時(shí)交易_for地推中間態(tài)新老標(biāo)志","driveLogicTableId":9881,"driveLogicTableEnv":"DEV","commonDecorateIdList":"9665,3269,3270,3271,4556,8012,8270,6030,7247,6031,7248,6032,7249,6033,7250,6034,6035,2134,7254,7255,2085,619,620,5997,1586,7867,6845","atomicAliasPre":"pre","committer":"panjingrong","physicDataSourceId":9494,"storageType":"ONLINE","atomicAliasDev":"pre"}","atomicServiceId":1088},"pin":"wanyue3"}

共計(jì)15個(gè)指標(biāo)，64個(gè)維度

請(qǐng)求1(事務(wù))		請(qǐng)求2(事務(wù))		?
21:06:17.262	進(jìn)入同步方法	21:06:17.263	進(jìn)入同步方法	?
21:06:17.063	select unify_metric_impl where logic_id = 3245 查詢出15條數(shù)據(jù)(快照讀，readview1)	21:06:17.363	select unify_metric_impl where logic_id = 3245 查詢出15條數(shù)據(jù)(快照讀，readview2)	?
21:06:17.363	delete from unify_metric_impl where logic_id = 3245	21:06:17.372	delete from unify_metric_impl where logic_id = 3245	?
21:06:17.459	select unify_metric_impl where logic_id = 3245 查詢出0條數(shù)據(jù)	?	delete 由于logic_id不是索引，會(huì)表鎖阻塞	?
21:06:18.459	insert into unify_metric_impl 插入的logic_id = 3245的數(shù)據(jù)，15條	?	?	?
21:06:19.408	方法結(jié)束	?	?	?
?	?	21:06:19.529	刪除成功	?
?	?	21:06:20.362	select unify_metric_impl where logic_id = 3245 得到 15條數(shù)據(jù)	?
?	?	21:06:20.435	讀出15條數(shù)據(jù)，比較本次是否有新增指標(biāo)，得出沒有新增指標(biāo)，因此不進(jìn)行新增。addAtomicMetricNameForDrive addList empty	?
?	?	21:06:21.435	方法結(jié)束	?

核心結(jié)論點(diǎn)：

1.請(qǐng)求2的刪除操作被阻塞了，直到請(qǐng)求1執(zhí)行完整個(gè)方法。

2.請(qǐng)求2中去查看當(dāng)前實(shí)現(xiàn)的指標(biāo)的時(shí)候，發(fā)現(xiàn)庫(kù)里已經(jīng)存在所有指標(biāo)不會(huì)進(jìn)行新增，與上一步刪除的邏輯相悖。

3、結(jié)論點(diǎn)深度剖析

3.1 分析結(jié)論一

請(qǐng)求2的刪除操作被阻塞了，直到請(qǐng)求1執(zhí)行完整個(gè)方法。

3.1.1 復(fù)習(xí)mysql的InnoDB鎖機(jī)制

3.1.1.1 不是“一把鎖”，而是 “鎖矩陣”

鎖粒度	共享鎖（S 鎖）（讀鎖，允許多讀）	排他鎖（X 鎖）（寫鎖，獨(dú)占）	意向鎖（表級(jí)，輔助判斷）
表級(jí)	表 S 鎖（極少用，如LOCK TABLES ... READ）	表 X 鎖（極少用，如LOCK TABLES ... WRITE）	意向 S 鎖（IS）、意向 X 鎖（IX）
行級(jí)	行 S 鎖（SELECT ... FOR SHARE）	行 X 鎖（UPDATE/DELETE/INSERT默認(rèn)加）	（行鎖無(wú)需意向鎖）
間隙級(jí)	間隙 S 鎖（無(wú)，間隙只防插入）	間隙 X 鎖（防其他事務(wù)插入相同間隙）	無(wú)
Next-Key	無(wú)	Next-Key 鎖（行鎖 + 間隙鎖，默認(rèn)行鎖算法）	無(wú)

3.1.1.2 一張圖總結(jié)：InnoDB 鎖的 “決策邏輯”

3.1.2 理論應(yīng)用實(shí)踐

3.1.2.1 本次事故的物料：

mysql表：

CREATE TABLE `unify_metric_impl` (
  `id` bigint(50) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `metric_def_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '指標(biāo)定義id',
  `logic_table_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '邏輯表id',
  `name_en_atomic` varchar(256) NOT NULL DEFAULT '' COMMENT '真實(shí)指標(biāo)名',
  `committer` varchar(64) NOT NULL DEFAULT '' COMMENT '負(fù)責(zé)人',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '開始時(shí)間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時(shí)間',
  `metric_atomic_name_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '原子指標(biāo)id',
  `decorate_id_list_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '修飾列表',
  `name_cn_alias_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '中文別名',
  `metric_type_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '指標(biāo)類型：DERIVE 衍生指標(biāo)，F(xiàn)ORMULA 復(fù)合指標(biāo)',
  `description_temp` varchar(64) NOT NULL DEFAULT '' COMMENT '指標(biāo)說(shuō)明',
  `data_type_temp` varchar(16) NOT NULL DEFAULT '' COMMENT '數(shù)據(jù)類型：STRING，DOUBLE, LONG, INT',
  `data_accuracy_temp` tinyint(4) NOT NULL DEFAULT '2' COMMENT '數(shù)據(jù)精度-小數(shù)點(diǎn)后幾位',
  `security_level_temp` varchar(16) DEFAULT '-1' COMMENT '安全等級(jí)',
  `logic_table_id_excel_temp` varchar(16) DEFAULT '-1' COMMENT '模型excelId',
  `implement_type` varchar(32) NOT NULL DEFAULT '' COMMENT '指標(biāo)實(shí)現(xiàn)類型：APP、ATOMIC 原子服務(wù)',
  `app_ori_metric_name_temp` varchar(16) DEFAULT '' COMMENT '所依賴的app層原始名字（適用于導(dǎo)數(shù)任務(wù)改變字段的情況）',
  `name_en_depend_atomic` varchar(256) NOT NULL DEFAULT '' COMMENT '指標(biāo)依賴字段',
  `name_en_depend_app` varchar(16) DEFAULT '' COMMENT '所依賴的app層原始名字（適用于導(dǎo)數(shù)任務(wù)改變字段的情況）',
  `update_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '信息更新狀態(tài) 0-未完成更新，1-完成更新',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '信息更新狀態(tài) 0-未完成更新，1-完成更新',
  `light_decorate_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '點(diǎn)燈修飾id列表',
  `extend_decorate_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '支持的動(dòng)態(tài)修飾id列表',
  `extend_function_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '支持的原子服務(wù)函數(shù)id列表',
  `aggregation_type` varchar(64) NOT NULL DEFAULT '' COMMENT '聚合類型:ORIGINAL 原值 COUNT 計(jì)數(shù) DISTINCT 指定字段去重 SUM 求和 AVG  均值 MIN 求最大值 MAX 求最小值 QUANTITLE 求分位數(shù)',
  `middle_aggregation_type` varchar(30) NOT NULL DEFAULT '' COMMENT '中間層類型,UNKNOWN:未知,AGG_BY_FIELD:按聚合字段分組后聚合,AGG_BY_DAY:按天去重后累加',
  `static_decorate_id_list_combination` varchar(1024) NOT NULL DEFAULT '' COMMENT '支持的固化修飾id列表組合,[[d1,d2],[d2]]',
  PRIMARY KEY (`id`),
  KEY `idx_metric_def_id` (`metric_def_id`,`logic_table_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='指標(biāo)實(shí)現(xiàn)';

3.1.2.2 實(shí)踐分析

通過(guò)mysql的innoDB的鎖決策，可以得出

//刪除關(guān)聯(lián)指標(biāo)
 metricImplMapper.deleteByLogicTableIds(Collections.singletonList(logicId));

delete from unify_metric_impl where logic_table_id in (45631);

mysql的索引：KEY `idx_metric_def_id` (`metric_def_id`,`logic_table_id`)

刪除寫操作，不符合最左匹配原則，因此為表x鎖。

因此請(qǐng)求2的刪除操作需要等待請(qǐng)求1的事務(wù)釋放表鎖后才可繼續(xù)進(jìn)行，符合當(dāng)時(shí)場(chǎng)景。

3.2 分析結(jié)論二

請(qǐng)求2中去查看當(dāng)前實(shí)現(xiàn)的指標(biāo)的時(shí)候，發(fā)現(xiàn)庫(kù)里已經(jīng)存在所有指標(biāo)不會(huì)進(jìn)行新增，與上一步刪除的邏輯相悖。

3.2.1 復(fù)習(xí)Mysql的事務(wù)

3.2.1.1 ACID 不是 "四個(gè)獨(dú)立特性"，而是 "因果鏈"

?一句話：ACID 的核心是一致性，其他三個(gè)特性都是為了實(shí)現(xiàn)它的手段。

?一致性（Consistency）：一致性確保事務(wù)將數(shù)據(jù)庫(kù)從一個(gè)一致的狀態(tài)轉(zhuǎn)變到另一個(gè)一致的狀態(tài)。即使在多個(gè)事務(wù)同時(shí)執(zhí)行的情況下，數(shù)據(jù)庫(kù)也能保持?jǐn)?shù)據(jù)的一致性。

?原子性（Atomicity）：事務(wù)是 "不可分割的工作單元"（要么全成，要么全敗），是一致性的前提（如果步驟能拆分，中間失敗就會(huì)破壞一致性）。

?隔離性（Isolation）：通過(guò)控制多事務(wù)并發(fā)規(guī)則，避免互相干擾，是一致性的保障（并發(fā)混亂會(huì)直接破壞一致性）。

?耐久性（Durability）：事務(wù)提交后結(jié)果永久保存，是一致性的最終落點(diǎn)（否則重啟后數(shù)據(jù)丟失，之前的一致性白搭）。

3.2.1.2 隔離級(jí)別：不是 "越嚴(yán)越好"，而是 "成本與需求的平衡術(shù)"

InnoDB 的 4 種隔離級(jí)別，本質(zhì)是用 "數(shù)據(jù)可見性" 換 "并發(fā)性能"的選擇：

隔離級(jí)別	解決的問題	無(wú)法解決的問題	性能消耗	典型場(chǎng)景
讀未提交（RU）	無(wú)	臟讀、不可重復(fù)讀、幻讀	極低	實(shí)時(shí)監(jiān)控（允許臟數(shù)據(jù)）
讀已提交（RC）	臟讀	不可重復(fù)讀、幻讀	低	互聯(lián)網(wǎng)普通業(yè)務(wù)
可重復(fù)讀（RR，默認(rèn)）	臟讀、不可重復(fù)讀	幻讀（被 Next-Key 鎖解決）	中	金融交易、庫(kù)存管理
串行化（Serializable）	所有并發(fā)問題	無(wú)	極高	銀行對(duì)賬（無(wú)并發(fā)需求）

3.2.1.3 MVCC：事務(wù)的 "平行宇宙" 機(jī)制（為什么讀寫不沖突？）

InnoDB 的多版本并發(fā)控制是 "無(wú)鎖讀" 的核心，它讓讀和寫像在平行宇宙中運(yùn)行：

底層邏輯（用 "時(shí)間戳" 理解）：

?每個(gè)事務(wù)啟動(dòng)時(shí)，會(huì)拿到一個(gè)全局遞增的事務(wù) ID（trx_id）。

?每行數(shù)據(jù)隱藏 3 個(gè)字段：

?DB_TRX_ID：最后修改該行的事務(wù) ID；

?DB_ROLL_PTR：指向 undo 日志的指針（存儲(chǔ)歷史版本）；

?DB_DELETED：標(biāo)記是否刪除（邏輯刪除）。

讀操作的 "幻術(shù)"：

?快照讀（普通 SELECT）：只看 "事務(wù) ID ≤ 自己 ID" 且 "未被刪除" 的版本，完全不加鎖。例：事務(wù) A（ID=100）查詢時(shí)，會(huì)忽略所有被 ID>100 的事務(wù)修改的數(shù)據(jù)。

包含 4 個(gè)核心字段：

?m_ids：生成 Read View 時(shí)，當(dāng)前活躍的事務(wù) ID 列表（未提交的事務(wù)）。

?min_trx_id：m_ids中最小的事務(wù) ID。

?max_trx_id：下一個(gè)將要分配的事務(wù) ID（非活躍事務(wù) ID，僅用于判斷 “未來(lái)事務(wù)”）。

?creator_trx_id：生成該 Read View 的事務(wù)自身 ID。

可見性判斷規(guī)則（一條記錄是否對(duì)當(dāng)前事務(wù)可見，取決于其 “最后修改事務(wù) ID”，記為db_trx_id）：

1.若db_trx_id == creator_trx_id：可見（自己修改的自己可見）。

2.若db_trx_id < min_trx_id：可見（修改記錄的事務(wù)在當(dāng)前快照生成前已提交）。

3.若db_trx_id >= max_trx_id：不可見（修改記錄的事務(wù)在當(dāng)前快照生成后才啟動(dòng)）。

4.若min_trx_id ≤ db_trx_id < max_trx_id：

?若db_trx_id在m_ids中：不可見（該事務(wù)仍活躍，未提交）。

?若db_trx_id不在m_ids中：可見（該事務(wù)已提交）。

5.當(dāng)前讀（加鎖讀 / 寫操作）：讀取最新版本，并加鎖防止其他事務(wù)修改。

3.2.1.4 事務(wù)日志：InnoDB 的 "安全與性能" 平衡術(shù)

事務(wù)能既保證 durability 又不慢，全靠?jī)纱笕罩荆?/p>

1.redo log（重做日志）：

?作用：崩潰后恢復(fù)未寫入磁盤的數(shù)據(jù)（保證 durability）。

?反直覺：事務(wù)提交時(shí)，數(shù)據(jù)先寫 redo log（內(nèi)存 + 磁盤），再異步刷到數(shù)據(jù)文件（這叫 WAL 技術(shù)）。

?為什么快？redo log 是順序?qū)?/strong>（磁盤順序?qū)懕入S機(jī)寫快 100 倍 +）。

2.undo log（回滾日志）：

?作用：保存數(shù)據(jù)修改前的版本，用于事務(wù)回滾（保證 atomicity）和 MVCC 快照讀。

?注意：undo log 會(huì)被 purge 線程定期清理（當(dāng)沒有事務(wù)需要舊版本時(shí)）。

3.2.1.5 終極心法：事務(wù)設(shè)計(jì)的 "3 個(gè)凡是"

1.凡是不需要事務(wù)的操作，堅(jiān)決不用（如日志插入可關(guān)閉自動(dòng)提交，批量提交）。

2.凡是能在 RC 解決的，絕不升 RR（互聯(lián)網(wǎng)業(yè)務(wù)優(yōu)先選 RC，用業(yè)務(wù)邏輯防不可重復(fù)讀）。

3.凡是大事務(wù)，必拆分成 "讀 - 算 - 寫" 三步（讀階段不加鎖，算階段在應(yīng)用層，寫階段用最短事務(wù)加鎖）。

記住：事務(wù)的本質(zhì)不是 "約束"，而是 "工具"—— 能解決問題的最簡(jiǎn)單事務(wù)，才是最好的事務(wù)。

?

3.2.2 理論應(yīng)用實(shí)踐

3.2.2.1 本次事故的物料：

表的事務(wù)等級(jí)：

SELECT @@transaction_isolation;

?

需要?jiǎng)h除的指標(biāo)實(shí)現(xiàn)(根據(jù)實(shí)現(xiàn)id):

delete from unify_metric_impl where logic_table_id in (45631);

需要插入的指標(biāo)實(shí)現(xiàn)：

INSERT INTO `unify_metric_impl` (`id`, `metric_def_id`, `logic_table_id`, `name_en_atomic`, `committer`, `create_time`, `update_time`, `metric_atomic_name_temp`, `decorate_id_list_temp`, `name_cn_alias_temp`, `metric_type_temp`, `description_temp`, `data_type_temp`, `data_accuracy_temp`, `security_level_temp`, `logic_table_id_excel_temp`, `implement_type`, `app_ori_metric_name_temp`, `name_en_depend_atomic`, `name_en_depend_app`, `update_status`, `status`, `light_decorate_id_list`, `extend_decorate_id_list`, `extend_function_id_list`, `aggregation_type`, `middle_aggregation_type`, `static_decorate_id_list_combination`) VALUES (1358195, 19872, 45631, 'jdr_7fresh_trade_complete_ord_user_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358196, 19873, 45631, 'jdr_7fresh_trade_complete_ord_sku_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358197, 19875, 45631, 'jdr_7fresh_trade_complete_ord_ord_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358198, 19945, 45631, 'jdr_7fresh_sku_deal_ord_sku_dis_qtty_main_img_video_num', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358199, 17263, 45631, 'jdr_7fresh_trade_deal_ord_ord_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358200, 28017, 45631, 'jdr_7fresh_bd_bd_attendance_offline__store_cnt_bd_attendance_cnt', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'COUNT_DISTINCT', 'DEFAULT', ''), (1358201, 20242, 45631, 'jdr_7fresh_trade_deal_ord_app__ord_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358202, 18450, 45631, 'jdr_7fresh_trade_deal_ord_ord_amt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358203, 20276, 45631, 'jdr_7fresh_trade_deal_ord_app__ord_cnt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358204, 18452, 45631, 'jdr_7fresh_trade_deal_ord_sku_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358205, 18453, 45631, 'jdr_7fresh_trade_deal_ord_user_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358206, 18456, 45631, 'jdr_7fresh_trade_deal_ord_sku_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358207, 19866, 45631, 'jdr_7fresh_trade_complete_ord_ord_amt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358208, 21691, 45631, 'jdr_7fresh_trade_deal_ord_ord_amt_include_moutai', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (1358209, 19871, 45631, 'jdr_7fresh_trade_complete_ord_sku_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', '');

3.2.2.2 實(shí)踐分析：

用sql模擬兩個(gè)事務(wù)的執(zhí)行過(guò)程：

事務(wù)1：

begin; select * from unify_metric_impl umi where logic_table_id = 45631; delete from unify_metric_impl where logic_table_id in (45631); SELECT trx_id, trx_query FROM INFORMATION_SCHEMA.INNODB_TRX; select * from unify_metric_impl umi where logic_table_id = 45631; INSERT INTO `unify_metric_impl` ( `metric_def_id`, `logic_table_id`, `name_en_atomic`, `committer`, `create_time`, `update_time`, `metric_atomic_name_temp`, `decorate_id_list_temp`, `name_cn_alias_temp`, `metric_type_temp`, `description_temp`, `data_type_temp`, `data_accuracy_temp`, `security_level_temp`, `logic_table_id_excel_temp`, `implement_type`, `app_ori_metric_name_temp`, `name_en_depend_atomic`, `name_en_depend_app`, `update_status`, `status`, `light_decorate_id_list`, `extend_decorate_id_list`, `extend_function_id_list`, `aggregation_type`, `middle_aggregation_type`, `static_decorate_id_list_combination`) VALUES (19872, 45631, 'jdr_7fresh_trade_complete_ord_user_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (19873, 45631, 'jdr_7fresh_trade_complete_ord_sku_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (19875, 45631, 'jdr_7fresh_trade_complete_ord_ord_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (19945, 45631, 'jdr_7fresh_sku_deal_ord_sku_dis_qtty_main_img_video_num', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (17263, 45631, 'jdr_7fresh_trade_deal_ord_ord_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (28017, 45631, 'jdr_7fresh_bd_bd_attendance_offline__store_cnt_bd_attendance_cnt', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'COUNT_DISTINCT', 'DEFAULT', ''), (20242, 45631, 'jdr_7fresh_trade_deal_ord_app__ord_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (18450, 45631, 'jdr_7fresh_trade_deal_ord_ord_amt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (20276, 45631, 'jdr_7fresh_trade_deal_ord_app__ord_cnt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (18452, 45631, 'jdr_7fresh_trade_deal_ord_sku_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (18453, 45631, 'jdr_7fresh_trade_deal_ord_user_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (18456, 45631, 'jdr_7fresh_trade_deal_ord_sku_dis_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (19866, 45631, 'jdr_7fresh_trade_complete_ord_ord_amt_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (21691, 45631, 'jdr_7fresh_trade_deal_ord_ord_amt_include_moutai', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''), (19871, 45631, 'jdr_7fresh_trade_complete_ord_sku_qtty_deal_7fresh', '', '2025-09-02 09:23:41', '2025-09-02 09:23:41', '', '', '', '', '', '', 2, '-1', '-1', '', '', '', '', 0, 0, '', '4556,8012,8270,7247,7248,7249,7250,2134,7254,7255,5997,7867', '25,6,5,7,23,22,20,21,19,18,4,1,2,3,24', 'toSql', 'DEFAULT', ''); commit;

?

事務(wù)2：

begin; select * from unify_metric_impl umi where logic_table_id = 45631; delete from unify_metric_impl where logic_table_id in (45631); select * from unify_metric_impl umi where logic_table_id = 45631; commit;

?

流程圖(用一行數(shù)據(jù)進(jìn)行演示版本控制)：

?

為何事務(wù)1的select查詢出“為空”，事務(wù)2的select查詢出“不為空”：

對(duì)比維度事務(wù) 1 查詢（讀自己的刪除版本 V2）事務(wù) 2 查詢（讀readview前的V1版本）
自己生成的版本 V2（trx_id=17190，已刪除） V4（trx_id=17191，已刪除）
對(duì)自己版本的處理可見，且事務(wù)內(nèi)需反映自己的刪除操作，所以不追溯前驅(qū) V1 不可見，但當(dāng)前刪除的版本是由其他事務(wù)得到(V3)，并非在readview之前的數(shù)據(jù)。
追溯的終止條件遇到自己生成的版本，即使已刪除，也終止追溯遇到自己生成的已刪除版本，但不符合"有效刪除"，需繼續(xù)追溯
最終返回結(jié)果 v2（已刪除版本，反映自己的刪除操作） V1（readview之前有效的版本）
?

4.解決辦法

為了解決事務(wù)2的查詢"不為空"的問題，分別列出以下方案：

?
解決辦法優(yōu)點(diǎn) 缺點(diǎn) 傾向
方式1 針對(duì)同一個(gè)邏輯表的同步添加分布式鎖實(shí)現(xiàn)成本低，影響范圍小存在長(zhǎng)事務(wù)的問題短期解法
方式2 將事務(wù)2的select改為當(dāng)前讀(使用slecet...for update)，這樣就能查詢出最新的數(shù)據(jù)為空實(shí)現(xiàn)成本低，存在長(zhǎng)事務(wù)的問題，影響范圍大(長(zhǎng)事務(wù)涉及邏輯多) 不推薦
方式3 將長(zhǎng)事務(wù)拆分， "讀 - 算 - 寫" 三步 1. 讀：無(wú)鎖讀取原子服務(wù)與實(shí)現(xiàn)數(shù)據(jù)； 2. 算：在應(yīng)用層對(duì)比新增 / 刪除數(shù)據(jù)； 3. 寫：僅對(duì)差異數(shù)據(jù)執(zhí)行短事務(wù)操作從根源解決問題實(shí)現(xiàn)成本大，重構(gòu)該方法長(zhǎng)期解法
當(dāng)前落地情況：已通過(guò) “分布式鎖控制同一邏輯表同步并發(fā)” 的短期方案解決事故，后續(xù)將在業(yè)務(wù)迭代中推進(jìn) “讀 - 算 - 寫” 拆分的長(zhǎng)期優(yōu)化，進(jìn)一步降低事務(wù)粒度與鎖沖突風(fēng)險(xiǎn)。

5.附錄

5.1名詞解釋

事實(shí)邏輯表：由物理數(shù)倉(cāng)中的事實(shí)表和維度邏輯表關(guān)聯(lián)形成的語(yǔ)義表，可以描述業(yè)務(wù)過(guò)程的詳細(xì)信息，是指標(biāo)的數(shù)據(jù)來(lái)源。

原子服務(wù)：指標(biāo)的實(shí)現(xiàn)方式，一個(gè)指標(biāo)可以有多個(gè)實(shí)現(xiàn)。

?
審核編輯黃宇

聲明：本文內(nèi)容及配圖由入駐作者撰寫或者入駐合作網(wǎng)站授權(quán)轉(zhuǎn)載。文章觀點(diǎn)僅代表作者本人，不代表電子發(fā)燒友網(wǎng)立場(chǎng)。文章及其配圖僅供工程師學(xué)習(xí)之用，如有內(nèi)容侵權(quán)或者其他違規(guī)問題，請(qǐng)聯(lián)系本站處理。舉報(bào)投訴

MySQL

MySQL

+關(guān)注

關(guān)注
1

文章
905

瀏覽量
29518

收藏人收藏

掃一掃，分享給好友

復(fù)制鏈接分享

加入交流群

掃碼添加小助手

加入工程師交流群

評(píng)論

發(fā)布評(píng)論請(qǐng)先登錄

相關(guān)推薦

熱點(diǎn)推薦

TPS84610：高效集成電源解決方案的深度剖析

TPS84610：高效集成電源解決方案的深度剖析在電子設(shè)計(jì)領(lǐng)域，一款性能卓越、功能豐富的電源解決方案至關(guān)重要。TPS84610作為一款備受關(guān)注的產(chǎn)品，為工程師們提供了出色的選擇。本文

發(fā)表于 03-04 15:20 ?44次閱讀

TPS84210：高效集成電源解決方案的深度剖析

TPS84210：高效集成電源解決方案的深度剖析在電子設(shè)計(jì)領(lǐng)域，電源管理芯片的性能和特性對(duì)整個(gè)系統(tǒng)的穩(wěn)定性和效率起著至關(guān)重要的作用。今天，我們將深入探討德州儀器（TI）的TPS84210，一款

發(fā)表于 03-04 15:20 ?41次閱讀

從踩坑到高效落地：關(guān)鍵詞搜索淘寶天貓商品列表 API 的實(shí)操心得

API」關(guān)鍵詞搜索是電商數(shù)據(jù)業(yè)務(wù)最常用、最容易翻車、最影響體驗(yàn)的接口： ? 搜不到結(jié)果 ? 翻幾頁(yè)就斷 ? 排序不準(zhǔn)、價(jià)格假 ? 封號(hào)、限流、字段亂變 ? 并發(fā)一高直接崩從踩坑到

發(fā)表于 02-28 14:22 ?1364次閱讀

MySQL事務(wù)與鎖機(jī)制詳解

在我擔(dān)任某互聯(lián)網(wǎng)金融平臺(tái)SRE期間，曾遇到過(guò)一次嚴(yán)重的線上事故：凌晨3點(diǎn)，監(jiān)控系統(tǒng)瘋狂告警，數(shù)據(jù)庫(kù)活躍連接數(shù)從平時(shí)的200飆升到2000，大量請(qǐng)求超時(shí)。緊急排查后發(fā)現(xiàn)，一個(gè)批量更新任務(wù)與在線交易產(chǎn)生了死鎖，導(dǎo)致數(shù)據(jù)庫(kù)連接被占滿。

發(fā)表于 01-27 10:33 ?257次閱讀

智能工廠改造踩坑？有人物聯(lián)網(wǎng)手把手教你挑對(duì)系統(tǒng)

中小工廠砸?guī)资f(wàn)搞智能改造，結(jié)果踩了最冤的坑：花 “智慧工廠系統(tǒng)” 的錢，買的只是個(gè) “升級(jí)款工廠監(jiān)控系統(tǒng)”—— 只能看設(shè)備轉(zhuǎn)不轉(zhuǎn)，沒法調(diào)生產(chǎn)、降能耗，錢直接打了水漂。智能改造選系統(tǒng)，真的像開盲盒

發(fā)表于 12-19 14:20 ?260次閱讀

Amphenol FlexTraX：創(chuàng)新電纜管理解決方案深度剖析

Amphenol FlexTraX：創(chuàng)新電纜管理解決方案深度剖析在電子設(shè)備和網(wǎng)絡(luò)系統(tǒng)中，電纜管理一直是一個(gè)關(guān)鍵且具有挑戰(zhàn)性的任務(wù)。合理的電纜管理不僅能提高系統(tǒng)的可靠性和可維護(hù)性，還能提升整體的美觀

發(fā)表于 12-11 14:50 ?391次閱讀

STM32 5 個(gè)容易踩坑的外設(shè)使用技巧

STM32是嵌入式開發(fā)領(lǐng)域的熱門MCU，功能豐富到幾乎可以完成所有常見控制任務(wù)：GPIO、ADC、UART、定時(shí)器、DMA……應(yīng)有盡有。但是，正因?yàn)楣δ軓?qiáng)大，開發(fā)中踩坑的機(jī)會(huì)也隨之增多。很多初學(xué)者

發(fā)表于 11-24 19:04 ?822次閱讀

別踩分頁(yè)坑！京東商品詳情接口實(shí)戰(zhàn)指南：從并發(fā)優(yōu)化到數(shù)據(jù)完整性閉環(huán)

京東商品詳情接口（jingdong.ware.get）是電商數(shù)據(jù)開發(fā)的核心難點(diǎn)，本文詳解其權(quán)限申請(qǐng)、分頁(yè)優(yōu)化、多規(guī)格遞歸解析與完整性校驗(yàn)等實(shí)戰(zhàn)方案，結(jié)合代碼示例與性能調(diào)優(yōu)參數(shù)，助你高效穩(wěn)定對(duì)接，提升

發(fā)表于 09-30 15:50 ?1097次閱讀

別再卡分頁(yè)！淘寶全量商品接口實(shí)戰(zhàn)開發(fā)指南：從并發(fā)優(yōu)化到數(shù)據(jù)完整性閉環(huán)

淘寶店鋪全量商品接口實(shí)戰(zhàn)指南：詳解權(quán)限申請(qǐng)、分頁(yè)優(yōu)化、并發(fā)拉取與增量更新，結(jié)合代碼實(shí)現(xiàn)高效穩(wěn)定的數(shù)據(jù)獲取，解決超時(shí)、限流、數(shù)據(jù)丟失等核心難題，助力電商

發(fā)表于 09-30 10:47 ?568次閱讀

京東商品 SKU 信息接口技術(shù)干貨：數(shù)據(jù)拉取、規(guī)格解析與字段治理（附踩坑總結(jié) + 可運(yùn)行代碼

本文詳解京東商品SKU接口對(duì)接技術(shù)，涵蓋核心參數(shù)、權(quán)限申請(qǐng)、簽名生成、規(guī)格解析及常見坑點(diǎn)解決方案，結(jié)合可運(yùn)行代碼與實(shí)戰(zhàn)經(jīng)驗(yàn)，助力開發(fā)者高效集成SKU數(shù)據(jù)，實(shí)現(xiàn)庫(kù)存、價(jià)格等關(guān)鍵信息精準(zhǔn)獲

發(fā)表于 09-29 11:56 ?567次閱讀

UWB自動(dòng)跟隨技術(shù)原理、算法融合優(yōu)化和踩坑實(shí)錄

UWB為什么是最靠譜的自動(dòng)跟隨技術(shù)？原理是什么？需要做什么算法融合、優(yōu)化？我們?cè)陂_發(fā)過(guò)程中踩過(guò)的坑。

發(fā)表于 08-14 17:45 ?1542次閱讀

企業(yè)級(jí)MySQL數(shù)據(jù)庫(kù)管理指南

在當(dāng)今數(shù)字化時(shí)代，MySQL作為全球最受歡迎的開源關(guān)系型數(shù)據(jù)庫(kù)，承載著企業(yè)核心業(yè)務(wù)數(shù)據(jù)的存儲(chǔ)與處理。作為數(shù)據(jù)庫(kù)管理員（DBA），掌握MySQL

發(fā)表于 07-09 09:50 ?717次閱讀

鴻蒙5開發(fā)寶藏案例分享---應(yīng)用并發(fā)設(shè)計(jì)

?** 鴻蒙并發(fā)編程實(shí)戰(zhàn)指南：解鎖ArkTS多線程黑科技** 嘿，開發(fā)者朋友們！今天給大家扒一扒鴻蒙官方文檔里藏著的并發(fā)編程寶藏—— 100+實(shí)戰(zhàn)場(chǎng)景

發(fā)表于 06-12 16:19

HarmonyOS實(shí)戰(zhàn)：一招解決等待多個(gè)并發(fā)結(jié)果

講解開發(fā)過(guò)程中遇到的并發(fā)問題，官方API 11文檔寫的太簡(jiǎn)單了，根本沒有解決方案，小編也是苦思冥想，絞盡腦汁才找到解決方案。需要開發(fā)鴻蒙的小伙伴可以仔細(xì)閱讀，避免踩

發(fā)表于 06-09 14:57 ?620次閱讀

嵌入式開發(fā)避坑指南｜FreeRTOS的5個(gè)\"反直覺\"小技巧

解決方案 任務(wù)運(yùn)行時(shí)好時(shí)壞堆棧碎片啟用configCHECK_FOR_STACK_OVERFLOW=2 系統(tǒng)頻繁復(fù)位棧底越界使用uxTaskGetStackHighWaterMark()監(jiān)控 數(shù)據(jù)

發(fā)表于 03-20 13:57

搜索歷史

并發(fā)丟數(shù)據(jù)深度剖析：MySQL鎖機(jī)制與事務(wù)實(shí)戰(zhàn)踩坑及解決方案

評(píng)論

對(duì)比維度	事務(wù) 1 查詢（讀自己的刪除版本 V2）	事務(wù) 2 查詢（讀readview前的V1版本）
自己生成的版本	V2（trx_id=17190，已刪除）	V4（trx_id=17191，已刪除）
對(duì)自己版本的處理	可見，且事務(wù)內(nèi)需反映自己的刪除操作，所以不追溯前驅(qū) V1	不可見，但當(dāng)前刪除的版本是由其他事務(wù)得到(V3)，并非在readview之前的數(shù)據(jù)。
追溯的終止條件	遇到自己生成的版本，即使已刪除，也終止追溯	遇到自己生成的已刪除版本，但不符合"有效刪除"，需繼續(xù)追溯
最終返回結(jié)果	v2（已刪除版本，反映自己的刪除操作）	V1（readview之前有效的版本）

?	解決辦法	優(yōu)點(diǎn)	缺點(diǎn)	傾向
方式1	針對(duì)同一個(gè)邏輯表的同步添加分布式鎖	實(shí)現(xiàn)成本低，影響范圍小	存在長(zhǎng)事務(wù)的問題	短期解法
方式2	將事務(wù)2的select改為當(dāng)前讀(使用slecet...for update)，這樣就能查詢出最新的數(shù)據(jù)為空	實(shí)現(xiàn)成本低，	存在長(zhǎng)事務(wù)的問題，影響范圍大(長(zhǎng)事務(wù)涉及邏輯多)	不推薦
方式3	將長(zhǎng)事務(wù)拆分， "讀 - 算 - 寫" 三步 1. 讀：無(wú)鎖讀取原子服務(wù)與實(shí)現(xiàn)數(shù)據(jù)； 2. 算：在應(yīng)用層對(duì)比新增 / 刪除數(shù)據(jù)； 3. 寫：僅對(duì)差異數(shù)據(jù)執(zhí)行短事務(wù)操作	從根源解決問題	實(shí)現(xiàn)成本大，重構(gòu)該方法	長(zhǎng)期解法