第 8.4 课 · 阅读时间：约 10 分钟

SQL 事务是数据库作为一个整体执行的一组语句：要么全部生效，要么全部不生效。在本课中，你将学习 START TRANSACTION、COMMIT 和 ROLLBACK，了解 SAVEPOINT 如何撤销事务的一部分、出错时会发生什么，以及当多个用户修改同一份数据时，隔离级别能防止哪些问题。

SQL 事务：COMMIT、ROLLBACK 与隔离级别

在前面的课程中，你用 INSERT、UPDATE 和 DELETE 修改数据，一次一条语句。实际操作通常需要多条语句：在 Sakila 中出租一部电影，意味着要在 rental 中添加一行，同时在 payment 中添加一行。如果第二条语句失败，第一条的结果就不能单独留下。

SQLtest 上的示例数据库是只读的，所以在那里这些语句会返回权限错误。示例使用 Sakila 的表结构，并已在 MySQL 8.0 和 PostgreSQL 17 上验证过。

什么是事务？

事务把多个修改变成一个操作。数据库保证四个 ACID 特性，你已经在第 1.3 课中见过它们：

• 原子性（Atomicity）： 事务中的所有语句要么全部生效，要么都不生效。
• 一致性（Consistency）： 事务结束后，所有约束仍然成立。
• 隔离性（Isolation）： 并发事务看不到彼此尚未完成的修改。
• 持久性（Durability）： 修改一旦提交，即使发生故障或断电也不会丢失。

经典的例子是转账：从一个账户扣款，再给另一个账户加款。没有事务的话，两步之间一旦出故障，钱就会凭空消失。

START TRANSACTION、COMMIT 和 ROLLBACK

START TRANSACTION;

-- statements that change data

COMMIT;    -- save all changes
-- or
ROLLBACK;  -- undo all changes

• START TRANSACTION（或 BEGIN）开启一个事务。
• COMMIT 使事务中的所有修改永久生效，并对其他用户可见。
• ROLLBACK 撤销从事务开始以来所做的全部修改。

不同数据库的关键字略有不同：

示例：出租电影并收款

START TRANSACTION;

INSERT INTO rental (rental_date, inventory_id, customer_id, staff_id)
VALUES ('2005-08-24 10:00:00', 1, 1, 1);

INSERT INTO payment (customer_id, staff_id, rental_id, amount, payment_date)
VALUES (1, 1, LAST_INSERT_ID(), 0.99, '2005-08-24 10:00:00');

COMMIT;

结果：两行一起保存。在 MySQL 中，LAST_INSERT_ID() 返回刚刚生成的 rental_id；PostgreSQL 用 INSERT ... RETURNING rental_id 实现同样的目的。

示例：撤销错误

START TRANSACTION;

UPDATE film
SET rental_rate = rental_rate * 2;

SELECT SUM(rental_rate) FROM film;  -- the new prices, visible inside the transaction

ROLLBACK;

结果：ROLLBACK 之后，所有价格都恢复原样。在 COMMIT 之前，其他用户看不到翻倍后的价格（下文介绍的 READ UNCOMMITTED 级别除外）。

自动提交：每条语句都是一个事务

如果你没有开启事务，数据库仍然会使用事务：每条语句在自己的事务中执行并自动提交。这种模式称为自动提交（autocommit），在 MySQL、PostgreSQL 和 SQL Server 中默认开启。

SELECT @@autocommit;  -- MySQL: 1 means autocommit is on

因此，没有 START TRANSACTION 的单条 UPDATE 无法用 ROLLBACK 撤销：它在执行完成的那一刻就已经提交了。START TRANSACTION 会暂停自动提交，直到 COMMIT 或 ROLLBACK。

SAVEPOINT：撤销事务的一部分

保存点（savepoint）在事务内部标记一个位置。ROLLBACK TO SAVEPOINT 只撤销该位置之后的修改，事务仍然保持打开状态。

START TRANSACTION;

UPDATE film SET rental_rate = 1.99 WHERE film_id = 1;

SAVEPOINT before_second;

UPDATE film SET rental_rate = 9.99 WHERE film_id = 2;

ROLLBACK TO SAVEPOINT before_second;

COMMIT;

结果：电影 1 获得新价格 1.99；对电影 2 的修改被撤销，它保留原来的价格。

保存点在较长的操作中很有用：某一步可能失败，而你希望重试这一步，又不丢失之前已经完成的工作。

语句执行失败时会发生什么

在这一点上，不同数据库的行为不同，这也是常见的错误来源。

MySQL 和 MariaDB 只撤销失败的那条语句。事务仍然打开，COMMIT 会保存所有执行成功的语句：

START TRANSACTION;

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (1, 1, 2.99, '2005-08-24 10:00:00');   -- succeeds

INSERT INTO payment (customer_id, staff_id, amount, payment_date)
VALUES (999, 1, 4.99, '2005-08-24 10:00:00'); -- fails: customer 999 does not exist

COMMIT;

MySQL 中的结果：第二条插入因外键错误而失败，但 COMMIT 仍然保存了第一笔付款。要实现“全有或全无”，应用程序必须自己检查错误并执行 ROLLBACK。

PostgreSQL 在出错后会把整个事务标记为失败。之后的每条语句都会被拒绝，并提示 "current transaction is aborted"，而 COMMIT 实际上执行的是回滚：什么都不会保存。

会隐式提交的语句

在 MySQL、MariaDB 和 Oracle 中，CREATE TABLE、ALTER TABLE、DROP TABLE 等 DDL 语句在执行前会提交当前事务。之后的 ROLLBACK 无法撤销在它们之前所做的修改：

START TRANSACTION;

UPDATE film SET rental_rate = 5.99 WHERE film_id = 1;

CREATE TABLE film_backup (film_id INT);  -- implicit COMMIT here

ROLLBACK;  -- too late: the UPDATE is already saved

MySQL 中的结果：新价格 5.99 保留了下来。PostgreSQL 和 SQL Server 在事务内部执行 DDL，因此在那里 ROLLBACK 会同时撤销 UPDATE 和 CREATE TABLE。

并发问题：脏读、不可重复读和幻读

当多个事务同时处理相同的行时，一个事务可能会看到处于不该看到状态的数据。SQL 标准定义了三种读异常：

脏读的过程：

事务隔离级别

隔离级别决定事务能够避免上述哪些异常。隔离越强，意外越少，但事务之间相互等待的时间也越多。

默认级别取决于数据库：

• MySQL 和 MariaDB（InnoDB）： REPEATABLE READ。普通的 SELECT 读取的是事务第一次读取时建立的快照，因此实际上也看不到幻读。
• PostgreSQL、SQL Server、Oracle： READ COMMITTED。PostgreSQL 把 READ UNCOMMITTED 当作 READ COMMITTED 处理，所以那里永远不会出现脏读；Oracle 只支持 READ COMMITTED 和 SERIALIZABLE。

修改隔离级别：

-- for the next transaction only
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- for all following transactions of this connection (MySQL)
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

在 PostgreSQL 中，SET TRANSACTION ISOLATION LEVEL ... 要紧跟在 BEGIN 之后。在 SERIALIZABLE 级别下，数据库可能会以序列化错误取消两个冲突事务中的一个，这时应用程序应该重试它。

实用规则

• 保持事务简短：事务打开期间会持有锁，其他用户需要等待。绝不要在事务内部等待用户输入。
• 检查每条语句的结果，出错时执行 ROLLBACK，尤其是在 MySQL 中，它不会替你做这件事。
• 在 MySQL 中不要把 DDL 和数据事务混在一起：DDL 会隐式提交。
• 两个事务以相反的顺序锁定相同的行，可能会互相阻塞：这就是死锁（deadlock）。数据库会取消其中一个，应用程序应该重试它。

本课要点：

• 事务把语句组合成一个整体：COMMIT 保存全部语句的结果，ROLLBACK 撤销全部修改。
• 没有显式事务时，每条语句都会立即提交（自动提交）。
• SAVEPOINT 和 ROLLBACK TO SAVEPOINT 只撤销事务的一部分。
• 出错后，MySQL 会让事务保持打开，而 PostgreSQL 会中止事务；在 MySQL 中要自己执行 ROLLBACK。
• 在 MySQL 中，DDL 语句会隐式提交当前事务。
• 隔离级别用于防止脏读、不可重复读和幻读；MySQL 默认是 REPEATABLE READ，PostgreSQL、SQL Server 和 Oracle 默认是 READ COMMITTED。

常见问题

SQL 中的事务是什么？

事务是数据库作为一个整体执行的一组 SQL 语句：要么用 COMMIT 保存全部修改，要么用 ROLLBACK 撤销全部修改。当一个操作需要多条语句，或者多个用户同时修改数据时，事务能保证数据的一致性。

COMMIT 和 ROLLBACK 有什么区别？

COMMIT 结束事务，使其修改永久生效并对其他用户可见。ROLLBACK 结束事务并撤销它所做的全部修改，把数据恢复到 START TRANSACTION 之前的状态。

COMMIT 之后还能回滚吗？

不能。COMMIT 之后修改就是永久的；要撤销它们，需要执行新的语句把数据改回去，或者使用备份。在自动提交模式下执行的语句也是如此。

默认的事务隔离级别是什么？

使用 InnoDB 的 MySQL 和 MariaDB 默认是 REPEATABLE READ；PostgreSQL、SQL Server 和 Oracle 默认是 READ COMMITTED。

面试问题

ACID 对事务意味着什么？

原子性：全有或全无。一致性：事务前后约束都成立。隔离性：并发事务看不到彼此未提交的修改。持久性：已提交的数据在故障后依然保留。

什么是脏读？哪个隔离级别可以避免它？

脏读是指读取了另一个事务尚未提交、之后可能被回滚的修改。READ COMMITTED 及所有更强的级别都可以避免脏读。

不可重复读和幻读有什么区别？

不可重复读是你已经读过的某一行的值发生了变化；幻读是用相同条件重复查询时，结果中出现了新的行。

在 MySQL 中，如果事务中的某条语句失败了会怎样？

只有这条语句被撤销；事务仍然打开，COMMIT 会保存执行成功的语句。要让整个操作具有原子性，应用程序必须捕获错误并执行 ROLLBACK。