博客 / 詳情

返回

併發丟數據深度剖析：MySQL鎖機制與事務實戰踩坑及解決方案

1、理論來源於實踐

現象：於2025-08-13 21:45:35，事實邏輯表將自身的指標與維度同步到原子服務的實現時，出現同步過來的指標與維度丟失。

核心原因：兩次重複的事實邏輯表同步時間非常相近，導致同步過來的指標與維度丟失。
在這裏插入圖片描述

2、倒帶進事故現場

邏輯表向原子服務同步的核心邏輯是 “先刪後增”：刪除舊數據→對比新老數據→插入新增數據，具體流程如下：

在這裏插入圖片描述

整體業務代碼精簡邏輯如下：

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

}

用一個請求進行舉例：

{"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":"七鮮實時交易_for地推中間態新老標誌","atomicAliasProd":"prod","implServiceTypeKey":"realtime","originPhysicDataSourceId":0,"nameCn":"七鮮實時交易_for地推中間態新老標誌","description":"七鮮實時交易_for地推中間態新老標誌","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"}

共計15個指標，64個維度

| 請求1(事務) | 請求2(事務) |  | | |
| ------------ | ------------------------------------------------------------------------- | ------------ | ------------------------------------------------------------------------------- | - |
| 21:06:17.262 | 進入同步方法 | 21:06:17.263 | 進入同步方法 |  |
| 21:06:17.063 | select unify\_metric\_impl where logic\_id = 3245 查詢出15條數據(快照讀，readview1) | 21:06:17.363 | select unify\_metric\_impl where logic\_id = 3245 查詢出15條數據(快照讀，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條數據 |  | delete 由於logic\_id不是索引，會表鎖阻塞 |  |
| 21:06:18.459 | insert into unify\_metric\_impl 插入的logic\_id = 3245的數據，15條 |  |  |  |
| 21:06:19.408 | 方法結束 |  |  |  |
|  |  | 21:06:19.529 | 刪除成功 |  |
|  |  | 21:06:20.362 | select unify\_metric\_impl where logic\_id = 3245 得到 15條數據 |  |
|  |  | 21:06:20.435 | 讀出15條數據，比較本次是否有新增指標， 得出沒有新增指標，因此不進行新增。addAtomicMetricNameForDrive addList empty |  |
|  |  | 21:06:21.435 | 方法結束 |  |



核心結論點：

1.請求2的刪除操作被阻塞了，直到請求1執行完整個方法。

2.請求2中去查看當前實現的指標的時候，發現庫裏已經存在所有指標不會進行新增，與上一步刪除的邏輯相悖。



3、結論點深度剖析

3.1 分析結論一

請求2的刪除操作被阻塞了，直到請求1執行完整個方法。

3.1.1 複習mysql的InnoDB鎖機制

3.1.1.1 不是“一把鎖”，而是 “鎖矩陣”
鎖粒度 共享鎖（S 鎖） （讀鎖，允許多讀） 排他鎖（X 鎖） （寫鎖，獨佔） 意向鎖（表級，輔助判斷）
表級 表 S 鎖（極少用，如LOCK TABLES ... READ） 表 X 鎖（極少用，如LOCK TABLES ... WRITE） 意向 S 鎖（IS）、意向 X 鎖（IX）
行級 行 S 鎖（SELECT ... FOR SHARE） 行 X 鎖（UPDATE/DELETE/INSERT默認加） （行鎖無需意向鎖）
間隙級 間隙 S 鎖（無，間隙只防插入） 間隙 X 鎖（防其他事務插入相同間隙） 無
Next-Key 無 Next-Key 鎖（行鎖 + 間隙鎖，默認行鎖算法） 無



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

在這裏插入圖片描述



3.1.2 理論應用實踐

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 '指標定義id',
  `logic_table_id` bigint(11) unsigned NOT NULL DEFAULT '0' COMMENT '邏輯表id',
  `name_en_atomic` varchar(256) NOT NULL DEFAULT '' COMMENT '真實指標名',
  `committer` varchar(64) NOT NULL DEFAULT '' COMMENT '負責人',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '開始時間',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
  `metric_atomic_name_temp` varchar(32) NOT NULL DEFAULT '' COMMENT '原子指標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 '指標類型：DERIVE 衍生指標，FORMULA 複合指標',
  `description_temp` varchar(64) NOT NULL DEFAULT '' COMMENT '指標説明',
  `data_type_temp` varchar(16) NOT NULL DEFAULT '' COMMENT '數據類型：STRING，DOUBLE, LONG, INT',
  `data_accuracy_temp` tinyint(4) NOT NULL DEFAULT '2' COMMENT '數據精度-小數點後幾位',
  `security_level_temp` varchar(16) DEFAULT '-1' COMMENT '安全等級',
  `logic_table_id_excel_temp` varchar(16) DEFAULT '-1' COMMENT '模型excelId',
  `implement_type` varchar(32) NOT NULL DEFAULT '' COMMENT '指標實現類型：APP、ATOMIC 原子服務',
  `app_ori_metric_name_temp` varchar(16) DEFAULT '' COMMENT '所依賴的app層原始名字（適用於導數任務改變字段的情況）',
  `name_en_depend_atomic` varchar(256) NOT NULL DEFAULT '' COMMENT '指標依賴字段',
  `name_en_depend_app` varchar(16) DEFAULT '' COMMENT '所依賴的app層原始名字（適用於導數任務改變字段的情況）',
  `update_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '信息更新狀態 0-未完成更新，1-完成更新',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT '信息更新狀態 0-未完成更新，1-完成更新',
  `light_decorate_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '點燈修飾id列表',
  `extend_decorate_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '支持的動態修飾id列表',
  `extend_function_id_list` varchar(1024) NOT NULL DEFAULT '' COMMENT '支持的原子服務函數id列表',
  `aggregation_type` varchar(64) NOT NULL DEFAULT '' COMMENT '聚合類型:ORIGINAL 原值 COUNT 計數 DISTINCT 指定字段去重 SUM 求和 AVG  均值 MIN 求最大值 MAX 求最小值 QUANTITLE 求分位數',
  `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='指標實現';
3.1.2.2 實踐分析

通過mysql的innoDB的鎖決策，可以得出

//刪除關聯指標
 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鎖。

在這裏插入圖片描述

因此請求2的刪除操作需要等待請求1的事務釋放表鎖後才可繼續進行，符合當時場景。



3.2 分析結論二

請求2中去查看當前實現的指標的時候，發現庫裏已經存在所有指標不會進行新增，與上一步刪除的邏輯相悖。

3.2.1 複習Mysql的事務

3.2.1.1 ACID 不是 "四個獨立特性"，而是 "因果鏈"

在這裏插入圖片描述

•一句話：ACID 的核心是一致性，其他三個特性都是為了實現它的手段。

•一致性（Consistency）：一致性確保事務將數據庫從一個一致的狀態轉變到另一個一致的狀態。即使在多個事務同時執行的情況下，數據庫也能保持數據的一致性。

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

•隔離性（Isolation）：通過控制多事務併發規則，避免互相干擾，是一致性的保障（併發混亂會直接破壞一致性）。

•耐久性（Durability）：事務提交後結果永久保存，是一致性的最終落點（否則重啓後數據丟失，之前的一致性白搭）。



3.2.1.2 隔離級別：不是 "越嚴越好"，而是 "成本與需求的平衡術"

InnoDB 的 4 種隔離級別，本質是用 "數據可見性" 換 "併發性能"的選擇：

隔離級別 解決的問題 無法解決的問題 性能消耗 典型場景
讀未提交（RU） 無 髒讀、不可重複讀、幻讀 極低 實時監控（允許髒數據）
讀已提交（RC） 髒讀 不可重複讀、幻讀 低 互聯網普通業務
可重複讀（RR，默認） 髒讀、不可重複讀 幻讀（被 Next-Key 鎖解決） 中 金融交易、庫存管理
串行化（Serializable） 所有併發問題 無 極高 銀行對賬（無併發需求）



3.2.1.3 MVCC：事務的 "平行宇宙" 機制（為什麼讀寫不衝突？）

InnoDB 的多版本併發控制是 "無鎖讀" 的核心，它讓讀和寫像在平行宇宙中運行：

底層邏輯（用 "時間戳" 理解）：

•每個事務啓動時，會拿到一個全局遞增的事務 ID（trx\_id） 。

•每行數據隱藏 3 個字段：

◦DB_TRX_ID：最後修改該行的事務 ID；

◦DB_ROLL_PTR：指向 undo 日誌的指針（存儲歷史版本）；

◦DB_DELETED：標記是否刪除（邏輯刪除）。

讀操作的 "幻術" ：

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

包含 4 個核心字段：

•m_ids：生成 Read View 時，當前活躍的事務 ID 列表（未提交的事務）。

•min_trx_id：m_ids中最小的事務 ID。

•max_trx_id：下一個將要分配的事務 ID（非活躍事務 ID，僅用於判斷 “未來事務”）。

•creator_trx_id：生成該 Read View 的事務自身 ID。

可見性判斷規則（一條記錄是否對當前事務可見，取決於其 “最後修改事務 ID”，記為db_trx_id）：

1.若db\_trx\_id == creator\_trx\_id：可見（自己修改的自己可見）。

2.若db\_trx\_id < min\_trx\_id：可見（修改記錄的事務在當前快照生成前已提交）。

3.若db\_trx\_id >= max\_trx\_id：不可見（修改記錄的事務在當前快照生成後才啓動）。

4.若min\_trx\_id ≤ db\_trx\_id < max\_trx\_id：

◦若db\_trx\_id在m\_ids中：不可見（該事務仍活躍，未提交）。

◦若db\_trx\_id不在m\_ids中：可見（該事務已提交）。

5.當前讀（加鎖讀 / 寫操作）：讀取最新版本，並加鎖防止其他事務修改。

3.2.1.4 事務日誌：InnoDB 的 "安全與性能" 平衡術

事務能既保證 durability 又不慢，全靠兩大日誌：

1.redo log（重做日誌） ：

◦作用：崩潰後恢復未寫入磁盤的數據（保證 durability）。

◦反直覺：事務提交時，數據先寫 redo log（內存 + 磁盤），再異步刷到數據文件（這叫 WAL 技術）。

◦為什麼快？redo log 是順序寫（磁盤順序寫比隨機寫快 100 倍 +）。

2.undo log（回滾日誌） ：

◦作用：保存數據修改前的版本，用於事務回滾（保證 atomicity）和 MVCC 快照讀。

◦注意：undo log 會被 purge 線程定期清理（當沒有事務需要舊版本時）。

3.2.1.5 終極心法：事務設計的 "3 個凡是"

1.凡是不需要事務的操作，堅決不用（如日誌插入可關閉自動提交，批量提交）。

2.凡是能在 RC 解決的，絕不升 RR（互聯網業務優先選 RC，用業務邏輯防不可重複讀）。

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

記住：事務的本質不是 "約束"，而是 "工具"—— 能解決問題的最簡單事務，才是最好的事務。



3.2.2 理論應用實踐

3.2.2.1 本次事故的物料：

表的事務等級：

SELECT @@transaction_isolation;



在這裏插入圖片描述





需要刪除的指標實現(根據實現id):

delete from unify_metric_impl where logic_table_id in (45631);

需要插入的指標實現：

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 實踐分析：

用sql模擬兩個事務的執行過程：

事務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;



事務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;



流程圖(用一行數據進行演示版本控制)：

在這裏插入圖片描述



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

對比維度 事務 1 查詢（讀自己的刪除版本 V2） 事務 2 查詢（讀readview前的V1版本）
自己生成的版本 V2（trx\_id=17190，已刪除） V4（trx\_id=17191，已刪除）
對自己版本的處理 可見，且事務內需反映自己的刪除操作，所以不追溯前驅 V1 不可見，但當前刪除的版本是由其他事務得到(V3) ，並非在readview之前的數據。
追溯的終止條件 遇到自己生成的版本，即使已刪除，也終止追溯 遇到自己生成的已刪除版本，但不符合"有效刪除"，需繼續追溯
最終返回結果 v2（已刪除版本，反映自己的刪除操作） V1（readview之前有效的版本）



4.解決辦法

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

 解決辦法 優點 缺點 傾向
方式1 針對同一個邏輯表的同步添加分佈式鎖 實現成本低，影響範圍小 存在長事務的問題 短期解法
方式2 將事務2的select改為當前讀(使用slecet...for update)，這樣就能查詢出最新的數據為空 實現成本低， 存在長事務的問題，影響範圍大(長事務涉及邏輯多) 不推薦
方式3 將長事務拆分， "讀 - 算 - 寫" 三步 1. 讀：無鎖讀取原子服務與實現數據； 2. 算：在應用層對比新增 / 刪除數據； 3. 寫：僅對差異數據執行短事務操作 從根源解決問題 實現成本大，重構該方法 長期解法

當前落地情況：已通過 “分佈式鎖控制同一邏輯表同步併發” 的短期方案解決事故，後續將在業務迭代中推進 “讀 - 算 - 寫” 拆分的長期優化，進一步降低事務粒度與鎖衝突風險。

5.附錄

5.1名詞解釋

事實邏輯表：由物理數倉中的事實表和維度邏輯表關聯形成的語義表，可以描述業務過程的詳細信息，是指標的數據來源。

原子服務：指標的實現方式，一個指標可以有多個實現。

程序員
user avatar dalidezhuantou_bpc01t 頭像 fennudebiandang 頭像 ivictor 頭像 u_17518318 頭像 u_13303 頭像
5 位用戶收藏了這個故事！

發佈 評論

Some HTML is okay.