PostgreSQL - 提交 (Commit)

PostgreSQL：使用 COMMIT、ROLLBACK 和 SAVEPOINT 管理事务

事务是关系数据库中的一个基本概念，它确保了数据完整性。事务允许您将一系列 SQL 语句分组到一个单一的、要么全部成功要么全部失败的操作中。这由 ACID 属性（原子性、一致性、隔离性、持久性）保证。

自动提交与显式事务

默认情况下，PostgreSQL 在自动提交模式下运行。这意味着您执行的每个 SQL 语句（例如 INSERT、UPDATE 或 DELETE）都被视为一个独立的事务，并在成功完成后自动提交。这对于单个操作来说很简单。

对于必须作为一个单一单元成功或失败的多步骤操作，您必须使用显式事务块。您可以使用 BEGIN 开始，并使用 COMMIT 或 ROLLBACK 结束。

核心事务命令

BEGIN：开始一个事务

BEGIN（或 START TRANSACTION）命令开始一个新的事务块。在 BEGIN 之后执行的任何语句都是此事务的一部分，并且在您提交之前对其他用户不可见。

COMMIT：保存您的更改

COMMIT 命令永久保存当前事务中进行的所有更改。一旦提交，这些更改就是持久的，并且不能通过 ROLLBACK 撤销。

COMMIT [WORK | TRANSACTION]; -- WORK 和 TRANSACTION 是可选关键字。

ROLLBACK：撤销您的更改

ROLLBACK 命令放弃当前事务中进行的所有更改，将数据库恢复到事务开始之前的状态。

实际示例：银行转账

银行转账是事务的经典示例。将 100 美元从账户 A 转账到账户 B 需要两条 UPDATE 语句。这两条语句必须全部成功，或全部失败。如果借方成功但贷方失败，资金就会丢失！

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    balance NUMERIC(10, 2) NOT NULL
);

INSERT INTO accounts (id, name, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00);

成功事务

BEGIN;

-- 1. 从 Alice 的账户扣除 100 美元
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- 2. 向 Bob 的账户存入 100 美元
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT; -- 所有更改现已永久保存。

失败事务（使用 ROLLBACK）

想象一下，我们在提交之前意识到犯了一个错误。

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 99; -- 错误：账户 99 不存在！

-- 假设我们发现了错误。我们可以手动撤销所有操作。
ROLLBACK; -- 此事务内的所有更改都将被放弃。Alice 的余额恢复到 1000。

高级控制：SAVEPOINT

对于长时间、复杂的事务，您可以设置 SAVEPOINT。这允许您回滚到事务中的特定点，而无需放弃整个事务。

BEGIN;

INSERT INTO accounts (id, name, balance) VALUES (3, 'Charlie', 200.00);
SAVEPOINT new_user_created;

-- 现在，尝试一个有风险的操作
UPDATE accounts SET balance = balance - 5000 WHERE id = 3; -- 失败检查约束（未显示）

-- 发生了一个错误。与其完全回滚，不如回到保存点。
ROLLBACK TO SAVEPOINT new_user_created;

-- Charlie 的账户仍然存在。现在我们可以尝试另一个操作。
UPDATE accounts SET balance = balance - 50 WHERE id = 3;

COMMIT; -- 提交 Charlie 的创建和最终更新。

主要要点和最佳实践

• 原子性是关键：对涉及多个相关步骤的任何操作使用事务。
• 应用逻辑：大多数应用程序框架和数据库库都提供帮助程序来管理事务，通常在代码中发生错误或异常时自动回滚。
• DDL 是事务性的：PostgreSQL 的一个独特特性是数据定义语言（DDL）命令，如 CREATE TABLE 和 ALTER TABLE，可以包含在事务块中并回滚。这对于复杂的模式迁移非常有用。
• 保持事务简短：长时间运行的事务可能会锁定表，从而阻塞其他用户。设计您的应用程序，使事务尽可能简短。