MySQL中的事务和锁

一下基于innodb引擎

一、MySQL的锁

占有模式

目的：用于读取操作。多个事务可以同时持有同一数据资源（如一行记录）的共享锁
兼容性： S 锁之间是兼容的。一个事务持有 S 锁时，不影响其他事务也来获取同一资源的 S 锁。
阻塞：
- 如果一个事务持有资源的 S 锁，其他事务请求该资源的 X 锁会被阻塞，直到 S 锁释放。
- 如果一个事务持有资源的 X 锁，其他事务请求该资源的 S 锁会被阻塞，直到 X 锁释放。

排他锁 (X Lock / Exclusive Lock):

目的：用于写入操作（INSERT, UPDATE, DELETE）。保证一个事务在修改数据时，其他事务不能读取（脏读在 RC/RR 下由 MVCC 避免，但锁层面会阻塞 S 锁）或修改同一数据。
兼容性： X 锁与任何其他锁（无论是 S 锁还是另一个 X 锁）都是不兼容的。
阻塞：
- 如果一个事务持有资源的 X 锁，其他事务请求该资源的 S 锁或 X 锁都会被阻塞，直到 X 锁释放。
- 如果一个事务请求资源的 X 锁，而该资源已被其他事务加了 S 锁或 X 锁，该请求会被阻塞，直到所有冲突锁释放。

兼容性矩阵 (简化版)

当前锁状态 \ 请求锁类型	请求 S 锁	请求 X 锁
无锁	允许	允许
持有 S 锁	允许	阻塞
持有 X 锁	阻塞	阻塞

S 锁兼容 S 锁： ✅ (允许多个读)
S 锁不兼容 X 锁： ❌ (读阻塞写，写阻塞读)
X 锁不兼容 S 锁： ❌ (写阻塞读)
X 锁不兼容 X 锁： ❌ (写阻塞写)

加锁示例

sql

SELECT * FROM table WHERE id = 3 LOCK IN SHARE MODE;

加锁类型： 共享锁 (S Lock)。
加锁范围：在 READ COMMITTED 或 REPEATABLE READ 隔离级别下，如果 id=3 是主键或唯一索引，通常只锁住 id=3 这一行。如果 id 是非唯一索引或没有索引，情况会更复杂（可能锁住满足条件的行，甚至锁范围或升级）
目的：显式地告诉数据库，这个 SELECT 语句需要读取最新的、已提交的数据版本，并且在读取期间，不允许其他事务修改这行数据（即阻塞其他事务对该行加 X 锁）。其他事务仍然可以同时对该行加 S 锁进行读取。
与普通 SELECT (快照读) 的区别：普通 SELECT 利用 MVCC 读取历史快照（在 RR 下是事务开始时的快照，在 RC 下是语句开始时的快照），不需要加任何行锁。LOCK IN SHARE MODE 则是当前读，它读取最新已提交版本并加 S 锁。

sql

SELECT * FROM table WHERE id = 3 FOR UPDATE;

加锁类型： 排他锁 (X Lock)。
加锁范围：同样，如果 id=3 是主键或唯一索引，通常只锁住 id=3 这一行。非唯一索引或无索引情况复杂。
目的：显式地告诉数据库，这个 SELECT 语句需要读取最新的、已提交的数据版本，并且在读取期间，不允许其他事务读取（FOR UPDATE/SHARE MODE）或修改这行数据（即阻塞其他事务对该行加 S 锁和 X 锁）。这是为了后续可能要基于这个查询结果进行更新操作做准备。
与普通 SELECT (快照读) 的区别：同样是当前读，但加的是更强的 X 锁。

sql

INSERT INTO table (id, name) VALUES (3, '3');

加锁类型： 排他锁 (X Lock)。
加锁范围： InnoDB 在执行 INSERT 时，会对新插入的行加上排他锁 (X Lock)。这个锁是在行被实际插入到索引中时加上的。
潜在冲突：如果另一个事务试图在相同的键值（如主键 id=3）上执行 INSERT、UPDATE 或 DELETE，或者执行 SELECT ... FOR UPDATE / SELECT ... LOCK IN SHARE MODE 定位到这一行（如果它已存在），就会发生锁冲突。INSERT 操作本身还会检查唯一键约束，这可能导致等待其他事务释放 S 锁（例如在检查唯一约束时需要读取索引记录）。

sql

UPDATE table SET name= '3' WHERE id = 3;

加锁类型： 排他锁 (X Lock)。
加锁过程：与 UPDATE 极其相似
- 定位阶段 (读取)：找到要删除的行（id=3），尝试对其加 X 锁（当前读）。
- 删除阶段 (写入)：在 X 锁保护下，标记该行为已删除（产生删除标记），旧版本进入 Undo Log。物理删除可能延迟发生（Purge 线程）。
加锁范围：被删除的行上持有 X 锁。同样，非唯一索引或无索引可能导致范围锁。

sql

DELETE FROM table WHERE id = 3;

加锁类型： 排他锁 (X Lock)。
加锁过程：与 UPDATE 极其相似。
- 位阶段 (读取)：找到要删除的行（id=3），尝试对其加 X 锁（当前读）
- 删除阶段 (写入)：在 X 锁保护下，标记该行为已删除（产生删除标记），旧版本进入 Undo Log。物理删除可能延迟发生（Purge 线程）。
加锁范围：被删除的行上持有 X 锁。同样，非唯一索引或无索引可能导致范围锁。

锁的类型

1. 行锁 (Record Lock)

作用：锁定单行记录，防止其他事务修改该行
类型：

共享锁（S Lock）：允许多个事务同时读取同一行
排他锁（X Lock）：只允许一个事务修改行

示例：

sql

-- 共享锁：多个事务可同时读取
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁：阻止其他事务读写
SELECT * FROM orders WHERE order_id = 100 FOR UPDATE;

关键特性：

依赖索引生效

使用主键索引：精准锁定单行 ✅

sql

UPDATE products SET stock=10 WHERE id=5; -- id是主键

无索引：退化为表锁 ❌

sql

UPDATE users SET status=0 WHERE phone='13800138000'; -- phone无索引

锁升级机制

当SQL扫描大量行时，可能自动升级为表锁

sql

-- 可能触发锁升级
UPDATE logs SET archived=1 WHERE create_time < '2023-01-01';

非主键索引的锁定

锁定二级索引 + 对应主键

mermaid

graph LR
A[二级索引 age=25] --> B[主键 id=101]
C[事务锁定 age=25] --> D[同时锁定 id=101]

2. 间隙锁 (Gap Lock)

作用：锁定索引值之间的"空隙"，防止幻读
范围：开区间 (10, 20) 不包括边界值

示例场景：

sql

-- 表：products (price: 10, 20, 30)
-- 事务A（RR隔离级别）
SELECT * FROM products 
WHERE price BETWEEN 15 AND 25 
FOR UPDATE; -- 锁定(10,20)和(20,30)间隙

-- 事务B尝试插入（被阻塞）
INSERT INTO products (price) VALUES (18); -- 在(10,20)间隙内

关键特性：

只在RR/Serializable级别生效
防止范围内插入新数据
兼容规则：

不同间隙：互不影响 ✅

sql

-- 事务A锁定(10,20)
-- 事务B可插入price=5（在(-∞,10)间隙）

相同间隙：互斥 ❌

sql

-- 事务A锁定(10,20)
-- 事务B无法插入price=15

3. 临键锁 (Next-Key Lock)

作用：行锁 + 间隙锁组合，InnoDB默认锁机制
范围：左开右闭区间 (10, 20]

示例：

sql

-- 表：students (score: 60,70,80,90)
-- 事务A
SELECT * FROM students 
WHERE score > 75 AND score < 85 
FOR UPDATE; -- 锁定(70,80]和(80,90]区间

-- 阻塞操作：
UPDATE students SET grade='B' WHERE score=80; -- 行记录
INSERT INTO students (score) VALUES (78);    -- 间隙插入

锁范围图示：

mermaid

graph LR
    A[60] --> B(70]
    B --> C(80]
    C --> D(90]
    style B stroke:#f66,stroke-width:2px
    style C stroke:#f66,stroke-width:2px
    锁定区域:::locked
    classDef locked fill:#f9f,stroke:#333,stroke-width:1px

4. 表锁 (Table Lock)

作用：锁定整张表，粒度最粗的锁
使用场景：

全表更新操作
备份操作
无索引的写操作

显式加锁：

sql

LOCK TABLES orders WRITE; -- 排他锁
LOCK TABLES products READ; -- 共享锁
UNLOCK TABLES; -- 释放锁

隐式加锁：

sql

-- 无索引更新触发表锁
UPDATE config SET value=1 WHERE description LIKE '%debug%';

性能影响：

并发性大幅降低 ⚠️
仅推荐在维护操作中使用

5. 意向锁 (Intention Lock)

作用：表级锁，快速判断表中是否有行锁
类型：

IS（意向共享锁）：准备加行级S锁
IX（意向排他锁）：准备加行级X锁

工作流程：

事务加行锁前，先申请表级意向锁
其他事务通过意向锁判断表级操作是否安全

兼容矩阵：

	X	IX	S	IS
X	❌	❌	❌	❌
IX	❌	✅	❌	✅
S	❌	❌	✅	✅
IS	❌	✅	✅	✅

示例：

sql

-- 事务A
SELECT * FROM accounts WHERE id=1 FOR UPDATE; 
-- 步骤：
-- 1. 加表级IX锁
-- 2. 加行级X锁

-- 事务B尝试加表锁
LOCK TABLES accounts WRITE; 
-- 检测到IX锁 → 阻塞等待

6. 死锁 (Deadlock)

本质：事务间循环等待资源
发生条件：

互斥访问
持有并等待
不可剥夺
循环等待

经典场景：

mermaid

sequenceDiagram
    participant T1 as 事务A
    participant T2 as 事务B
    T1->>T1: 锁定行R1 (UPDATE users SET... WHERE id=1)
    T2->>T2: 锁定行R2 (UPDATE orders SET... WHERE id=100)
    T1->>T2: 尝试锁定R2 (阻塞)
    T2->>T1: 尝试锁定R1 (阻塞)

解决方案：

死锁检测（默认开启）
InnoDB自动回滚代价最小的事务
sql
```
SHOW ENGINE INNODB STATUS; -- 查看死锁日志
```
1

锁超时

sql

SET innodb_lock_wait_timeout = 30; -- 设置等待超时(秒)

预防措施：

按固定顺序访问资源

python

# 正确顺序：先更新users表，再更新orders表
def update_data():
    update_user()
    update_order()

使用覆盖索引减少锁冲突
保持事务短小精悍

最佳实践总结

索引优化：确保WHERE条件使用索引
隔离级别：

读多写少：READ COMMITTED
防幻读：REPEATABLE READ

锁监控：

sql

SHOW OPEN TABLES WHERE In_use > 0; -- 查看表锁
SELECT * FROM information_schema.INNODB_LOCKS; -- 查看行锁

避免长事务：减少锁持有时间
设计原则：访问资源的顺序保持一致

通过合理使用锁机制，可在保证数据一致性的前提下，显著提升数据库并发性能。实际开发中应结合EXPLAIN分析执行计划，避免意外锁升级。

显式加锁读： LOCK IN SHARE MODE 加 S 锁 (共享读锁)，FOR UPDATE 加 X 锁 (排他写锁)。它们都是当前读，读取最新已提交数据并上锁。
隐式加锁写： INSERT、UPDATE、DELETE 在执行过程中（主要是定位和修改目标行时）隐式地对受影响的行加 X 锁。
兼容性是核心：
- S 锁只和 S 锁兼容 -> 允许多个事务并发读。
- X 锁和任何锁都不兼容 -> 保证写操作的独占性，防止脏写、不可重复读（在锁层面）和更新丢失。
隔离级别影响：上面描述主要基于 READ COMMITTED 和 REPEATABLE READ。在 REPEATABLE READ 下，InnoDB 还会使用间隙锁 (Gap Locks) 和临键锁 (Next-Key Locks) 来防止幻读，这会改变锁的范围（不仅锁记录，还锁记录之间的“间隙”），影响兼容性范围。在 READ COMMITTED 下，通常只有记录锁（行锁），没有间隙锁（除了外键检查和唯一键冲突检查等特殊情况）。
索引影响：索引类型（主键、唯一、非唯一、无索引）会极大影响锁的范围（行锁 vs 范围锁/表锁）。使用合适的索引是减少锁冲突、提高并发性的关键。

理解 S 锁和 X 锁的占有模式、兼容性以及不同 SQL 语句如何获取这些锁，是分析和解决数据库并发问题（如死锁、锁等待超时）的基础。

二、MySQL的事务

MySQL的事务是一组操作序列，这些操作要么全部执行，要么全部不执行，是数据库管理系统执行过程中的一个逻辑单位。通过事务，MySQL能够保证即使在系统或其他故障的情况下，数据库也不会处于不一致的状态。

事务的ACID特性

原子性（Atomicity） - 事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
一致性（Consistency） - 事务必须使数据库从一个一致性状态转换到另一个一致性状态。
隔离性（Isolation） - 并发执行的事务之间不能互相干扰，多个并发事务之间要相互隔离。
持久性（Durability） - 一旦事务提交，则其所做的修改将永久保存在数据库中，即使系统崩溃也不会丢失。

事务的实现原理

MySQL中，事务的实现依赖于存储引擎，不同的存储引擎对事务的支持不同。以支持事务的InnoDB存储引擎为例，其事务的实现原理包括：

Undo日志：保证事务的原子性。当事务进行中发生错误或者用户执行回滚操作时，系统可以利用Undo日志撤销已经进行的修改。
Redo日志：保证事务的持久性。即使数据库发生故障，已经提交的事务也不会丢失，因为这些修改操作会记录在Redo日志中。
锁机制：保证事务的隔离性。通过对数据加锁，防止多个事务并发执行时互相干扰。
MVCC（多版本并发控制）：也是为了保证隔离性，在InnoDB中，通过保存数据的多个版本，来允许读写操作并发进行，提高性能。隔离级别是数据库管理系统处理并发事务时的重要机制，它定义了事务间的"可见性规则"。MySQL支持四种标准隔离级别，控制着事务间的相互影响程度：

mermaid

graph LR
    A[隔离级别] --> B[读未提交 Read Uncommitted]
    A --> C[读已提交 Read Committed]
    A --> D[可重复读 Repeatable Read]
    A --> E[串行化 Serializable]
    并发性[并发性] -->|高| B
    并发性 -->|中高| C
    并发性 -->|中| D
    并发性 -->|低| E
    数据一致性 -->|低| B
    数据一致性 -->|中| C
    数据一致性 -->|高| D
    数据一致性 -->|最高| E

1. 读未提交 (Read Uncommitted)

核心特点

能看到其他事务未提交的修改（"脏读"）
隔离性最低，并发性最高
实际开发中极少使用

问题示例：脏读

sql

-- 事务A（转账操作）
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 余额减100
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 余额加100

-- 事务B（查询余额）
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 看到-100后的余额（未提交）

此时事务A如果回滚，事务B读到的就是无效的"脏数据"

适用场景

数据准确性要求极低的场景
只读的统计报表（可容忍暂时不一致）
MySQL默认不采用此级别

2. 读已提交 (Read Committed)

核心特点

只能看到已提交的数据（解决脏读）
可能发生不可重复读
Oracle默认级别，MySQL常用级别

问题示例：不可重复读

sql

-- 事务A（两次查询）
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM products WHERE id = 5; -- 第一次查询：库存=10

          -- 事务B更新并提交
          START TRANSACTION;
          UPDATE products SET stock=5 WHERE id=5;
          COMMIT;

SELECT * FROM products WHERE id = 5; -- 第二次查询：库存=5

同一事务内两次读取结果不一致（库存从10→5）

MVCC实现机制

mermaid

sequenceDiagram
    participant T as 事务
    participant V1 as 版本1(stock=10)
    participant V2 as 版本2(stock=5)
    T->>V1: 事务开始（TID=100）
    T->>V1: 第一次读取（使用TID=100）
    Note right of V2: 事务B提交新版本
    T->>V2: 第二次读取（新ReadView）
    Note left of T: 看到新提交的stock=5

适用场景

多数Web应用（用户注册、内容管理）
需要看到最新提交数据的系统
可接受同一事务内查询结果变化

3. 可重复读 (Repeatable Read)

核心特点

同一事务内多次读取结果一致（解决不可重复读）
InnoDB默认隔离级别
通过间隙锁防止幻读

防止幻读示例

sql

-- 事务A（范围查询）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM orders WHERE amount > 1000; -- 返回3条记录

          -- 事务B插入新订单
          START TRANSACTION;
          INSERT INTO orders(amount) VALUES (1500); -- 被间隙锁阻塞
          COMMIT;

SELECT * FROM orders WHERE amount > 1000; -- 仍返回3条记录
COMMIT; -- 提交后新订单才可见

MVCC+锁机制

mermaid

graph TB
    A[事务开始] --> B[创建ReadView]
    B --> C[第一次查询]
    C --> D[快照冻结]
    D --> E[后续查询使用相同ReadView]
    F[写操作] --> G[间隙锁锁定范围]
    G --> H[阻止新数据插入]

幻读与间隙锁

幻读：同一事务内相同查询返回不同行数

间隙锁解决方案：

sql

-- 表：orders (id: 101,102,105)
SELECT * FROM orders WHERE id BETWEEN 100 AND 200 FOR UPDATE;
-- 锁定范围：(负无穷,101), (101,102), (102,105), (105,正无穷)
-- 阻止在间隙中插入新记录（如id=103）

适用场景

金融交易系统（账户余额计算）
库存管理系统
需要事务内数据一致的场景

4. 串行化 (Serializable)

核心特点

完全隔离事务（最高隔离级别）
所有操作加锁，类似单线程执行
彻底解决幻读，但性能最低

工作机制

sql

-- 事务A
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts; -- 自动加共享锁

          -- 事务B尝试写入
          START TRANSACTION;
          UPDATE accounts SET balance=0; -- 被阻塞直到事务A结束

锁机制对比

操作	可重复读	串行化
SELECT	快照读（无锁）	加共享锁
SELECT...FOR UPDATE	加临键锁	加排他锁
INSERT	可能被间隙锁阻塞	一定被阻塞

适用场景

银行核心系统（账户迁移）
对数据一致性要求极高的场景
可接受低并发的系统

隔离级别对比总结

特性	读未提交	读已提交	可重复读	串行化
脏读	可能发生	不可能	不可能	不可能
不可重复读	可能发生	可能发生	不可能	不可能
幻读	可能发生	可能发生	可能（但被阻止）	不可能
性能	⭐⭐⭐⭐⭐	⭐⭐⭐⭐	⭐⭐⭐	⭐
加锁强度	最弱	中等	较强	最强
默认级别	-	Oracle	MySQL InnoDB	-

如何选择隔离级别？

优先使用默认级别（可重复读）

sql

-- 查看当前隔离级别
SELECT @@transaction_isolation;

需要最新数据 → 读已提交

sql

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

需要绝对一致 → 串行化

sql

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

性能优化建议：

避免长事务（减少锁持有时间）
使用覆盖索引（减少锁范围）
精确查询条件（避免全表扫描）

实践提示：95%场景使用默认级别即可，特殊需求再调整。金融系统建议使用可重复读+应用层校验双重保障。

通过合理选择隔离级别，可在数据一致性和系统性能间取得最佳平衡。

SpringCloud

Netty

MySQL中的事务和锁

一、MySQL的锁

占有模式

排他锁 (X Lock / Exclusive Lock):

加锁示例

锁的类型

1. 行锁 (Record Lock)

2. 间隙锁 (Gap Lock)

3. 临键锁 (Next-Key Lock)

4. 表锁 (Table Lock)

5. 意向锁 (Intention Lock)

6. 死锁 (Deadlock)

最佳实践总结

二、MySQL的事务

事务的ACID特性

事务的实现原理

1. 读未提交 (Read Uncommitted)

2. 读已提交 (Read Committed)

3. 可重复读 (Repeatable Read)

4. 串行化 (Serializable)

隔离级别对比总结

如何选择隔离级别？

MySQL中的事务和锁 ​

一、MySQL的锁 ​

占有模式 ​

共享锁 (S Lock / Share Lock)： ​

排他锁 (X Lock / Exclusive Lock): ​

加锁示例 ​

锁的类型 ​

1. 行锁 (Record Lock) ​

2. 间隙锁 (Gap Lock) ​

3. 临键锁 (Next-Key Lock) ​

4. 表锁 (Table Lock) ​

5. 意向锁 (Intention Lock) ​

6. 死锁 (Deadlock) ​

最佳实践总结 ​

二、MySQL的事务 ​

事务的ACID特性 ​

事务的实现原理 ​

1. 读未提交 (Read Uncommitted) ​

2. 读已提交 (Read Committed) ​

3. 可重复读 (Repeatable Read) ​

4. 串行化 (Serializable) ​

隔离级别对比总结 ​

如何选择隔离级别？ ​

MySQL中的事务和锁

一、MySQL的锁

占有模式

共享锁 (S Lock / Share Lock)：

排他锁 (X Lock / Exclusive Lock):

加锁示例

锁的类型

1. 行锁 (Record Lock)

2. 间隙锁 (Gap Lock)

3. 临键锁 (Next-Key Lock)

4. 表锁 (Table Lock)

5. 意向锁 (Intention Lock)

6. 死锁 (Deadlock)

最佳实践总结

二、MySQL的事务

事务的ACID特性

事务的实现原理

1. 读未提交 (Read Uncommitted)

2. 读已提交 (Read Committed)

3. 可重复读 (Repeatable Read)

4. 串行化 (Serializable)

隔离级别对比总结

如何选择隔离级别？