Files

120 lines
5.2 KiB
Markdown
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
---
id: backend.mysql.tx-locks-mvcc
title: 事务、隔离级别、MVCC 和锁(行锁、间隙锁、死锁)
type: lesson
domain: backend
stack: [mysql]
tags: [事务, 隔离级别, MVCC, 间隙锁, 死锁, undo log, redo log]
level: intermediate
prerequisites: [backend.mysql.index-explain]
est_hours: 4
related: [backend.postgresql.index-tx-mvcc, backend.mysql.interview]
week: [2, 14]
order: 2
---
# 事务、隔离级别、MVCC 和锁(行锁、间隙锁、死锁)
## 解释说明
**ACID 由谁保证**
| 特性 | InnoDB 的实现 |
| --- | --- |
| 原子性 | undo log:失败时按 undo log 回滚 |
| 持久性 | redo log:先写日志再改数据页(WAL),崩溃后按 redo log 恢复 |
| 隔离性 | MVCC + 锁 |
| 一致性 | 以上三者 + 约束共同保证 |
另外还有 **binlog**(Server 层的逻辑日志),用于主从复制和按时间点恢复。redo log 和 binlog 通过“两阶段提交”保持一致。
**隔离级别**(InnoDB 默认 **可重复读 RR**,PostgreSQL 默认读已提交)
| 级别 | 脏读 | 不可重复读 | 幻读 |
| --- | --- | --- | --- |
| 读未提交 | 可能 | 可能 | 可能 |
| 读已提交 RC | 不会 | 可能 | 可能 |
| 可重复读 RR | 不会 | 不会 | 快照读不会;当前读靠间隙锁防止 |
| 串行化 | 不会 | 不会 | 不会 |
**MVCC**:每行有隐藏的事务 ID 和回滚指针,旧版本串在 undo log 里。普通 `SELECT` 是**快照读**,按 Read View 判断哪个版本可见;RC 每条语句生成新的 Read View,RR 在事务第一次读时生成、之后一直复用。
**当前读和锁**:`SELECT ... FOR UPDATE`、`UPDATE`、`DELETE` 读的是最新版本并加锁。
- **记录锁**:锁住索引上的一条记录。
- **间隙锁**:锁住两条记录之间的“空隙”,防止别人往里插入,RR 下才有。
- **临键锁(Next-Key Lock)**:记录锁 + 前面的间隙,是 RR 下的默认加锁单位。
- **锁加在索引上**:条件列没有索引时,会扫描并锁住大量记录,效果接近锁表。
**死锁**:两个事务互相等待对方持有的锁。InnoDB 会自动检测并回滚其中一个(错误码 1213),应用要捕获并重试。
## 案例
- 两个请求同时给同一个用户扣余额:用 `UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?`,一条语句完成判断和扣减,靠行锁串行化。
- 两个事务以相反顺序更新两行(A 先改 1 再改 2,B 先改 2 再改 1)就会死锁。解决:统一按 ID 顺序加锁。
## 代码示例
两个客户端窗口复现死锁:
```sql
CREATE TABLE accounts (id INT PRIMARY KEY, balance DECIMAL(12, 2) NOT NULL) ENGINE = InnoDB;
INSERT INTO accounts VALUES (1, 100), (2, 100);
-- 窗口 A
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- 窗口 B
BEGIN;
UPDATE accounts SET balance = balance - 10 WHERE id = 2;
-- 窗口 A(等待 B 释放 id=2)
UPDATE accounts SET balance = balance + 10 WHERE id = 2;
-- 窗口 B:ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
-- 查看最近一次死锁:SHOW ENGINE INNODB STATUS\G 的 LATEST DETECTED DEADLOCK 部分
```
应用层处理死锁(Python + PyMySQL,按 ID 排序加锁 + 遇到 1213 重试):
```python
import time
import pymysql
def transfer(conn: pymysql.connections.Connection, src: int, dst: int, amount: int, retries: int = 3) -> None:
first, second = sorted((src, dst)) # 统一加锁顺序,从根源上减少死锁
for attempt in range(retries):
try:
with conn.cursor() as cur:
conn.begin()
cur.execute("SELECT id FROM accounts WHERE id IN (%s, %s) ORDER BY id FOR UPDATE", (first, second))
n = cur.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s AND balance >= %s",
(amount, src, amount))
if n == 0:
raise ValueError("余额不足")
cur.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
conn.commit()
return
except pymysql.err.OperationalError as e:
conn.rollback()
if e.args[0] != 1213 or attempt == retries - 1: # 1213 = 死锁
raise
time.sleep(0.05 * (attempt + 1))
except Exception:
conn.rollback()
raise
```
## 易错点
- **以为 RR 能防止所有并发问题**:快照读看到的是旧数据,“先查余额再扣”在 RR 下仍然会超扣,要用当前读(`FOR UPDATE`)或条件更新。
- **更新条件没有索引**:会锁住大量行,甚至阻塞全表写入。
- **大事务**:一次更新几十万行,锁持有时间长、undo 膨胀、主从延迟。分批提交。
- **死锁不重试**:死锁在高并发下无法完全避免,应用要能重试。
- **把 MySQL 的结论直接套到 PostgreSQL**:两者默认隔离级别、MVCC 实现都不同(见 [PostgreSQL 的事务课程](../postgresql/02-index-tx-mvcc.md))。
## 面试常问
见 [MySQL 面试题](interview.md):redo log / undo log / binlog 的区别、RR 怎么解决幻读、间隙锁、死锁排查。