MySQL 事务与锁机制

MySQL 事务与锁机制详解

1. 事务（Transaction）

事务是数据库操作的逻辑单元，保证一组操作要么全部成功，要么全部失败（ACID特性）。

1.1 ACID 特性

特性	说明
Atomicity（原子性）	事务是不可分割的最小单位，要么全部执行，要么全部回滚。
Consistency（一致性）	事务执行前后，数据库从一个合法状态变为另一个合法状态。
Isolation（隔离性）	多个事务并发执行时，一个事务的操作不应影响其他事务。
Durability（持久性）	事务提交后，修改永久保存，即使系统崩溃也不丢失。

1.2 事务控制语句

START TRANSACTION;  -- 或 BEGIN
INSERT INTO accounts VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- 提交事务

-- 发生错误时回滚
ROLLBACK;

1.3 事务隔离级别

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	✅	✅	✅	最低隔离级别，事务可以读取未提交的数据。
READ COMMITTED	❌	✅	✅	只能读取已提交的数据（Oracle/PostgreSQL 默认）。
REPEATABLE READ	❌	❌	✅	同一事务内多次读取相同数据结果一致（MySQL 默认）。
SERIALIZABLE	❌	❌	❌	最高隔离级别，完全串行化执行。

设置隔离级别：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

---

2. 锁（Locking）

锁用于控制并发访问，防止数据不一致。MySQL 主要支持 表锁 和 行锁。

2.1 行级锁（Row-Level Locks）

锁类型	说明	SQL 示例
共享锁（S锁）	允许其他事务读，但不能修改。	SELECT ... LOCK IN SHARE MODE
排他锁（X锁）	禁止其他事务读或写（默认 UPDATE/DELETE 自动加）。	SELECT ... FOR UPDATE
间隙锁（Gap Lock）	锁定索引记录之间的间隙，防止幻读（RR 隔离级别）。	自动应用
临键锁（Next-Key Lock）	行锁 + 间隙锁，锁定记录及前面的间隙。	自动应用

示例：

-- 事务1：加排他锁
START TRANSACTION;
SELECT * FROM orders WHERE id = 1 FOR UPDATE;  -- 锁定 id=1 的行
-- 事务2尝试修改会被阻塞
-- UPDATE orders SET amount = 100 WHERE id = 1;  -- 等待锁释放
COMMIT;

2.2 表级锁（Table-Level Locks）

锁类型	说明	SQL 示例
表共享锁（READ）	允许其他会话读，但不能写。	LOCK TABLES orders READ;
表排他锁（WRITE）	禁止其他会话读写。	LOCK TABLES orders WRITE;
全局读锁	所有表只读（用于备份）。	FLUSH TABLES WITH READ LOCK;

示例：

LOCK TABLES orders WRITE;  -- 禁止其他会话访问 orders 表
-- 执行维护操作...
UNLOCK TABLES;  -- 释放锁

2.3 意向锁（Intention Locks）

InnoDB 内部使用，用于快速检测表级锁冲突：

• 意向共享锁（IS）：事务打算在某些行上加 S 锁。
• 意向排他锁（IX）：事务打算在某些行上加 X 锁。

2.4 死锁与锁超时

• 死锁：两个事务互相等待对方释放锁，MySQL 会自动检测并回滚其中一个事务。
• 锁等待超时：innodb_lock_wait_timeout（默认 50s），超时后报错。
• NOWAIT / SKIP LOCKED（MySQL 8.0+）：

  SELECT * FROM orders FOR UPDATE NOWAIT;  -- 如果锁冲突，立即报错
  SELECT * FROM orders FOR UPDATE SKIP LOCKED;  -- 跳过被锁定的行

---

3. 事务与锁的典型问题

3.1 脏读（Dirty Read）

• 问题：事务A读取了事务B未提交的数据，B回滚后A读到的是无效数据。
• 解决：READ COMMITTED 或更高隔离级别。

3.2 不可重复读（Non-Repeatable Read）

• 问题：事务A多次读取同一数据，期间事务B修改并提交，导致A两次读取结果不同。
• 解决：REPEATABLE READ 隔离级别（InnoDB 默认）。

3.3 幻读（Phantom Read）

• 问题：事务A查询某范围数据，事务B插入新数据，A再次查询发现"幻影行"。
• 解决：SERIALIZABLE 或 REPEATABLE READ + 间隙锁（InnoDB 默认）。

---

4. 最佳实践

1. 尽量使用行锁（FOR UPDATE / LOCK IN SHARE MODE），减少锁冲突。
2. 控制事务粒度，避免长事务（减少锁持有时间）。
3. 合理选择隔离级别：
   • 高并发读写 → READ COMMITTED
   • 需要一致性读 → REPEATABLE READ
4. 避免死锁：
   • 按固定顺序访问表和行（如先 UPDATE users 再 UPDATE orders）。
   • 使用 NOWAIT 或 SKIP LOCKED 减少锁等待。

---

总结

机制	作用	示例
事务	保证操作原子性、一致性	BEGIN; ... COMMIT;
行锁	控制行级并发访问	SELECT ... FOR UPDATE
表锁	控制整表访问	LOCK TABLES ... WRITE
间隙锁	防止幻读	自动（RR 隔离级别）
死锁处理	自动检测并回滚	SHOW ENGINE INNODB STATUS