事务与锁 - SQL 数据库

01. 事务是什么、为什么需要它

事务就是把一组 SQL 操作打包成一个原子单位——要么全部成功，要么全部失败回滚。就像银行转账：扣 A 的钱和给 B 加钱是两个操作，但必须一起成功或一起失败，不能出现钱扣了但 B 没收到的情况。事务有四个特性（ACID）：原子性（要么全做要么全不做）、一致性（数据库从一种合法状态变到另一种）、隔离性（多个事务互不干扰）、持久性（提交了就永久保存不会丢）。没有事务的数据库就像没有保存按钮的文档编辑器——一崩数据就乱。

sql

-- 转账操作的正确姿势
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 确认余额没问题
SELECT balance FROM accounts WHERE id = 1;
COMMIT;
-- 出问题就 ROLLBACK;

02. 隔离级别——脏读、不可重复读、幻读

多个事务同时跑，如果不加控制就会出问题。SQL 标准定义了三种并发问题：脏读——事务 A 改了数据还没提交，事务 B 就读到了。万一 A 回滚了，B 拿到的就是不存在的数据。不可重复读——事务 A 在一次查询中多次读同一行，中间被事务 B 改了提交了，导致 A 两次读到不同的值。幻读——事务 A 按条件查了一批行，中间事务 B 插入或删除了符合条件的新行，A 再查发现多了或少了行，像幻觉一样。为应对这些问题，数据库提供了四种隔离级别：SERIALIZABLE > REPEATABLE READ > READ COMMITTED > READ UNCOMMITTED。

sql

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

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

-- MySQL 默认是 REPEATABLE READ
-- PostgreSQL 默认是 READ COMMITTED

03. 四种隔离级别详解

READ UNCOMMITTED——事务能看到别人还没提交的修改。并发性最高但一致性最差，实际项目基本不用。 READ COMMITTED——只能看到别人已提交的数据，解决了脏读。但一个事务内两次读同一条可能不一样（不可重复读）。PostgreSQL 的默认级别，适合多数 OLTP 场景。 REPEATABLE READ——同一个事务里多次读同一条记录，结果始终一样。用 MVCC 实现，读的是事务开始时的快照。MySQL InnoDB 默认用这个。InnoDB 通过间隙锁解决了幻读。 SERIALIZABLE——完全串行执行，一个接一个来。并发性最低，数据完全一致，但性能极差，只适合银行核心账务这种场景。

sql

-- READ COMMITTED 示例
-- 事务 A 读不到事务 B 还没提交的修改
-- 但 B 提交后，A 再读就能看到新值

-- REPEATABLE READ 示例
-- 事务 A 整个过程中读到的都是事务开始时的快照
-- B 提交了 A 也看不到

大多数 Web 应用用 READ COMMITTED 就够。金融系统、库存扣减这类要求高的才上 REPEATABLE READ 或更高。

04. 锁机制——行锁、表锁、间隙锁

事务并发控制靠锁。MySQL InnoDB 主要用这几类锁：行锁——锁住具体几行，粒度最细，并发性最好。UPDATE、DELETE、SELECT FOR UPDATE 会自动加行锁。表锁——整张表锁住，粒度最粗。ALTER TABLE 或显式的 LOCK TABLES 会加。间隙锁——锁住索引记录之间的空隙，防止别的事务在这些空隙插入新记录（解决幻读）。只在 REPEATABLE READ 及以上级别生效。死锁——事务 A 等 B 释放锁，B 又等 A 释放锁，互相等，系统卡死。InnoDB 会自动检测死锁并回滚其中一个事务。

sql

-- SELECT ... FOR UPDATE（显式加排他行锁）
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 检查当前锁情况
SHOW ENGINE INNODB STATUS;

SELECT FOR UPDATE 如果 WHERE 条件不走索引，会锁全表。务必确保 WHERE 列有索引。

05. MVCC——多版本并发控制

MVCC 是 InnoDB 实现高并发的核心机制。它不通过锁来保证一致性，而是每行数据保存多个版本。每个事务看到的是这个时刻的快照——你开始时候的数据样子，别人改了你暂时看不见。具体实现：每行有两个隐藏列——DB_TRX_ID（最后一次修改这行的事务 ID）和 DB_ROLL_PTR（指向回滚段的指针，存着旧版本）。事务启动时拿到一个 Read View，通过比对事务 ID 决定能不能看到某个版本的数据。 MVCC 的好处：读不阻塞写，写不阻塞读。没有 MVCC 的话，一个长事务的 SELECT 可能把一切写操作都堵住。

sql

-- MVCC 的好处
-- 事务 A: SELECT * FROM products（快照读，不锁）
-- 事务 B: UPDATE products SET price = 99 WHERE id = 1（正常写，不等 A）

-- 如果用 SELECT FOR UPDATE 就变成当前读，会锁

MVCC 的快照读不需要加锁，所以能实现很高的并发。但 UPDATE、DELETE 还是需要当前读加锁。

知识测验

第 1/5 题正确 0

ACID 里的 A 代表什么？

窗口函数