Menu

PostgreSQL 事务:BEGIN、COMMIT 与 ROLLBACK

PostgreSQL 事务是怎样工作的:BEGIN、COMMIT 和 ROLLBACK,保存点,出错时打开的事务会怎样,以及各种隔离级别。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

事务把多条语句组合在一起,让它们要么全部生效,要么全部不生效。用 BEGIN 开始事务,用 COMMIT 让修改永久生效,或者用 ROLLBACK 撤销 BEGIN 之后的所有修改。在提交之前,其他会话看不到你的修改。

最后 Alice 剩 70,Bob 有 80。如果服务器在两次更新之间崩溃,两次更新都不会保留:钱不可能离开一个账户却没有到达另一个账户。

用 ROLLBACK 撤销修改

ROLLBACK 会丢弃 BEGIN 之后做的所有修改:

DELETE 在事务中删掉了全部三行,回滚又把它们恢复了:计数是 3。在 psql 中,手动执行有风险的 UPDATE 或 DELETE 时也可以这样包起来。在 BEGIN 之后运行它,用 SELECT 检查结果,再决定 COMMIT 还是 ROLLBACK。

没有 BEGIN 时,PostgreSQL 把每条语句放在各自的事务中运行,执行完就提交(自动提交)。驱动的行为各不相同:Python 的 psycopg 会在第一条语句时开启事务,并等待 conn.commit()。

用 SAVEPOINT 回滚事务的一部分

保存点在事务内部标记一个位置。ROLLBACK TO SAVEPOINT name 只撤销这个位置之后的工作,事务会继续:

日志中有“import users”和“import products”。导入订单的步骤被撤销了,而第一次插入没有丢失。RELEASE SAVEPOINT name 用来释放不再需要的保存点。

解决“current transaction is aborted”

当事务中的某条语句失败时,PostgreSQL 会把整个事务标记为失败,在你结束它之前拒绝执行后续的所有语句:

BEGIN;
INSERT INTO accounts VALUES ('alice', 10);   -- duplicate key: error
UPDATE accounts SET balance = 0;
-- ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;

解决办法是 ROLLBACK。对已中止的事务执行 COMMIT 不会保存任何东西;PostgreSQL 会回复 ROLLBACK。如果想在预期的错误之后继续执行,就在有风险的语句之前设置保存点,出错时回滚到它。驱动和 ORM 提供的“嵌套事务”就是这样实现的。

表结构的修改也能回滚

CREATE TABLE、ALTER TABLE、DROP TABLE 以及大多数其他 DDL 都是事务性的,所以中途失败的迁移不会留下任何痕迹:

to_regclass 返回 NULL:对其他人来说,这张表从来没有存在过。少数命令拒绝在事务块中运行,其中包括 VACUUM、CREATE DATABASE 和 CREATE INDEX CONCURRENTLY。

选择隔离级别

隔离级别决定一个事务能看到其他会话的哪些提交。用 BEGIN ISOLATION LEVEL ... 设置。

级别每条语句看到的数据适用场景
READ COMMITTED(默认)语句开始之前已提交的数据大多数应用
REPEATABLE READ事务第一条查询时拍下的一个快照必须保持一致的报表和导出
SERIALIZABLE和 REPEATABLE READ 一样,而且如果事务的结果可能和逐个依次执行时不同,PostgreSQL 会取消它先读后写的业务规则

READ UNCOMMITTED 可以使用,但行为和 READ COMMITTED 一样:PostgreSQL 从不显示未提交的数据。在 REPEATABLE READ 和 SERIALIZABLE 下,要预料到 SQLSTATE 为 40001(“could not serialize access”)的错误,遇到时重试整个事务。

锁住即将修改的行

为了防止其他会话在你读取和写入之间修改某一行,在读取时用 FOR UPDATE 锁住它。其他写入方会等到你提交为止:

BEGIN;
SELECT balance FROM accounts WHERE name = 'alice' FOR UPDATE;
UPDATE accounts SET balance = balance - 30 WHERE name = 'alice';
COMMIT;

让事务尽量短

打开的事务在结束之前一直持有它的行锁,还会阻止 VACUUM 清除旧的行版本。一个崩溃的脚本留下的“idle in transaction”会话,可能会把其他写入方阻塞好几个小时。设置 idle_in_transaction_session_timeout 可以自动结束这种会话。

在事务中,now() 对每条语句都返回同一个时间:事务开始的时间(见日期函数)。官方事务教程中有更多示例。

常见问题

PostgreSQL 默认自动提交吗?

是的。在 BEGIN 之外发送的语句会在自己的事务中运行,执行完就提交。有些驱动的行为正好相反:Python 的 psycopg 会替你开启事务并等待 commit(),除非你打开了自动提交。

PostgreSQL 默认的隔离级别是什么?

READ COMMITTED。每条语句看到的是该语句开始之前已经提交的数据。如果一个事务必须看到同一个一致的快照,就用 BEGIN ISOLATION LEVEL REPEATABLE READ 或 SERIALIZABLE。

PostgreSQL 中能回滚 CREATE TABLE 或 ALTER TABLE 吗?

能。PostgreSQL 中几乎所有 DDL 都是事务性的,所以事务中的 CREATE、ALTER 或 DROP 在 ROLLBACK 时都会消失。少数命令,比如 CREATE DATABASE、VACUUM 和 CREATE INDEX CONCURRENTLY,根本不能在事务块中运行。

PostgreSQL 中 COMMIT 和 END 有什么区别?

没有区别。END 是 PostgreSQL 的扩展,作用和 COMMIT 相同,而 START TRANSACTION 是 BEGIN 的标准写法。

Coddy 编程语言插图

和 Coddy 一起学编程

开始