【MySQL】MySQL 事务和锁详解

一、MySQL 事务

1.1 事务介绍

  • 在 MySQL 中，事务（Transaction） 是一组不可分割的 SQL 操作序列，这些操作要么全部成功执行，要么全部失败回滚，以此保证数据库操作的完整性和一致性。

  • 事务将数据库从一种一致性状态转换为另一种一致性状态，保证系统始终处于一个完整且正确的状态；

在这里插入图片描述

1.1.1 事务执行流程

假设用户 A（账户余额 1000 元）要向用户 B（账户余额 2000 元）转账 500 元，这个过程需要执行两步操作：

  1. 从 A 的账户扣减 500 元（UPDATE account SET balance = balance - 500 WHERE id = 'A'）；
  2. 向 B 的账户增加 500 元（UPDATE account SET balance = balance + 500 WHERE id = 'B'）。

这两步操作必须同时成功或同时失败，否则会导致数据不一致（比如 A 扣了钱但 B 没收到，或 A 没扣钱但 B 多了钱）。此时，事务就是保证这一点的核心机制。

1. 开启事务
BEGIN;  -- 或 START TRANSACTION，标记事务开始
2. 执行操作
-- 第一步：A账户扣500元
UPDATE account SET balance = balance - 500 WHERE id = 'A';
-- 第二步：B账户加500元
UPDATE account SET balance = balance + 500 WHERE id = 'B';
3. 提交事务（操作成功时）

如果两步更新都没有问题，执行COMMIT确认修改：

COMMIT;

此时，A 的余额变为 500 元，B 的余额变为 2500 元，修改永久生效（符合持久性）。

4. 回滚事务（操作失败时）

假设第一步执行成功（A 扣了 500 元），但第二步因网络故障或 SQL 错误未能执行（B 的余额未变），此时需执行ROLLBACK撤销所有操作：

ROLLBACK;

A 的余额会恢复到 1000 元，B 的余额仍为 2000 元

1.2.1 事务的特征（ACID）

事务必须满足以下四个基本特性，简称 ACID：

原子性（Atomicity）

事务中包含的所有操作（如插入、更新、删除等）是一个不可分割的整体，要么全部成功执行并提交，要么一旦发生错误就全部撤销（回滚），不会出现 “部分执行” 的中间状态

例如：

  • 事务的 “原子性” 类似生活中 “要么全做，要么全不做” 的场景。例如：银行转账时，“A 账户扣除 100 元” 和 “B 账户增加 100 元” 这两个操作必须同时成功 —— 若 A 扣钱后 B 加钱失败，整个事务需回滚（A 的钱恢复，B 的钱不变），避免出现 “钱凭空消失” 的错误。

实现机制：

  • 通过undolog（回滚日志） 实现。undolog 记录事务中每一步操作的 “逆运算”（例如，插入操作的逆是删除，更新操作的逆是恢复原值）。当事务需要回滚时，数据库会 “回放” 这些逆运算，将数据恢复到事务开始前的状态。
一致性（Consistency）

事务的执行必须使数据库从一个一致性状态转变为另一个一致性状态，且始终满足数据库的完整性约束（如主键唯一、外键关联、字段非空等）

例如：

  • 若表中 “用户名” 字段设为唯一键，事务中修改用户名时，提交后必须保证新用户名不重复；若修改后出现重复，事务必须回滚，否则破坏一致性。

  • 转账场景中，A 和 B 的总余额在事务前后必须相等（A 减少 100，B 增加 100，总余额不变），这就是一种一致性约束。

隔离性（Isolation）

当多个事务同时操作数据库时，每个事务的操作应与其他事务 “隔离”，互不干扰。隔离性通过定义不同的隔离级别，控制并发事务对同一数据的访问行为，解决脏读、不可重复读、幻读等问题

  1. 并发问题：

    • 脏读：事务 A 读取到事务 B 未提交的修改（若 B 回滚，A 读取的数据是 “无效” 的）。
    • 不可重复读：事务 A 多次读取同一数据时，事务 B 修改并提交了该数据，导致 A 两次读取结果不一致。
    • 幻读：事务 A 按条件查询数据时，事务 B 插入了符合条件的新数据，导致 A 再次查询时多了 “不存在” 的记录。

  2. 隔离级别（MySQL 默认是 “可重复读”）：

    • 读未提交（Read Uncommitted）：最低级别，允许读取未提交的事务数据，可能出现脏读、不可重复读、幻读。
    • 读已提交（Read Committed）：只能读取已提交的事务数据，避免脏读，但仍可能出现不可重复读、幻读。
    • 可重复读（Repeatable Read）：保证同一事务内多次读取数据结果一致，避免脏读、不可重复读，MySQL 通过 MVCC 避免幻读（大部分场景）。
    • 串行化（Serializable）：最高级别，事务串行执行（不并发），完全避免所有并发问题，但性能极低。

  3. 实现机制：

    • MVCC（多版本并发控制）：通过为数据记录保存多个版本，实现 “非锁定读”。每个事务看到的数据版本由其开始时间决定，避免了读操作对写操作的阻塞，提升并发性能。
    • 锁机制：处理并发写操作（如同时更新同一行）。MySQL 支持多种粒度的锁：
      • 表锁：对整个表加锁（如 ALTER TABLE 操作），粒度大，并发低。
      • 页锁：对数据页（B + 树的叶子节点）加锁，粒度中等。
      • 行锁：对单行记录加锁（如 InnoDB 的行级锁），粒度小，并发高。

  4. 事务控制语句：

    • START TRANSACTION / BEGIN：显式开启事务。
    • COMMIT：提交事务，将所有修改持久化。
    • ROLLBACK：回滚事务，撤销所有未提交的修改。
    • SAVEPOINT <标识>：在事务中创建保存点（中间状态）。
    • ROLLBACK TO SAVEPOINT <标识>：回滚到指定保存点（而非事务开始），适用于部分回滚场景。
    • RELEASE SAVEPOINT <标识>：删除保存点。

在这里插入图片描述

设置和查看隔离级别

设置当前会话的隔离级别（仅对当前连接有效）

-- 语法：设置为指定级别
SET TRANSACTION ISOLATION LEVEL [隔离级别];-- 示例：
-- 设置为读未提交
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 设置为读已提交
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 设置为可重复读（默认）
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 设置为串行化
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

设置全局隔离级别（影响所有新会话，不影响当前已存在的会话）

-- 语法：
SET GLOBAL TRANSACTION ISOLATION LEVEL [隔离级别];-- 示例：设置全局隔离级别为读已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

查看当前会话的隔离级别

-- MySQL 8.0+ 推荐语法
SELECT @@transaction_isolation;-- 兼容旧版本（MySQL 5.7及以下）
SELECT @@tx_isolation;

查看全局隔离级别（新会话的默认隔离级别）

-- MySQL 8.0+ 推荐语法
SELECT @@global.transaction_isolation;-- 兼容旧版本
SELECT @@global.tx_isolation;

首先，我们准备这样的表，用于下面的事件隔离测试：

在这里插入图片描述

读未提交

其中一个客户端修改了分数，但未提交事务

-- 开启事务，修改学生1的课程1成绩
BEGIN;
UPDATE score_tbl SET score = 90.00 WHERE student_id=1 AND course_id=1;
-- 不执行COMMIT，保持事务未提交

另一个客户端使用读未提交的隔离界别，读到了脏数据

-- 设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
-- 查询学生1的课程1成绩
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1; 
-- 结果：90.00（读取到事务A未提交的修改，出现【脏读】）

在这里插入图片描述

读已提交

客户端A开启事务，查询成绩，输出结果为90

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
-- 第一次查询学生1的课程1成绩
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1;

在这里插入图片描述

另一个客户端B修改了成绩，并且提交了事务

BEGIN;
-- 修改学生1的课程1成绩并提交
UPDATE score_tbl SET score = 95.00 WHERE student_id=1 AND course_id=1;
COMMIT; -- 提交事务

此时，回到A客户端，事务还没有执行结束，我们再查询一遍分数，变成了95，出现了不可重复读

SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1;

在这里插入图片描述

可重复读

同样的方式，这次我们设置可重复读的隔离界别

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
-- 第一次查询学生1的课程1成绩
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1; 
-- 结果：90

在这里插入图片描述

即使另一个事务提交了，也不会影响当前事务的读操作

BEGIN;
-- 修改学生1的课程1成绩并提交
UPDATE score_tbl SET score = 95.00 WHERE student_id=1 AND course_id=1;
COMMIT; -- 提交事务

读取的依然是修改前的值

SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1; 
-- 结果：90

在这里插入图片描述

事务提交后，再次查询，已经查询到了最新的值

SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1;

在这里插入图片描述

串行化

一个客户端设置串行化隔离，查询记录，此时上锁了

在这里插入图片描述

另一个客户端尝试插入数据，但是被阻塞了

BEGIN;
INSERT INTO score_tbl (student_id, course_id, score) VALUES (3, 4, 80.00);

在这里插入图片描述

提交事务后，锁被释放，成功插入

COMMIT;

在这里插入图片描述

不同隔离界别的对比
隔离级别脏读不可重复读幻读测试场景表现（结合 score 表）
读未提交可能可能可能看到未提交的成绩修改（如 90.00）
读已提交不可能可能可能仅看到已提交的成绩修改（两次查询结果不同）
可重复读（默认）不可能不可能不可能事务内成绩查询结果不变，课程记录数不新增
串行化不可能不可能不可能插入课程 4 记录时被阻塞，需等待前事务结束
持久性（Durability）

事务一旦提交，其对数据的修改将永久保存，即使发生数据库宕机、断电等故障，修改也不会丢失

实现机制：

  • 通过redolog（重做日志） 实现。redolog 是一种物理日志，记录 “某个数据页的偏移量上修改了什么数据”（而非具体操作）

  • 事务提交时，数据库会将修改记录写入 redolog 并刷盘（确保写入磁盘）。即使此时数据尚未写入数据文件（MySQL 为提升性能会先放内存），若发生宕机，重启后数据库可通过 redolog"重放" 操作，恢复提交的修改

1.2 锁

锁机制用于管理对共享资源的并发访问，用来实现事务的隔离级别

在这里插入图片描述

1.2.1 锁的粒度

MySQL InnoDB 支持多粒度锁，即可以对不同层级的资源加锁，从大到小分为：

  • 表级锁：对整个表加锁（如意向锁、AUTO-INC 锁）；
  • 页级锁：对 B+ 树的叶子节点页加锁（较少直接使用，由引擎自动管理）；
  • 行级锁：对表中某一行记录加锁（如共享锁、排他锁）。

粒度越小，并发度越高，但锁管理开销越大；粒度越大，并发度越低，开销越小。

1.2.2 锁类型

根据作用和范围，MySQL 锁可分为以下几类：

1. 共享锁（S 锁，行级）

  • 定义：事务对记录执行读操作时加的锁，允许其他事务加 S 锁，但阻塞 X 锁。

  • 触发时机：

    • SERIALIZABLE 隔离级别下，普通 SELECT 自动加 S 锁；
    • 其他级别需手动加锁：SELECT ... LOCK IN SHARE MODE;

  • 释放时机：事务提交或回滚后释放。

-- 事务A加S锁
BEGIN;
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1 LOCK IN SHARE MODE; 
-- 此时事务A持有该记录的S锁-- 事务B尝试加X锁（更新操作）会被阻塞
BEGIN;
UPDATE score_tbl SET score=95 WHERE student_id=1 AND course_id=1; -- 阻塞，直到事务A提交
不同隔离级别下的读操作锁机制
SERIALIZABLE

  • 读操作锁行为：所有读操作（SELECT）会默认自动加 S 锁（无需手动指定）。

  • 设计目标：提供最高隔离性，避免脏读、不可重复读、幻读，但牺牲并发性能（本质是 “串行执行事务”）。

  • 底层逻辑：通过 S 锁强制阻塞修改操作 —— 只要事务 A 对某行加了 S 锁，事务 B 想修改该行（加 X 锁）就必须等待事务 A 提交 / 回滚，直到 S 锁释放。

  • 事务 A：

BEGIN;
-- 自动加S锁（SERIALIZABLE级别默认）
SELECT * FROM users WHERE id = 1;
  • 事务 B：
BEGIN;
-- 尝试加X锁修改，会被事务A的S锁阻塞（需等待）
UPDATE users SET name = 'Bob' WHERE id = 1;
  • 结果：事务 B 的 UPDATE 会被阻塞，直到事务 A 执行COMMIT或ROLLBACK释放 S 锁后，事务 B 才能继续
REPEATABLE READ
  • 读操作锁行为：默认不加 S 锁，而是通过MVCC（多版本并发控制） 保证 “可重复读”（同一事务内多次读同一行，结果一致）；若需解决幻读，需手动加 S 锁（SELECT ... LOCK IN SHARE MODE）或依赖间隙锁（Gap Lock）。
  • 设计目标：在保证可重复读的前提下，尽可能提升并发性能（避免默认加锁导致的阻塞）

（1）默认不加 S 锁：依赖 MVCC 实现可重复读
MVCC 的核心是 “读快照”：事务启动时生成一个read view（快照），后续读操作均基于该快照，不受其他事务提交的修改影响。

  • 事务 A：
BEGIN;
-- 不加S锁，读快照（此时id=1的name是Alice）
SELECT * FROM users WHERE id = 1; -- 结果：Alice
  • 事务 B：
BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1;
COMMIT; -- 提交修改
  • 事务 A 再次读：
SELECT * FROM users WHERE id = 1; -- 结果仍为Alice（MVCC保证可重复读）
  • 原因：事务 A 的read view在启动时生成，只可见启动前已提交的版本，事务 B 的新修改不可见，因此无需加锁也能保证 “可重复读”。

（2）手动加 S 锁：解决幻读
幻读指 “同一事务内，两次读同一范围，第二次读到新插入的行”。RR 级别默认通过间隙锁（Gap Lock） 防止幻读，但如果是 “读操作需要严格禁止其他事务插入新行”，可手动加 S 锁（结合间隙锁生效）。

  • 事务 A：
BEGIN;
-- 手动加S锁（同时会对范围加间隙锁）
SELECT * FROM users WHERE id BETWEEN 1 AND 10 LOCK IN SHARE MODE;
-- 此时读到id=1（Alice）
  • 事务 B：
BEGIN;
-- 尝试插入新行（id=5），会被事务A的间隙锁+S锁阻塞
INSERT INTO users (id, name) VALUES (5, 'Charlie');
  • 结果：事务 B 的 INSERT 被阻塞，直到事务 A 提交释放 S 锁和间隙锁，避免了事务 A 再次读时出现 “幻读”（读到 id=5 的新行）。
READ COMMITTED

  • 读操作锁行为：不加 S 锁，完全依赖 MVCC，但与 RR 级别的 MVCC 不同：RC 级别每次读操作都会生成新的read view（只可见已提交的最新版本）。

  • 设计目标：保证 “读已提交”（避免脏读），但不保证可重复读，通过牺牲部分隔离性换取更高并发（无需加锁，修改操作阻塞少）。

  • 事务 A：

BEGIN;
-- 不加S锁，读当前已提交的快照（id=1的name是Alice）
SELECT * FROM users WHERE id = 1; -- 结果：Alice
  • 事务 B：
BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1;
COMMIT; -- 提交修改
  • 事务 A 再次读：
-- 生成新的read view，可见事务B的提交，结果为Bob
SELECT * FROM users WHERE id = 1; -- 结果：Bob
  • 为何无需 S 锁：RC 级别允许 “不可重复读”（两次读结果可不同），因此不需要通过 S 锁阻塞修改操作；MVCC 仅保证 “不读未提交的数据”（避免脏读），足够满足需求。
READ UNCOMMITTED

  • 读操作锁行为：既不加 S 锁，也不使用 MVCC，读操作直接读取当前数据页的最新版本（无论其他事务是否提交）。

  • 设计目标：最低隔离级别，追求极致性能，允许脏读（读未提交的数据），几乎不用于生产环境。

  • 事务 A：

BEGIN;
UPDATE users SET name = 'Bob' WHERE id = 1; -- 未提交，加X锁
  • 事务 B：
BEGIN;
-- 无锁无MVCC，直接读事务A未提交的修改
SELECT * FROM users WHERE id = 1; -- 结果：Bob（脏读）
  • 风险：若事务 A 执行ROLLBACK，事务 B 读到的 “Bob” 就是无效数据，可能导致业务逻辑错误。
2. 排他锁（X 锁，行级）
  • 定义：事务对记录执行更新 / 删除操作时加的锁，完全阻塞其他事务的 S 锁和 X 锁。
  • 触发时机：所有隔离级别下，UPDATE/DELETE 操作自动加 X 锁；手动加锁：SELECT ... FOR UPDATE;
  • 释放时机：事务提交或回滚后释放。
-- 事务A加X锁
BEGIN;
UPDATE score_tbl SET score=95 WHERE student_id=1 AND course_id=1; 
-- 此时事务A持有该记录的X锁-- 事务B尝试加S锁会被阻塞
BEGIN;
SELECT score FROM score_tbl WHERE student_id=1 AND course_id=1 LOCK IN SHARE MODE; -- 阻塞
3. 意向锁（表级）

意向锁是表级锁，用于标识 “某事务计划对表中某些行加 S 锁或 X 锁”，避免表级锁与行级锁的冲突。分为：

  • 意向共享锁（IS）：事务计划对某些行加 S 锁前，先对表加 IS 锁；
  • 意向排他锁（IX）：事务计划对某些行加 X 锁前，先对表加 IX 锁。

作用：快速判断表中是否有行级锁（例如，若表有 IX 锁，则全表扫描加表锁的操作会被阻塞）。

事务 A 更新某行时，先对表加 IX 锁，再对行加 X 锁：

BEGIN;
-- 自动加IX锁（表级），再对行加X锁（行级）
UPDATE score_tbl SET score=95 WHERE student_id=1 AND course_id=1;
4. AUTO-INC 锁（表级）
  • 定义：针对 AUTO_INCREMENT 自增列的特殊表锁，保证插入时自增值唯一。
  • 触发时机：INSERT 操作插入自增列时自动加锁，插入完成后立即释放（MySQL 5.1.22+ 优化为轻量级锁，并发插入性能提升）。
-- 表结构：id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(10)
BEGIN;
INSERT INTO students (name) VALUES ('Alice'); -- 加AUTO-INC锁，获取id=1，立即释放
COMMIT;
不同隔离级别锁的对比
隔离级别读操作是否加 S 锁依赖机制解决的问题未解决的问题性能
SERIALIZABLE是（默认）共享锁（S 锁）脏读、不可重复读、幻读无（但串行化）最差
REPEATABLE READ否（默认）MVCC + 间隙锁脏读、不可重复读、幻读无（InnoDB 中）中等
READ COMMITTED否MVCC（实时快照）脏读不可重复读、幻读较好
READ UNCOMMITTED否无（直接读最新）无脏读、不可重复读、幻读最好（不推荐）
锁的兼容性
1. 意向锁

  • 意向锁是表级锁，用于标识 “某事务计划对表中部分行加 S 锁或 X 锁”，是行级锁的 “预告”。

  • 目的：快速判断表中是否存在行级锁，避免全表扫描时的锁冲突（例如，若表有意向锁，全表锁操作需等待意向锁释放）。

  • 意向锁之间互相兼容：IS（意向共享锁）与 IS、IX（意向排他锁）之间无冲突；IX 与 IS、IX 之间也无冲突。

  • 意向锁与行级锁兼容：意向锁（表级）和行级锁（S/X 锁）互不干扰，仅行级锁之间会冲突（如 S 锁与 X 锁冲突）。

  • 意向锁与表级锁的冲突：

    • IS 锁会阻塞表级 X 锁（全表写锁），但允许表级 S 锁（全表读锁）；
    • IX 锁会阻塞表级 S 锁和 X 锁（全表读写锁均被阻塞）；
    • 意向锁不阻塞非全表扫描的请求（如通过索引访问行）。
2. 行级锁
  • 加 S 锁（行级读锁） 前：先自动为表和所在页加 IS 锁，再为目标行加 S 锁。
  • 加 X 锁（行级写锁） 前：先自动为表和所在页加 IX 锁，再为目标行加 X 锁。
已持有锁 \ 请求锁S（共享锁）X（排他锁）IS（意向共享锁）IX（意向排他锁）AI（AUTO-INC 锁）
S（共享锁）兼容冲突兼容冲突冲突
X（排他锁）冲突冲突冲突冲突冲突
IS（意向共享锁）兼容冲突兼容兼容兼容
IX（意向排他锁）冲突冲突兼容兼容兼容
AI（AUTO-INC 锁）冲突冲突兼容兼容冲突
锁算法
1. Record Lock（记录锁）
  • 定义：锁定单个行记录（仅锁定索引对应的行，不包含范围）。
  • 适用场景：通过唯一索引（主键 / 唯一键）精准命中某一行时。

表 score_tbl 主键为 (student_id, course_id)，事务 A 更新精准命中的行：

-- 事务A
BEGIN;
UPDATE score_tbl SET score=95 WHERE student_id=1 AND course_id=1; -- 加Record Lock（行级X锁）
2. Gap Lock（间隙锁）
  • 定义：锁定索引范围内的间隙（不包含记录本身），防止其他事务在间隙中插入数据（解决幻读）。
  • 适用场景：REPEATABLE READ 及以上级别，通过非唯一索引或范围查询时触发。
  • 范围示例：若索引存在值 4、7，则间隙锁可能锁定 (4,7) 区间。

表 students 有索引 age，值为 22、24、25。事务 A 锁定 age=24 的间隙：

-- 事务A（REPEATABLE READ级别）
BEGIN;
-- 查询age=24，未命中（假设无此记录），加Gap Lock锁定(22,25)间隙
SELECT * FROM students WHERE age=24 FOR UPDATE;-- 事务B尝试在间隙中插入数据会被阻塞
BEGIN;
INSERT INTO students (age) VALUES (23); -- 阻塞，因(22,25)被Gap Lock锁定
3. Next-Key Lock（记录锁 + 间隙锁）
  • 定义：锁定索引范围 + 记录本身（左开右闭区间），是 InnoDB 默认的行锁算法。
  • 适用场景：非唯一索引的精准查询或范围查询（REPEATABLE READ 级别）。

表 students 索引 age 有值 22、24、25。事务 A 查询 age=24（命中）：

-- 事务A
BEGIN;
SELECT * FROM students WHERE age=24 FOR UPDATE; 
-- 加Next-Key Lock，锁定(22,24]区间（包含24本身的Record Lock + (22,24)的Gap Lock）-- 事务B插入age=23（在(22,24)间隙）会被阻塞；更新age=24也会被阻塞
4. Insert Intention Lock（插入意向锁）
  • 定义：INSERT 操作时产生的特殊间隙锁，标识 “事务想在某间隙插入数据”，多个事务插入不冲突的行时不互斥。
  • 适用场景：多事务同时向同一间隙插入不同数据时。

索引 age 有值 4、7，事务 A 插入 5，事务 B 插入 6：

-- 事务A
BEGIN;
INSERT INTO students (age) VALUES (5); -- 加Insert Intention Lock锁定(4,7)间隙-- 事务B
BEGIN;
INSERT INTO students (age) VALUES (6); -- 同样加Insert Intention Lock，与A兼容，不阻塞
锁兼容

锁之间的兼容性决定了 “一个事务持有某锁时，其他事务能否请求另一锁”。横向为已持有锁，纵向为请求锁：

  • 间隙锁（GAP）与插入意向锁（Insert Intention）冲突（防止插入间隙数据）；
  • 记录锁（Record）与任何请求的 Record/Next-Key 锁冲突（同一行互斥）。
持有锁 \ 请求锁GAPInsert IntentionRecordNext-Key
GAP兼容冲突兼容兼容
Insert Intention兼容兼容兼容兼容
Record兼容兼容冲突冲突
Next-Key兼容冲突冲突冲突
锁的对象
  • 行级锁是针对表的索引加锁
  • 表级锁是针对页或表进行加锁
  • 索引包括聚集索引和辅助索引，重点考虑 InnoDB 在 read committed 和 repeatable read 级别下锁的情况

假设我们有如下的表，其中 id 为主键，no（学号），辅助唯一索引，name（姓名）和 age（年龄）为辅助普通索引，score（学分）无索引

idnonameagescore
15S0001Bob2534
18S0002Alice2477
20S0003Jim245
30S0004Eric2391
37S0005Tom2222
49S0006Tom2583
50S0007Rose2389

聚集索引，查询命中：

UPDATE students SET score = 100 WHERE id = 15;

在这里插入图片描述

聚集索引，查询未命中：

UPDATE students SET score = 100 WHERE id = 16;

在这里插入图片描述

辅助唯一索引，查询命中：

UPDATE students SET score = 100 WHERE no = 'S0003';

在这里插入图片描述

辅助唯一索引，查询未命中：

UPDATE students SET score = 100 WHERE no = 'S0008';

在这里插入图片描述

辅助非唯一索引，查询命中：

UPDATE students SET score = 100 WHERE name = 'Tom';

在这里插入图片描述

辅助非唯一索引，查询未命中：

UPDATE students SET score = 100 WHERE name = 'John';

在这里插入图片描述

无索引：

UPDATE students SET score = 100 WHERE score = 22;

在这里插入图片描述

聚集索引，范围查询：

UPDATE students SET score = 100 WHERE id <= 20;

在这里插入图片描述

辅助索引，范围查询：

UPDATE students SET score = 100 WHERE age <= 23;

在这里插入图片描述

修改索引值：

UPDATE students SET name = 'John' WHERE id = 15;

在这里插入图片描述

MVCC

  • MVCC（Multi-Version Concurrency Control，多版本并发控制）是 InnoDB 存储引擎实现隔离级别的核心机制，它通过保存数据的多个版本，允许读写操作不相互阻塞，从而提高数据库的并发性能。

  • 简单来说，当多个事务同时读写数据库时，MVCC 会为每个事务提供一个独立的 “数据快照”，事务操作的是自己快照中的数据，而不是直接修改或读取最新的物理数据，以此避免锁竞争并实现不同的隔离级别。

  • 在 read committed 和 repeatable read 下，innodb 使用MVCC

  • 然后对于快照数据的定义不同，在 read committed 隔离级别下，对于快照数据总是读取被锁定行的最新一份快照数据；

  • 而在 repeatable read 隔离级别下，对于快照数据总是读取事务开始时的行数据版本；

在这里插入图片描述

首先我们要了解，聚集索引当中是存在隐藏列的，他们分别是：

  • trx_id：事务修改记录时，trx_id 记录该事务修改 id；
  • roll_pointer：事务修改记录时，将旧记录写入到 undolog 中，roll_pointer 指向旧版本的记录。

比如说当前有一个学生表，包含年龄跟姓名，我们在表中插入一条记录，作为事务1，他的字段就应该是这样的：

在这里插入图片描述

此时我们需要去执行事务 2，修改张三的名字为李四，那么现在当前事务的 trx_id = 2，roll_pointer 就会指向原来事务 1 的那张表的记录

在这里插入图片描述

然后又有一个事务 3 ，此时要修改李四的 age 为 38，此时版本链就应该如下：

在这里插入图片描述

我们所说的回滚，其实就是根据这个事务的操作，然后根据版本链，去寻找之前的一些记录，将对应的数据进行回恢复即可。

Read View

Read View（读视图）是 MVCC 在READ COMMITTED（RC） 和REPEATABLE READ（RR） 隔离级别中实现数据可见性的机制

隔离级别Read View 创建时机数据可见性特点
READ COMMITTED事务中每次执行 SELECT 时生成新的 Read View同一事务中多次读取可能不一致（可看到其他事务已提交的修改），避免脏读，但允许不可重复读。
REPEATABLE READ事务首次执行 SELECT 时生成 Read View，后续复用同一事务中多次读取结果一致（仅可见事务启动前已提交的数据），避免不可重复读。
1. Read View 的构成
  • m_ids：创建 Read View 时，当前活跃（已启动未提交）的事务 ID 列表。
  • min_trx_id：m_ids中最小的事务 ID。
  • max_trx_id：当前系统即将分配的下一个事务 ID（非m_ids中的最大值）。
  • creator_trx_id：创建该 Read View 的事务自身 ID。
2. 数据版本可见性判断流程（基于聚集索引隐藏列trx_id）

聚集索引每行数据包含隐藏列：

  • trx_id：最后修改该行的事务 ID；
  • roll_pointer：指向 undo 日志中该行的上一版本（形成版本链）。

判断规则：

  1. 若trx_id < min_trx_id：修改该行的事务在 Read View 创建前已提交，可见。
  2. 若trx_id >= max_trx_id：修改该行的事务在 Read View 创建后启动，不可见。
  3. 若min_trx_id <= trx_id < max_trx_id：
    • 若trx_id在m_ids中（事务仍活跃）：不可见；
    • 若trx_id不在m_ids中（事务已提交）：可见；
Redo 日志
  • 作用：确保事务提交后，修改不会因宕机丢失（实现 ACID 中的 “持久性”）。
  • 存储：分为内存中的redo log buffer和磁盘中的redo log file（物理文件）。
  • 特点：
    • 顺序写入，性能高；
    • 记录对数据页的物理修改（页地址、偏移量、修改内容）；
    • 仅在数据库宕机后恢复时使用（重演修改，恢复数据）。
  • 机制：事务提交时，必须先将 redo 日志持久化到redo log file，才算提交完成。
Undo日志
  • 作用：
    1. 事务回滚：记录操作的逆逻辑（如 INSERT 的逆操作为 DELETE，UPDATE 的逆操作为反向 UPDATE），使事务可回滚到修改前状态。
    2. MVCC 支持：通过roll_pointer串联行的多个版本（版本链），为 Read View 提供历史数据版本。
  • 存储：位于共享表空间，是逻辑日志（记录 “做了什么”，而非物理修改）。

1.2.3 死锁

MySQL 死锁机制

死锁是指两个或多个事务在执行过程中，因争夺锁资源而陷入互相等待的状态：每个事务都持有对方需要的锁，且都无法继续执行，形成循环等待，导致所有事务都被阻塞。

MySQL（InnoDB 存储引擎）通过 wait-for graph（等待图） 实现死锁检测：

  • 等待图中，每个节点代表一个事务，每条边代表一个事务对另一个事务持有的锁的等待关系。
  • 采用 非递归深度优先算法 检测图中是否存在循环（循环即表示死锁）。
  • 当检测到死锁时，MySQL 会选择 代价较小的事务（如修改行数少的事务）进行回滚，释放其持有的锁，解除死锁。
MySQL常见死锁
1. 相反加锁顺序死锁

核心原因：多个事务对同一批资源（表、行）按 相反顺序加锁，导致互相等待对方释放锁。

  • 场景 1：不同表加锁顺序相反
    例：事务 1 先锁表 A 再锁表 B，事务 2 先锁表 B 再锁表 A。

    • 事务 1：UPDATE tableA ...（持有表 A 的锁）→ 等待锁表 B；
    • 事务 2：UPDATE tableB ...（持有表 B 的锁）→ 等待锁表 A；
      此时形成循环等待，触发死锁。

  • 场景 2：同表不同行加锁顺序相反（隐含索引锁关联）
    InnoDB 加锁时，辅助索引加锁会同时锁定对应的聚集索引（主键索引），可能导致隐藏的加锁顺序问题。

    以students表为例（id为主键，no为辅助唯一索引）：

    • 事务 1：先通过no='S0001'（辅助索引）加锁→ 同时锁定id=15（聚集索引）→ 再通过id=18加锁；
    • 事务 2：先通过id=18加锁→ 再通过no='S0001'加锁→ 需等待事务 1 释放id=15的锁；
      事务 1 等待事务 2 释放id=18的锁，事务 2 等待事务 1 释放id=15的锁，形成死锁。

  • 场景 3：外键 / 触发器隐含加锁
    外键约束检查时会锁定父表行，触发器执行时可能隐含对其他表的加锁，若加锁顺序相反，也会导致死锁。

2. 锁冲突死锁（插入意向锁与 gap 锁冲突）

InnoDB 在 Repeatable Read（RR）隔离级别 下，为保证幻读，会使用gap锁（间隙锁）和next-key锁（记录锁 + gap 锁）。

  • 插入意向锁：一种特殊的间隙锁，用于插入操作，需在插入位置的间隙获取，仅与其他间隙锁冲突。

  • 冲突场景：两个事务在同一间隙持有 gap 锁，且都等待对方释放锁以获取插入意向锁。

    • 例：students表id为 15、18、20…，事务 1 先执行SELECT * FROM students WHERE id BETWEEN 16 AND 17 FOR UPDATE（加 gap 锁：(15,18)）；事务 2 也执行相同语句（同样持有 (15,18) 的 gap 锁）。此时两事务都想插入id=16的行，需获取插入意向锁，但都被对方的 gap 锁阻塞，互相等待形成死锁。
死锁的预防和处理

  1. 预防措施

    • 统一加锁顺序：所有事务按固定顺序（如表名、主键升序）加锁，避免相反顺序。
    • 减少锁持有时间：事务中尽量晚加锁、早提交，缩短锁占用时长。
    • 降低隔离级别：使用Read Committed（RC），RC 下仅外键和唯一索引冲突时用 gap 锁，减少锁范围。
    • 避免范围锁：尽量用精确查询（WHERE id=?）代替范围查询（WHERE id < ?），减少 gap 锁使用。

  2. 处理方式

    • MySQL 自动检测死锁后，回滚代价较小的事务，报错：deadlock found when trying to get lock。
    • 应用程序需捕获该错误，重试事务（需避免无限重试）

更多资料：https://github.com/0voice

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如若转载，请注明出处：http://www.pswp.cn/diannao/92901.shtml
繁体地址，请注明出处：http://hk.pswp.cn/diannao/92901.shtml

如若内容造成侵权/违法违规/事实不符，请联系多彩编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

虚拟直线阈值告警人员计数算法暑期应用

虚拟直线阈值告警人员计数算法暑期应用

智慧人员计数助力暑期&#xff1a;技术赋能安全管理的创新实践一、背景&#xff1a;暑期人流激增下的安全管理挑战暑期是旅游、商业、交通等场景的客流高峰期&#xff0c;人员密集区域易引发踩踏事故、管理混乱等安全隐患。传统人工计数方式效率低、误差大&#xff0c;难以满足…
阅读更多...
SQL164 2021年11月每天新用户的次日留存率

SQL164 2021年11月每天新用户的次日留存率

SQL164 2021年11月每天新用户的次日留存率 思路 ​找出新用户​&#xff1a;确定每个用户首次活跃的日期&#xff08;即新用户&#xff09; 例如101用户在11月1日首次出现 ​处理跨天活跃​&#xff1a;考虑用户可能跨天活跃的情况&#xff08;in_time和out_time不在同一天&a…
阅读更多...
基于单片机的数字电压表设计

基于单片机的数字电压表设计

2 系统原理及基本框图 如图2.1所示&#xff0c;模拟电压经过档位切换到不同的分压电路衰减后&#xff0c;经隔离干扰送到A/D转换器进行A/D转换&#xff0c;然后送到单片机中进行数据处理。处理后的数据送到LCD中显示&#xff0c;同时通过串行通讯与上位机通信。图2.1系统基本方…
阅读更多...
[NLP]UPF基本语法及其在 native low power verification中的典型流程

[NLP]UPF基本语法及其在 native low power verification中的典型流程

UPF基本语法及其在 native low power verification中的典型流程 摘要：本文首先简要介绍 UPF（Unified Power Format），然后解释其在 native low power verification（原生低功耗验证）中的典型流程。最后，我将使用50个具体例子来完整展示 UPF 的关键语法。这些例子基…
阅读更多...
fish-speech 在50系列显卡使用 --compile加速兼容

fish-speech 在50系列显卡使用 --compile加速兼容

#环境说明 GPU: NVIDIA GeForce RTX 5080 Laptop GPU (sm_120) win11家庭版 24H2 #问题汇总 baize.exceptions.HTTPException: (500, "RuntimeError: ptxas failed with error code 4294967295: \\n\\n") 问题汇总 1 baize.exceptions.HTTPException: (500, "…
阅读更多...
UI自动化测试实战

UI自动化测试实战

Python接口自动化测试零基础入门到精通&#xff08;2025最新版&#xff09;一、设计背景 随着IT行业的发展&#xff0c;产品愈渐复杂&#xff0c;web端业务及流程更加繁琐&#xff0c;目前UI测试仅是针对单一页面&#xff0c;操作量大。为了满足多页面功能及流程的需求及节省工…
阅读更多...
面试实战，问题六，被问数据库索引，怎么回答

面试实战，问题六，被问数据库索引，怎么回答

Java开发面试&#xff1a;数据库索引的原理及常见问题解答 在Java开发面试中&#xff0c;数据库索引是核心知识点&#xff0c;涉及数据库优化和性能调优。索引通过高效的数据结构加速数据检索&#xff0c;降低磁盘IO成本&#xff0c;并支持排序操作。下面我将逐步解释索引的原理…
阅读更多...
ARM-I2C硬实现

ARM-I2C硬实现

硬件I2C-GD32F4系列的实现初始化操作在初始化函数里执行以下代码uint32_t i2cx_scl_port_rcu RCU_GPIOB; uint32_t i2cx_scl_port GPIOB; uint32_t i2cx_scl_pin GPIO_PIN_6; uint32_t i2cx_scl_af GPIO_AF_4;uint32_t i2cx_sda_port_rcu RCU_GPIOB; uint32_t i2cx_sda_po…
阅读更多...
WinUI3开发_过渡动画

WinUI3开发_过渡动画

简介 过渡动画是当发生事件时控件UI状态发生改变时以一种动画形式来演变到另外一种状态&#xff0c;而非瞬间改变&#xff0c;使用一种更加平滑的方式来进行切换&#xff0c;例如下图是文字切换的交叉栅栏效果&#xff1a;还有页面切换动画&#xff1a;在或者是图标动画&#x…
阅读更多...
Linux下提权root权限

Linux下提权root权限

现在AI工具这么丰富&#xff0c;稍微搜一下就有一个差不多的总结输出。但是&#xff0c;可能还不够详细&#xff0c;或者给得太多~~~今天时间关系&#xff0c;今天只总结了在Linux如何提权到root&#xff0c;并没有写如何进行防护。后面有时间&#xff0c;我再总结一下。命令实…
阅读更多...
焊接机器人节能先锋

焊接机器人节能先锋

汽车制造业中&#xff0c;机器人技术的应用已成为推动工业自动化和生产效率提升的重要力量。机器人在焊接、组装、涂装等关键制造环节中扮演着不可或缺的角色。随着工业生产规模的不断扩大&#xff0c;能源消耗和成本控制成为了企业必须面对的重大挑战。尤其是工业焊接用气的大…
阅读更多...
MinIO：云原生对象存储的终极指南

MinIO：云原生对象存储的终极指南

MinIO 是什么？ MinIO 是一款高性能、云原生的对象存储服务，具有以下优势： 轻量级部署：采用 Go 语言编写，资源占用低，支持快速部署 兼容性强：完全兼容 Amazon S3 API，轻松对接现有应用 高可用架构：支持分布式部署，确保数据持久性和服务可用性 高性能表现：专为云环境…
阅读更多...
Spring AOP `MethodInvocation` 工作原理

Spring AOP `MethodInvocation` 工作原理

⚙️ 一、通知到 MethodInterceptor 的转换机制 Spring AOP 通过适配器模式将开发者定义的注解型通知&#xff08;如 Before&#xff09;统一转换为 MethodInterceptor 接口实现&#xff0c;确保所有通知类型能接入同一调用链。以下是转换细节&#xff1a; 1. 适配器实现原理 核…
阅读更多...
PPO原论文阅读

PPO原论文阅读

一、Introduction1.目前存在的问题&#xff1a;(deep)Q-learning:在一些简单问题上表现不佳&#xff0c;可理解性差基础的policy gradient算法&#xff1a;&#xff08;如REINFORCE&#xff09;鲁棒性差&#xff0c;需要大量数据TRPO&#xff1a;复杂&#xff0c;在包含噪音&am…
阅读更多...
零基础也能创作专属歌曲：文心一言+蘑兔AI协同教程

零基础也能创作专属歌曲：文心一言+蘑兔AI协同教程

在AI技术飞速发展的今天&#xff0c;音乐创作已不再是专业音乐人的专属领域。通过文心一言与蘑兔AI的协同使用&#xff0c;即使没有音乐基础&#xff0c;也能轻松完成从歌词创作到作曲编曲的全流程。本文将详细拆解操作步骤&#xff0c;助你快速上手&#xff0c;实现音乐创作梦…
阅读更多...
图论:搜索问题

图论:搜索问题

提到图论中的搜索问题&#xff0c;首先想到的也就是DFS和BFS了&#xff0c;而提到这两种搜索&#xff0c;那么最典型的题目就是岛屿问题了&#xff0c;下面就练习几道相关的题目&#xff0c;为之后的更深奥的图论学习打下基础&#xff01; 孤岛的总面积 题目链接&#xff1a;…
阅读更多...
AI驱动攻防升级，API安全走到关键档口

AI驱动攻防升级，API安全走到关键档口

在数字化转型与AI技术快速发展的双重驱动下&#xff0c;API已成为企业业务与外部世界连接的神经中枢。然而&#xff0c;随着API的深度应用&#xff0c;针对API的攻击规模与复杂性也在持续升级。 API为何频频成为黑客重点盯防的突破口&#xff1f;企业常见的API防护手段是否还能…
阅读更多...
网络基础DAY18-动态路由协议基础

网络基础DAY18-动态路由协议基础

动态路由协议基础知识回顾&#xff1a;1.什么是路由&#xff1f; 答&#xff1a;是三层设备转发IP报文的路径信息。 2.路由有哪些来源&#xff1f; 答&#xff1a;1.直连路由2.静态路由3.动态路由 3.有直连路由的条件&#xff1f; 答&#xff1a;1.二层和三层物理接口状态为UP …
阅读更多...
axios统一封装规范管理

axios统一封装规范管理

新建/api/ 1.新建统一处理文件/api/axios.ts import axios from "axios"const http axios.create({baseURL: import.meta.env.VITE_API_BASE_URL, // 从环境变量读取timeout: 10000, });// 请求拦截器&#xff08;如添加 Token&#xff09; http.interceptors.reque…
阅读更多...
Java学习第七十四部分——Elasticsearch（ES）

Java学习第七十四部分——Elasticsearch（ES）

目录 一、前言提要 二、核心特性 三、应用场景 四、主要优势 五、集成方式 六、基础操作 七、高级特性 八、概念类比——与关系型数据库 九、简单示例——实现存储与搜索 十、生态集成——基于Spring Data Elasticsearch 十一、性能优化建议 十二、总结归纳概述 一…
阅读更多...
最新文章

Copyright @ 2022~2023