MySQL 事务与锁机制

一、事务基础

1.1 什么是事务

事务（Transaction）是数据库操作的最小工作单元，是作为单个逻辑工作单元执行的一系列操作。事务要么完全执行，要么完全不执行。

-- 经典转账案例
-- A 向 B 转账 100 元

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE user_id = 'B';

COMMIT;

-- 如果发生错误
ROLLBACK;

1.2 ACID 特性

特性	英文	说明	实现机制
原子性	Atomicity	事务中的操作要么全部成功，要么全部失败	Undo Log
一致性	Consistency	事务前后数据库状态保持一致	其他三个特性共同保证
隔离性	Isolation	并发事务之间相互隔离	锁 + MVCC
持久性	Durability	事务提交后，数据永久保存	Redo Log

1.3 事务操作语法

-- 开启事务
START TRANSACTION;
-- 或者
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT sp1;
SAVEPOINT sp2;

-- 回滚到保存点
ROLLBACK TO sp1;

-- 释放保存点
RELEASE SAVEPOINT sp1;

-- 查看自动提交状态
SHOW VARIABLES LIKE 'autocommit';

-- 关闭自动提交
SET autocommit = 0;

二、事务隔离级别

2.1 并发问题

问题	说明	示例
脏读	读取到其他事务未提交的数据	事务 A 读到事务 B 未提交的修改
不可重复读	同一事务内多次读取同一数据结果不同	两次读之间有其他事务修改了数据
幻读	同一事务内多次查询结果集不同	两次查询之间有其他事务插入了数据

2.2 四种隔离级别

隔离级别从低到高：

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITTED	✓	✓	✓
READ COMMITTED	✗	✓	✓
REPEATABLE READ	✗	✗	✓
SERIALIZABLE	✗	✗	✗

InnoDB 通过 MVCC + 间隙锁 在 RR 级别也可以避免幻读

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

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 在配置文件中设置
[mysqld]
transaction_isolation = REPEATABLE-READ

2.3 隔离级别详解

READ UNCOMMITTED（读未提交）

-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 结果: 1000

-- 会话2
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;  -- 未提交

-- 会话1 再次查询
SELECT balance FROM accounts WHERE id = 1;  -- 结果: 500 (脏读!)

-- 会话2 回滚
ROLLBACK;

-- 会话1 读到了一个不存在的数据

READ COMMITTED（读已提交）

-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 结果: 1000

-- 会话2
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;  -- 提交

-- 会话1 再次查询
SELECT balance FROM accounts WHERE id = 1;  -- 结果: 500 (不可重复读!)

-- 同一事务内，两次读取结果不同

REPEATABLE READ（可重复读）- MySQL 默认

-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 结果: 1000

-- 会话2
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- 会话1 再次查询
SELECT balance FROM accounts WHERE id = 1;  -- 结果: 1000 (可重复读!)

-- 但是...
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;  -- 结果: 600 (当前读)

SERIALIZABLE（串行化）

-- 会话1
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;  -- 自动加读锁

-- 会话2
START TRANSACTION;
UPDATE accounts SET balance = 500 WHERE id = 1;  -- 阻塞!等待会话1提交

-- 所有操作串行执行，性能最差但最安全

三、MVCC 多版本并发控制

3.1 MVCC 概述

MVCC（Multi-Version Concurrency Control）是 InnoDB 实现事务隔离的重要机制，通过保存数据的多个版本来实现并发读写。

核心思想：读不加锁，读写不冲突

3.2 隐藏列

InnoDB 每行数据都有三个隐藏列：

id(主键)	name(数据列)	DB_TRX_ID (事务 ID)	DB_ROLL_PTR(回滚指针)
1	Tom	100	0x12345
2	Jerry	101	0x12346

• DB_TRX_ID：最后修改该行的事务 ID
• DB_ROLL_PTR：指向 Undo Log 中该行的前一个版本
• DB_ROW_ID：如果没有主键，InnoDB 会生成一个隐藏的行 ID

3.3 Undo Log 版本链

当前版本: id=1, name='Tom_v3', trx_id=103
                    │
                    │ roll_ptr
                    ▼
Undo Log: id=1, name='Tom_v2', trx_id=102
                    │
                    │ roll_ptr
                    ▼
Undo Log: id=1, name='Tom_v1', trx_id=101
                    │
                    │ roll_ptr
                    ▼
Undo Log: id=1, name='Tom', trx_id=100
                    │
                    ▼
                  NULL

3.4 ReadView（读视图）

ReadView 包含四个关键属性：

┌─────────────────────────────────────────────────────────────┐
│                        ReadView                             │
├─────────────────────────────────────────────────────────────┤
│ creator_trx_id：创建该 ReadView 的事务ID                     │
│ m_ids：创建 ReadView 时活跃的事务ID列表                       │
│ min_trx_id：m_ids 中的最小值                                 │
│ max_trx_id：创建 ReadView 时系统应该分配的下一个事务ID         │
└─────────────────────────────────────────────────────────────┘

可见性判断规则：

如果 trx_id == creator_trx_id：
    可见（自己的修改）

如果 trx_id < min_trx_id：
    可见（事务在创建 ReadView 之前已提交）

如果 trx_id >= max_trx_id：
    不可见（事务在创建 ReadView 之后开启）

如果 min_trx_id <= trx_id < max_trx_id：
    如果 trx_id 在 m_ids 中：不可见（事务还未提交）
    如果 trx_id 不在 m_ids 中：可见（事务已提交）

3.5 RC 与 RR 的区别

READ COMMITTED (RC):
- 每次 SELECT 都创建新的 ReadView
- 因此可以读到其他事务已提交的数据

REPEATABLE READ (RR):
- 只在第一次 SELECT 时创建 ReadView
- 后续 SELECT 复用同一个 ReadView
- 因此整个事务内读到的数据一致

-- 模拟 RR 级别的 MVCC

-- T1 时刻：事务A开始，创建 ReadView
-- m_ids = [100], min_trx_id = 100, max_trx_id = 101

-- T2 时刻：事务B (trx_id=101) 修改数据并提交
-- 数据版本链：name='Jerry' (trx_id=101) -> name='Tom' (trx_id=99)

-- T3 时刻：事务A 查询
-- trx_id=101 >= max_trx_id=101，不可见
-- 沿版本链找到 trx_id=99 < min_trx_id=100，可见
-- 结果：name='Tom'

-- T4 时刻：事务A 再次查询，复用 ReadView，结果仍是 name='Tom'

3.6 快照读与当前读

-- 快照读（Snapshot Read）：读取 MVCC 版本链中的数据
SELECT * FROM users WHERE id = 1;

-- 当前读（Current Read）：读取最新版本的数据，并加锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;      -- 排他锁
SELECT * FROM users WHERE id = 1 FOR SHARE;       -- 共享锁
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;  -- 共享锁

-- INSERT、UPDATE、DELETE 都是当前读
INSERT INTO users (id, name) VALUES (1, 'Tom');
UPDATE users SET name = 'Jerry' WHERE id = 1;
DELETE FROM users WHERE id = 1;

四、InnoDB 锁机制

4.1 锁的分类

按粒度分：
├── 全局锁（Global Lock）
├── 表级锁（Table Lock）
└── 行级锁（Row Lock）
    ├── 记录锁（Record Lock）
    ├── 间隙锁（Gap Lock）
    └── 临键锁（Next-Key Lock）

按模式分：
├── 共享锁（S Lock / Shared Lock）
└── 排他锁（X Lock / Exclusive Lock）

按意向分：
├── 意向共享锁（IS Lock）
└── 意向排他锁（IX Lock）

按算法分：
├── 记录锁（Record Lock）
├── 间隙锁（Gap Lock）
└── 临键锁（Next-Key Lock）

4.2 全局锁

-- 加全局读锁
FLUSH TABLES WITH READ LOCK;

-- 释放锁
UNLOCK TABLES;

-- 使用场景：全库逻辑备份
-- 问题：阻塞所有写操作

-- 推荐：使用 mysqldump 的 --single-transaction
mysqldump --single-transaction -uroot -p database > backup.sql
-- 利用 MVCC 实现一致性备份，不需要全局锁

4.3 表级锁

-- 表锁
LOCK TABLES users READ;   -- 读锁
LOCK TABLES users WRITE;  -- 写锁
UNLOCK TABLES;

-- 元数据锁（MDL）- 自动加锁
-- 对表进行 CRUD 时自动加 MDL 读锁
-- 对表结构进行修改时自动加 MDL 写锁

-- 查看 MDL 锁
SELECT * FROM performance_schema.metadata_locks;

-- 意向锁（Intention Lock）
-- 在加行锁之前，自动加对应的意向锁
-- IS：意向共享锁
-- IX：意向排他锁

4.4 行级锁

记录锁（Record Lock）

-- 锁定一条记录
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 锁的是索引记录
-- 如果没有索引，会升级为表锁

间隙锁（Gap Lock）

-- 锁定索引记录之间的"间隙"
-- 目的：防止幻读

-- 假设表中有 id = 1, 5, 10 三条记录
-- 间隙为：(-∞, 1), (1, 5), (5, 10), (10, +∞)

-- 事务A
SELECT * FROM users WHERE id > 5 AND id < 10 FOR UPDATE;
-- 锁定 (5, 10) 这个间隙

-- 事务B
INSERT INTO users (id, name) VALUES (7, 'Test');
-- 阻塞！因为 7 在 (5, 10) 间隙内

临键锁（Next-Key Lock）

-- 临键锁 = 记录锁 + 间隙锁
-- 锁定记录及其前面的间隙

-- 假设表中有 id = 1, 5, 10 三条记录
-- Next-Key Lock 锁定范围：(-∞, 1], (1, 5], (5, 10], (10, +∞)

-- 事务A
SELECT * FROM users WHERE id = 5 FOR UPDATE;
-- 锁定 (1, 5] 这个范围

-- InnoDB 在 RR 级别默认使用 Next-Key Lock
-- 某些情况下会退化为记录锁或间隙锁

4.5 锁的兼容性

锁类型	共享锁	排他锁	意向共享锁	意向排他锁
共享锁	✓	✗	✓	✗
排他锁	✗	✓	✗	✗
意向共享锁	✓	✗	✓	✗
意向排他锁	✗	✗	✗	✓

4.6 加锁规则

-- 1. 唯一索引等值查询，记录存在：记录锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- 只锁 id=1 这条记录

-- 2. 唯一索引等值查询，记录不存在：间隙锁
SELECT * FROM users WHERE id = 3 FOR UPDATE;  -- 假设 id=3 不存在
-- 锁定 (1, 5) 间隙

-- 3. 唯一索引范围查询：临键锁
SELECT * FROM users WHERE id >= 5 FOR UPDATE;
-- 锁定 [5, +∞)

-- 4. 非唯一索引等值查询：临键锁 + 间隙锁
SELECT * FROM users WHERE age = 20 FOR UPDATE;
-- 锁定所有 age=20 的记录及相邻间隙

-- 5. 非唯一索引范围查询：临键锁
SELECT * FROM users WHERE age > 20 FOR UPDATE;

4.7 查看锁信息

-- 查看当前锁情况（MySQL 8.0+）
SELECT * FROM performance_schema.data_locks;

-- 查看锁等待
SELECT * FROM performance_schema.data_lock_waits;

-- 查看事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待详情
SELECT
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- 或使用 sys 库
SELECT * FROM sys.innodb_lock_waits\G

五、死锁

5.1 什么是死锁

死锁是指两个或多个事务相互持有对方需要的锁，导致所有事务都无法继续执行。

死锁可视化说明：

• 🔒 TransactionA：持有 row1 的锁，尝试获取 row2 的锁
• 🔒 TransactionB：持有 row2 的锁，尝试获取 row1 的锁
• 💀 死锁：两个事务相互等待，形成循环等待
• MySQL 会自动检测死锁并回滚其中一个事务来解除死锁

5.2 死锁示例

-- 会话1
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- 锁定 id=1

-- 会话2
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;  -- 锁定 id=2

-- 会话1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- 等待 id=2

-- 会话2
UPDATE accounts SET balance = balance + 100 WHERE id = 1;  -- 等待 id=1
-- 死锁！InnoDB 检测到死锁，回滚其中一个事务
-- ERROR 1213 (40001): Deadlock found when trying to get lock

5.3 死锁检测与处理

-- InnoDB 死锁检测参数
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- 死锁检测默认开启
-- 检测到死锁后，回滚持有最少行级排他锁的事务

-- 禁用死锁检测（高并发场景可考虑）
SET GLOBAL innodb_deadlock_detect = OFF;
-- 此时需要设置锁等待超时
SET GLOBAL innodb_lock_wait_timeout = 5;  -- 5秒超时

-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS\G
-- 查找 LATEST DETECTED DEADLOCK 部分

5.4 死锁日志分析

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 10, OS thread handle 123456, query id 100 localhost root updating
UPDATE accounts SET balance = balance + 100 WHERE id = 2
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`accounts`

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 11, OS thread handle 123457, query id 101 localhost root updating
UPDATE accounts SET balance = balance + 100 WHERE id = 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`accounts`
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`accounts`

*** WE ROLL BACK TRANSACTION (1)

5.5 避免死锁的方法

-- 1. 按固定顺序访问表和行
-- 不好的写法：
-- 事务A: UPDATE t1 SET ... WHERE id = 1; UPDATE t2 SET ... WHERE id = 1;
-- 事务B: UPDATE t2 SET ... WHERE id = 1; UPDATE t1 SET ... WHERE id = 1;

-- 好的写法：都先访问 t1，再访问 t2
-- 事务A: UPDATE t1 SET ...; UPDATE t2 SET ...;
-- 事务B: UPDATE t1 SET ...; UPDATE t2 SET ...;


-- 2. 一次性锁定所有需要的资源
SELECT * FROM accounts WHERE id IN (1, 2) FOR UPDATE ORDER BY id;
-- 按 id 排序后加锁


-- 3. 降低隔离级别
-- RC 级别下间隙锁会减少，死锁概率降低
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;


-- 4. 为表添加合理的索引
-- 避免全表扫描导致锁定过多行


-- 5. 减小事务粒度，缩短事务时间
START TRANSACTION;
-- 只包含必要的操作
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
-- 不要在事务中做耗时操作


-- 6. 使用乐观锁代替悲观锁
UPDATE products SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;
-- 根据影响行数判断是否成功

六、事务实践建议

6.1 事务设计原则

-- 1. 事务尽可能短小
-- 不好：
START TRANSACTION;
-- 大量业务逻辑
-- 调用外部接口
-- 长时间处理
COMMIT;

-- 好：
-- 业务逻辑处理
-- 调用外部接口
START TRANSACTION;
-- 只做数据库操作
COMMIT;


-- 2. 避免大事务
-- 不好：一次更新 100 万行
UPDATE users SET status = 0 WHERE last_login < '2020-01-01';

-- 好：分批更新
SET @batch_size = 1000;
REPEAT
    START TRANSACTION;
    UPDATE users SET status = 0
    WHERE last_login < '2020-01-01' AND status != 0
    LIMIT @batch_size;
    SET @affected = ROW_COUNT();
    COMMIT;
UNTIL @affected = 0 END REPEAT;


-- 3. 正确处理事务回滚
START TRANSACTION;
-- 操作1
-- 操作2
-- 如果失败
ROLLBACK;
-- 如果成功
COMMIT;

6.2 隔离级别选择

隔离级别	适用场景
READ UNCOMMITTED	几乎不使用
READ COMMITTED	对一致性要求不高的报表查询
REPEATABLE READ	默认级别，适合大多数 OLTP 场景
SERIALIZABLE	对一致性要求极高的场景，如金融核心

6.3 锁优化建议

-- 1. 尽量使用主键或唯一索引查询
-- 可以避免间隙锁
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- 2. 避免在事务中使用 SELECT ... FOR UPDATE 查询大量数据
-- 不好：
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

-- 好：先查询，再针对性加锁
SELECT id FROM orders WHERE status = 'pending';
-- 在应用中处理
SELECT * FROM orders WHERE id = 123 FOR UPDATE;


-- 3. 使用 LIMIT 减少锁定行数
SELECT * FROM orders WHERE user_id = 1 FOR UPDATE LIMIT 1;


-- 4. 考虑使用乐观锁
-- 适合读多写少的场景
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 10;

七、实战案例

7.1 库存扣减

-- 方案1：悲观锁
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- 检查库存
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;


-- 方案2：乐观锁
-- 不需要显式事务
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND stock > 0 AND version = @old_version;
-- 检查影响行数，0 表示库存不足或版本冲突


-- 方案3：CAS 更新
UPDATE products
SET stock = stock - 1
WHERE id = 1 AND stock > 0;
-- 检查影响行数

7.2 转账操作

-- 安全的转账事务
START TRANSACTION;

-- 按 id 顺序锁定账户，避免死锁
SELECT * FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

-- 检查余额
SET @balance = (SELECT balance FROM accounts WHERE id = 1);
IF @balance < 100 THEN
    ROLLBACK;
    -- 余额不足
ELSE
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
END IF;

7.3 防止重复提交

-- 使用唯一索引防止重复
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) UNIQUE,
    ...
);

-- 插入时会因唯一索引冲突失败
INSERT INTO orders (order_no, ...) VALUES ('ORD202401010001', ...);
-- 如果已存在，返回错误


-- 或使用 INSERT IGNORE
INSERT IGNORE INTO orders (order_no, ...) VALUES ('ORD202401010001', ...);
-- 如果已存在，静默失败

八、总结

事务核心要点

1. ACID 特性：原子性、一致性、隔离性、持久性
2. 隔离级别：从 RU 到 SERIALIZABLE，隔离性递增，性能递减
3. MVCC：通过版本链和 ReadView 实现无锁读

锁机制要点

1. 锁粒度：行锁粒度最细，并发度最高
2. 锁类型：记录锁、间隙锁、临键锁各有用途
3. 死锁处理：InnoDB 自动检测并回滚

最佳实践

1. 事务尽可能短小
2. 使用合适的隔离级别
3. 合理设计索引减少锁范围
4. 按固定顺序访问资源避免死锁
5. 根据业务场景选择乐观锁或悲观锁