MsSql 其他（2）

Mysql中的MVCC

一、MVCC 的核心目标与设计背景

MVCC（Multi-Version Concurrency Control，多版本并发控制） 是 InnoDB 存储引擎为实现高并发事务处理而设计的核心机制。其核心目标是：在不牺牲事务隔离性的前提下，通过“读不阻塞写，写不阻塞读”的方式，大幅提升数据库的并发性能。

传统数据库的并发控制依赖锁机制（如读锁、写锁），但锁会导致读写冲突（例如，读操作需等待写锁释放，写操作需等待读锁释放）。MVCC 则通过“保存数据的历史版本”实现读操作的“非锁定读取”，避免了读写操作的直接竞争，从而显著提升并发效率。

二、InnoDB 实现 MVCC 的四大核心组件

1. 隐藏字段：版本追踪的“元数据”

InnoDB 为每行数据自动添加 3 个隐藏字段（物理存储于行记录头部），用于记录数据的版本信息和关联历史版本，是 MVCC 的基础元数据：

示例：
假设表 user 有列 id（主键）、name，初始插入事务 ID 为 100，则行记录的隐藏字段为：

• DB_TRX_ID=100（插入事务 ID）
• DB_ROLL_PTR=NULL（无旧版本）
• DB_ROW_ID=1（自动生成的主键 ID）

2. undo log：版本存储的“历史仓库”

undo log 是 InnoDB 存储数据历史版本的核心载体，用于：

• 回滚事务：当事务需要回滚时，通过 undo log 恢复数据到修改前的状态。
• 生成多版本：为读操作提供历史版本数据（非锁定读取）。

undo log 的关键特性：

• 存储位置：与 redo log 不同，undo log 直接存储在 InnoDB 的表空间（.ibd 文件）中，而非独立日志文件。
• 版本链结构：每条 undo log 记录包含前一条 undo log 的指针（通过 DB_ROLL_PTR 关联），形成从最新版本到最旧版本的链式结构。
• Purge 线程清理：InnoDB 后台运行的 Purge 线程会定期扫描并删除“不再需要的旧版本”（即早于当前最老活跃事务的版本），避免 undo log 无限增长。

undo log 的类型：

• Insert Undo Log：仅在 INSERT 操作时生成，记录新插入行的原始数据（实际不存在，因插入前无数据）。回滚时只需丢弃该记录，无需恢复数据。
• Update Undo Log：由 UPDATE 或 DELETE 操作生成，记录修改前的旧版本数据。需长期保留（直到被 Purge 线程清理），用于事务回滚或 MVCC 读。

3. 行更新流程：版本链的构建与原子性保证

当事务执行 UPDATE 操作时，InnoDB 通过以下步骤生成新版本并维护版本链，确保事务的原子性和一致性：

示例：版本链示例
假设初始行 A（事务 ID=100，DB_ROLL_PTR=NULL）→ 事务 200 修改后生成版本 B（DB_ROLL_PTR 指向 A）→ 事务 300 修改后生成版本 C（DB_ROLL_PTR 指向 B）。最终，版本链为 C → B → A，通过 DB_ROLL_PTR 可从 C 回溯到 B，再到 A。

4. 事务提交与回滚：版本链的管理

• 事务提交（COMMIT）：
  InnoDB 仅需将事务状态标记为“已提交”（存储于事务系统表中），无需立即刷盘数据或 undo log（依赖 redo log 的持久化机制）。提交后，该行版本对其他符合隔离级别的事务可见（取决于 MVCC 规则）。
• 事务回滚（ROLLBACK）：
  需根据当前行的 DB_ROLL_PTR 回溯版本链，从 undo log 中恢复旧版本数据。若事务影响多行，需逐行回溯并恢复，效率随回滚行数增加而降低（经验表明，1000-10000 行的回滚仍高效，超过此范围可能变慢）。

三、事务隔离级别与 MVCC 的读行为

InnoDB 通过 MVCC 实现不同隔离级别的读语义，核心逻辑是：读操作根据事务 ID 与当前行的 DB_TRX_ID 比较，决定是否读取旧版本。以下是各隔离级别的底层实现细节：

1. READ UNCOMMITTED（读未提交）

• 行为：读操作直接读取当前最新提交版本（不检查 DB_TRX_ID）。
• 问题：可能读到未提交的脏数据（因未加锁，其他事务的修改可能未提交）。
• 实现：InnoDB 未对读操作加任何锁，直接访问当前行的最新版本。

2. READ COMMITTED（读提交，默认级别之一）

• 行为：每次读操作时，检查当前行的DB_TRX_ID：
  • 若 DB_TRX_ID 属于已提交的事务（且小于当前事务 ID），则读取该版本；
  • 否则，通过 DB_ROLL_PTR 回溯到更早的版本，直到找到满足条件的版本。
• 特点：每次读可能得到不同结果（因其他事务可能提交新版本），但保证读到已提交的最新数据（无脏读）。
• 实现：读操作不锁定数据，但每次读都重新计算可见版本（依赖版本链回溯）。

3. REPEATABLE READ（可重复读，默认级别）

• 行为：事务启动时，记录当前系统的全局事务 ID（trx_id）。后续所有读操作仅读取 DB_TRX_ID 小于等于该启动 ID 的版本（即事务启动时的“快照”）。
• 特点：同一事务内多次读同一数据结果一致（无幻读），但可能读到旧数据（因未读取后续提交的新版本）。
• 实现：通过“事务快照”实现，读操作仅访问 DB_TRX_ID ≤ 事务启动 ID 的版本（无需回溯版本链，因版本链中的旧版本均满足条件）。

4. SERIALIZABLE（串行化）

• 行为：强制所有读操作加共享锁（S锁），写操作加排他锁（X锁），读写互斥。
• 特点：完全避免脏读、不可重复读、幻读，但并发性能极差（很少使用）。
• 实现：读操作通过 SELECT ... LOCK IN SHARE MODE 显式加 S锁，写操作加 X锁，依赖锁机制实现串行化。

四、InnoDB MVCC 的本质：“伪 MVCC”

传统 MVCC 的核心理念是“乐观锁+多版本共存”：通过版本号校验（读操作检查版本号，写操作仅当版本号匹配时提交），避免锁的使用，实现无锁并发。但 InnoDB 的实现与传统 MVCC 存在本质差异：

1. 写操作加锁

InnoDB 的写操作（UPDATE/DELETE/INSERT）必须加排他锁（X锁），阻止其他事务同时修改同一行。这与传统 MVCC 的“乐观锁”（无锁）理念相悖。

2. 版本链的串行化

undo log 中的版本链本质是事务提交的顺序记录（按事务 ID 递增排列），属于“串行化”的历史版本，而非真正的多版本并行。版本链的回溯仅用于读操作的非阻塞，而非事务间的版本并行。

3. 原子性依赖锁

InnoDB 通过排他锁保证事务的原子性（写互斥），若事务失败需回滚，需依赖 undo log 恢复数据。这与传统 MVCC 通过版本号校验实现原子性的方式不同。

结论：InnoDB 的 MVCC 更接近“通过 undo log 实现非锁定读”的优化手段，而非严格意义上的多版本并发控制。其核心价值在于：通过保留历史版本，让读操作无需等待写锁释放即可访问旧数据，从而提升并发性能。

五、MVCC 的局限性与适用场景

1. 局限性

• 多行事务的限制：若事务修改多行数据，其中部分行提交失败需回滚时，InnoDB 需通过 undo log 恢复所有修改（依赖排他锁保证原子性），无法像理想 MVCC 那样通过版本号直接回滚单行。
• 空间占用：undo log 长期保留会占用磁盘空间（需依赖 Purge 线程清理）。
• 幻读问题：在 REPEATABLE READ 隔离级别下，InnoDB 通过“间隙锁+临键锁”解决幻读，但这属于锁机制的补充，并非 MVCC 本身的能力。

2. 适用场景

• 读多写少：高并发读场景下，非锁定读显著减少锁竞争（如电商商品详情页查询）。
• 短事务：事务执行时间短，版本链不会过长，Purge 线程可高效清理旧版本（如秒杀活动中的库存扣减）。
• 单行操作：修改单行数据时，版本链回溯效率高（如用户信息更新）。

总结

InnoDB 的 MVCC 是通过 隐藏字段（DB_TRX_ID、DB_ROLL_PTR）、undo log 版本链 和 事务隔离级别规则 共同实现的“非锁定读”优化机制。其本质是通过保留历史版本，让读操作无需等待写锁即可访问旧数据，从而提升并发性能。尽管与传统 MVCC 的“乐观锁”理念有差异，但它有效解决了读写冲突问题，是 InnoDB 高并发能力的核心支撑。理解 MVCC 的实现细节（如版本链、undo log、Purge 线程）对优化数据库性能（如减少锁等待、合理设置隔离级别）至关重要。

关系型数据库与非关系型数据库（NoSQL）

关系型数据库（RDBMS）与非关系型数据库（NoSQL）是两类主流的数据库技术，它们在设计理念、数据模型、适用场景等方面存在显著差异。以下从核心特性、数据模型、事务支持、扩展性、查询方式、典型场景等维度展开对比，帮助理解两者的本质区别。

一、核心设计理念

二、数据模型与结构

示例对比：

• 关系型数据库存储用户信息时，需预先定义users表的列（如id INT、name VARCHAR(50)、age INT），新增字段（如email）需修改表结构。
• MongoDB存储用户信息时，可直接插入包含任意字段的文档（如{ "id": 1, "name": "张三", "age": 25, "email": "zhangsan@example.com" }），后续新增字段无需修改表结构。

三、事务支持

典型场景：

• 银行转账（需保证A账户扣款与B账户入账的原子性）必须使用关系型数据库。
• 社交平台用户发帖（允许短暂延迟同步到粉丝动态）可使用NoSQL（如Cassandra）。

四、扩展性

示例：

• 单机MySQL最大支持约1000万行数据（受限于磁盘IO和内存），超大规模数据需分库分表。
• Cassandra单集群可支持PB级数据，通过增加节点线性提升容量和性能。

五、查询方式

示例：

• 查询“用户及其订单信息”时，关系型数据库可通过SELECT * FROM users u JOIN orders o ON u.id = o.user_id一步完成。
• NoSQL中需在应用层先查用户表，再根据用户ID查订单表（或预先将订单嵌入用户文档中）。

六、典型场景与代表产品

七、总结：如何选择？

选择关系型数据库还是NoSQL，需根据业务需求权衡以下因素：

• 一致性要求：强一致性（如金融）选RDBMS；弱一致性（如日志）选NoSQL。
• 数据结构：固定结构（如用户信息表）选RDBMS；动态结构（如用户行为日志）选NoSQL。
• 并发与规模：高并发、海量数据（如亿级用户）选NoSQL；中小规模、复杂查询选RDBMS。
• 事务需求：跨表事务（如订单支付）选RDBMS；单文档事务（如用户资料更新）可选NoSQL。

混合架构趋势：现代系统常采用“RDBMS + NoSQL”组合（如MySQL存储核心交易数据，Redis缓存高频访问数据，Elasticsearch支持全文搜索），兼顾一致性与性能。

Mysql死锁排查过程

一、案例背景

• 数据库环境：MySQL 8.0.30（InnoDB引擎，默认隔离级别 REPEATABLE-READ（RR））。

• 表结构：test表结构调整为（8.0特性，如隐藏字段DB_TRX_ID、DB_ROLL_PTR等）：

  CREATE TABLE `test` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `a` INT UNSIGNED DEFAULT NULL COMMENT '唯一索引字段',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_a` (`a`)  -- 显式命名唯一索引（8.0推荐显式命名）
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  

• 初始数据：

  INSERT INTO test (id, a) VALUES (1,1), (2,2), (4,4);
  

• 死锁操作：

  • 事务1（T1）：DELETE FROM test WHERE a = 2;（删除a=2的记录）
  • 事务2（T2）：INSERT INTO test (id,a) VALUES (10,2);（插入a=2的新记录）

二、死锁日志获取与解析

在MySQL 8.0中，SHOW ENGINE INNODB STATUS 的输出格式与5.5基本一致，但死锁检测算法和锁类型标识更清晰（如新增 INSERT INTENTION 锁类型明确标识）。以下是关键日志片段：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-01 10:00:00 0x7f8b4c0d7700
*** (1) TRANSACTION:
TRANSACTION 10001, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1001, OS thread handle 0x7f8b4c0d7700, query id 5001 localhost root updating
DELETE FROM test WHERE a = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 3 n bits 80 index `uniq_a` of table `test`.`test` trx id 10001 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

*** (2) TRANSACTION:
TRANSACTION 10002, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1408, 3 row lock(s), undo log entries 2
MySQL thread id 1002, OS thread handle 0x7f8b4c0d7700, query id 5002 localhost root update
INSERT INTO test (id,a) VALUES (10,2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 3 n bits 80 index `uniq_a` of table `test`.`test` trx id 10002 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 3 n bits 80 index `uniq_a` of table `test`.`test` trx id 10002 lock_mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 00000002; asc ;;
 1: len 4; hex 00000002; asc ;;

三、死锁核心流程详解（结合8.0锁机制）

1. 事务执行顺序与锁申请时间线

2. MySQL 8.0锁机制的关键变化（对比5.5）

• 锁类型标识更清晰：8.0日志中明确标注 lock_mode X locks rec but not gap（记录锁，不锁间隙）和 lock_mode S waiting（共享锁等待），便于快速定位锁冲突。
• 死锁检测优化：8.0引入 innodb_deadlock_detect_interval 参数（默认5秒），动态调整死锁检测频率，减少高并发下的性能损耗。
• 唯一索引锁范围缩小：对于唯一索引的等值查询（如 WHERE a=2），8.0仍使用记录锁（非间隙锁），与5.5一致，但通过 DB_TRX_ID 和 DB_ROLL_PTR 更精准定位版本链，减少不必要的锁竞争。

四、死锁根源：锁顺序冲突与RR隔离级别的相互作用

在RR隔离级别下，InnoDB的锁行为需同时满足 一致性读（Consistent Read） 和 写不阻塞读。本案例中：

• T1的DELETE操作：需修改 a=2 的记录，因此申请X锁（排他锁），确保其他事务无法读取或修改该记录的当前版本。
• T2的INSERT操作：因 a 是唯一索引，插入前需通过 SELECT ... FOR UPDATE 隐式检查是否存在重复键（即使未显式执行）。为避免检查期间数据被修改，InnoDB申请S锁（共享锁），防止其他事务删除或更新该记录。

矛盾点：
T2的S锁申请需排队等待T1的X锁释放（X锁与S锁互斥），而T1的X锁申请又被T2已持有的X锁（来自步骤T3的DELETE）阻塞。最终形成 ​循环等待​（T1→T2→T1）。

五、排查步骤扩展（MySQL 8.0专属工具）

除了传统的 SHOW ENGINE INNODB STATUS，MySQL 8.0提供了更强大的诊断工具：

1. performance_schema 实时监控锁等待

通过 performance_schema.data_locks 表实时查看锁等待状态：

SELECT 
  THREAD_ID, 
  OBJECT_SCHEMA, 
  OBJECT_NAME, 
  INDEX_NAME, 
  LOCK_TYPE, 
  LOCK_MODE, 
  LOCK_STATUS, 
  WAITING_THREAD_ID 
FROM performance_schema.data_locks;

• 关键字段：LOCK_MODE（锁模式，如X/S）、LOCK_STATUS（等待/持有）、WAITING_THREAD_ID（等待线程ID）。

2. EXPLAIN ANALYZE 分析锁范围

通过 EXPLAIN ANALYZE 查看SQL的执行计划，确认是否扫描了不必要的索引或行：

EXPLAIN ANALYZE DELETE FROM test WHERE a=2;

• 若输出显示 Using index condition，说明仅扫描了索引 uniq_a，锁范围可控；若显示 Using where; Using filesort，可能存在全表扫描，导致锁范围扩大。

3. innodb_status_output 动态日志

通过设置 SET GLOBAL innodb_status_output=ON; 开启实时InnoDB状态输出，定期抓取日志分析锁竞争趋势。

六、解决方案（适配MySQL 8.0）

针对本案例的死锁问题，结合8.0特性提供以下优化方案：

1. 调整事务顺序，统一加锁顺序

• 问题：T1和T2对同一索引 a=2 的加锁顺序冲突（T1先删后插，T2先删后插）。

• 优化：确保所有事务按相同顺序操作（如先插入后删除，或反之）。例如：

  -- 事务1调整为：先插入后删除（若业务允许）
  BEGIN;
  INSERT INTO test (id,a) VALUES (10,2);  -- 申请S锁（检查重复键）
  DELETE FROM test WHERE a=2;            -- 申请X锁（删除记录）
  COMMIT;
  
  -- 事务2调整为：先删除后插入（与事务1顺序一致）
  BEGIN;
  DELETE FROM test WHERE a=2;            -- 申请X锁
  INSERT INTO test (id,a) VALUES (10,2);  -- 申请S锁（检查重复键，此时T1已提交，X锁释放）
  COMMIT;
  

2. 缩短事务执行时间，减少锁持有时长

• 问题：事务执行时间越长，锁持有时间越久，冲突概率越高。
• 优化：将非必要操作移出事务（如日志记录、远程调用），确保事务仅包含核心数据操作。

3. 调整隔离级别（需权衡一致性）

• 问题：RR隔离级别下，InnoDB会加记录锁，但可能因间隙锁（Next-Key Lock）导致额外阻塞（本案例未触发间隙锁，因查询条件是等值）。

• 优化：若业务允许弱一致性，可将隔离级别改为READ COMMITTED（RC），此时InnoDB仅加记录锁（无间隙锁），减少锁范围。

  SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  

4. 使用覆盖索引避免主键锁

• 问题：若查询条件无法使用唯一索引，可能升级为表锁。
• 优化：为 a 字段添加覆盖索引（本案例已存在唯一索引，无需额外操作），确保查询仅扫描索引，避免回表加主键锁。

5. 监控与预警（8.0专属）

• 启用performance_schema的锁监控：

  UPDATE performance_schema.setup_instruments 
  SET ENABLED = 'YES', TIMED = 'YES' 
  WHERE NAME IN ('statement/sql/delete', 'statement/sql/insert', 'lock');
  

• 定期分析 performance_schema.data_lock_waits 表，识别高频锁冲突的SQL。

总结

本案例的死锁根源是：在MySQL 8.0的RR隔离级别下，两个事务对同一唯一索引 a=2 的锁申请顺序冲突（X锁与S锁互斥），导致循环等待。通过分析8.0的死锁日志（明确锁类型和顺序）、结合 performance_schema 实时监控，可快速定位问题。优化方向包括调整事务顺序、缩短锁持有时间、适配隔离级别等。

产生死锁的必要条件

一、死锁的本质与核心概念

死锁（Deadlock） 是多任务系统中资源竞争的极端表现，指两个或多个进程（或线程、事务）因互相持有对方所需资源而陷入永久阻塞的状态。若无外部干预（如系统强制终止进程），所有相关进程将无法继续执行。

二、死锁的四大必要条件（通用准则）

无论操作系统还是数据库，死锁的产生需同时满足以下四个条件（缺一不可）：

关键结论：只要破坏其中一个条件，死锁即可避免。数据库系统通常通过破坏“循环等待”或“请求与保持”条件来预防死锁。

三、操作系统死锁 vs 数据库死锁

虽然核心逻辑一致，但数据库死锁因资源类型（锁）和执行单元（事务）的特殊性，表现出独特的行为：

四、数据库死锁的典型场景与MySQL实例分析

以用户提供的MySQL案例为例，详细拆解死锁的形成过程：

场景描述

• 表结构：test 表（主键id，唯一索引a），初始数据 (1,1)、(2,2)、(4,4)。
• 事务1（T1）：DELETE FROM test WHERE a=2;（删除a=2的记录）。
• 事务2（T2）：INSERT INTO test (id,a) VALUES (10,2);（插入a=2的新记录）。

死锁形成步骤（结合MySQL 8.0锁机制）

关键结论

本案例中，四个必要条件均被满足：

• 互斥：索引a=2的X锁同一时间只能被一个事务持有。
• 请求与保持：T1持有X锁并请求S锁，T2持有X锁并请求S锁。
• 不剥夺：T1和T2均不会主动释放已持有的X锁。
• 循环等待：T1等待T2的X锁，T2等待T1的X锁，形成闭环。

五、数据库死锁的解锁与预防策略

（一）死锁的自动解锁（以MySQL为例）

InnoDB通过以下机制自动处理死锁：

1. 死锁检测：默认开启（innodb_deadlock_detect=ON），定期扫描事务等待图，识别循环等待链。
2. 事务回滚：选择“权重较小”的事务回滚（权重由事务大小、执行时间等因素决定），释放其持有的锁，解除死锁。
3. 日志记录：通过SHOW ENGINE INNODB STATUS输出死锁日志，包含事务ID、锁资源、等待链等信息，辅助排查。

（二）死锁的预防策略（破坏四大必要条件）

1. 破坏“互斥条件”（不可行）

锁的本质是互斥的，无法通过技术手段消除互斥性（否则无法保证数据一致性）。

2. 破坏“请求与保持条件”

• 策略：事务一次性申请所有所需资源，避免分步申请。
• 数据库实践：
  • 对于需要修改多张表的场景，按固定顺序访问表（如先更新order表，再更新user表）。
  • 对于索引操作，确保所有事务按相同顺序申请锁（如统一先删除后插入，或反之）。

3. 破坏“不剥夺条件”（不可行）

数据库事务的原子性要求事务要么提交要么回滚，无法强制剥夺已持有的锁（否则可能导致数据不一致）。

4. 破坏“循环等待条件”（核心策略）

• 有序资源分配法：为资源（如索引字段值）分配全局顺序，事务按固定顺序申请资源。
  • 示例：所有事务对a字段的操作按a值升序申请锁（如先处理a=1，再处理a=2）。
• 缩短事务时长：减少事务持有锁的时间（如避免在事务中执行耗时操作，如远程调用、复杂计算）。
• 调整隔离级别：
  • MySQL默认RR隔离级别下，InnoDB对唯一索引的等值查询使用记录锁（非间隙锁），减少锁范围。
  • 若业务允许弱一致性，可降级为READ COMMITTED（RC），仅在读取时加记录锁，避免间隙锁导致的额外阻塞。

5. 数据库专属优化（InnoDB）

• 索引优化：为高频操作的字段添加索引（如唯一索引），缩小锁范围（仅锁定目标记录，而非整表）。
• 批量操作拆分：将大事务拆分为多个小事务（如批量删除分多次执行），减少单次事务的锁持有时间。
• 监控与预警：通过performance_schema.data_locks表实时监控锁等待状态，及时发现潜在死锁风险。

六、总结

死锁是多任务系统中资源竞争的必然结果，其本质是四大必要条件的叠加。数据库死锁因资源类型（锁）和执行单元（事务）的特殊性，需结合索引设计、事务顺序、隔离级别等特性进行针对性预防。

开发人员行动指南：

• 设计事务时，确保所有事务按固定顺序访问资源（如索引字段值升序）。
• 缩短事务执行时间，避免在事务中执行非必要操作。
• 为高频字段添加索引，缩小锁范围。
• 监控死锁日志（SHOW ENGINE INNODB STATUS），定期分析并优化高频死锁场景。