PostgreSQL - 事务

PostgreSQL - 现代事务管理

事务是一系列操作，作为一个单独的逻辑工作单元执行。事务中的所有操作必须成功完成，否则它们都不会被应用。这确保了数据完整性，是可靠数据库系统的基石。这通常由 ACID 首字母缩写词描述。

事务的 ACID 特性

事务通过四个关键特性 (ACID) 保证可靠性：

• 原子性（Atomicity）：确保事务中的所有操作被视为一个单一的“原子”。事务要么完全完成（“提交”），要么完全撤销（“回滚”）。不存在部分完成的情况。
• 一致性（Consistency）：确保事务使数据库从一个有效状态转换到另一个有效状态。写入数据库的任何数据都必须符合所有定义的规则，包括约束、级联和触发器。
• 隔离性（Isolation）：确保并发执行的事务互不干扰。每个事务都仿佛独立于其他事务执行，从而防止“脏读”等问题。
• 持久性（Durability）：确保一旦事务提交，其更改就是永久性的，即使发生断电、崩溃或其他系统故障。

核心事务控制命令

您可以使用三个主要的 SQL 命令来控制事务：

• BEGIN TRANSACTION; (或仅 BEGIN;)：标记事务块的开始。
• COMMIT;：保存事务期间所做的所有更改，使它们永久生效。
• ROLLBACK;：丢弃事务期间所做的所有更改，将数据库恢复到事务开始前的状态。

这些命令与 DML（数据操作语言）语句（如 INSERT、UPDATE 和 DELETE）一起使用。DDL（数据定义语言）命令（如 CREATE TABLE）在 PostgreSQL 中通常是事务安全的，但有时可能会隐式提交开放的事务，因此最好避免在单个事务块中混合使用 DML 和 DDL。

实际应用：银行转账

事务的经典例子是两个银行账户之间的资金转账。这涉及两个独立的 UPDATE 操作：从一个账户借记，并贷记到另一个账户。我们必须确保两者都发生，否则都不发生。如果在两个操作之间系统崩溃，没有事务将是灾难性的。

首先，让我们设置 accounts 表：

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    owner_name VARCHAR(100) NOT NULL,
    balance NUMERIC(15, 2) NOT NULL CHECK (balance >= 0)
);

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

示例 1：成功转账 (COMMIT)

让我们从 Alice 转账 200 美元给 Bob。

-- Start the transaction
BEGIN TRANSACTION;

-- Step 1: Debit Alice's account
UPDATE accounts SET balance = balance - 200.00 WHERE id = 1;

-- Step 2: Credit Bob's account
UPDATE accounts SET balance = balance + 200.00 WHERE id = 2;

-- Check the state within the transaction (optional)
SELECT * FROM accounts ORDER BY id;

-- Make the changes permanent
COMMIT;

COMMIT 后，更改会被保存。最终的 SELECT * FROM accounts; 将显示 Alice 拥有 800 美元，Bob 拥有 700 美元。

示例 2：失败转账 (ROLLBACK)

现在，让我们尝试从 Alice 转账 1200 美元。这将失败，因为她的余额只有 800 美元，而且我们的表有一个 CHECK 约束来防止负余额。

BEGIN TRANSACTION;

-- Step 1: Attempt to debit Alice's account (this will succeed for now)
UPDATE accounts SET balance = balance - 1200.00 WHERE id = 1;
-- At this point, Alice's balance in this transaction is -400.00

-- In a real application, you might have a logic check here.
-- Let's say we detect the error and decide to abort.

-- Abort the transaction and undo all changes
ROLLBACK;

因为我们使用了 ROLLBACK，从 Alice 账户尝试的借记操作被完全撤销。最终的 SELECT * FROM accounts; 将显示余额与上一个成功事务结束时保持不变（Alice 800 美元，Bob 700 美元）。

高级控制：SAVEPOINT

对于复杂的事务，您可能希望只回滚部分工作。SAVEPOINT 允许您在事务中设置命名标记。然后，您可以使用 ROLLBACK TO savepoint_name 来撤销在该标记之后所做的更改，而无需中止整个事务。

BEGIN TRANSACTION;

-- Initial update
UPDATE accounts SET balance = balance - 50.00 WHERE id = 1; -- Alice 现在拥有 $750

SAVEPOINT after_debit;

-- A second operation that we might want to undo
UPDATE accounts SET balance = balance + 1000.00 WHERE id = 2; -- Bob 现在拥有 $1700

-- Something went wrong with the credit. Let's undo just that part.
ROLLBACK TO after_debit;

-- Now, Bob's balance is back to $700, but Alice's is still $750.

-- Finalize the transaction
COMMIT;

此事务结束后，Alice 将拥有 750 美元，Bob 将拥有 700 美元。部分回滚成功。

常见误区

• 孤立事务：在应用程序中忘记 COMMIT 或 ROLLBACK 可能会导致事务保持开放。这些空闲事务会持有行锁，从而阻塞其他用户并消耗服务器资源。
• 过长的事务：避免在事务内部执行慢速或用户交互操作。尽量使事务尽可能短且快速，以最大程度地减少锁定并提高并发性。