PostgreSQL 事务管理

介绍

在本实验中，你将探索 PostgreSQL 事务管理，这是确保数据完整性的关键方面。你将学习如何开始和提交事务，将一系列操作视为一个工作单元。你还会学习如何回滚失败的事务、设置隔离级别以及模拟并发更新时的锁。

这是一个实验（Guided Lab），提供逐步指导来帮助你学习和实践。请仔细按照说明完成每个步骤，获得实际操作经验。根据历史数据，这是一个初级级别的实验，完成率为 93%。获得了学习者 93% 的好评率。

开始和提交事务

在本步骤中，你将学习如何在 PostgreSQL 中开始和提交事务。事务通过将一系列操作视为一个工作单元来确保数据完整性。如果事务中的任何操作失败，则整个事务将回滚，防止部分更新并保持一致性。

首先，连接到 PostgreSQL 数据库，使用 postgres 用户。打开终端并使用以下命令：

sudo -u postgres psql

现在你应该看到 postgres=# 提示符。

接下来，创建一个名为 accounts 的表来演示事务。

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10, 2)
);

将一些初始数据插入 accounts 表。

INSERT INTO accounts (name, balance) VALUES ('Alice', 100.00);
INSERT INTO accounts (name, balance) VALUES ('Bob', 50.00);

现在，让我们使用 BEGIN 命令开始一个事务。

BEGIN;

在事务中，将 Alice 账户的 20 美元转入 Bob 账户。

UPDATE accounts SET balance = balance - 20.00 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 20.00 WHERE name = 'Bob';

为了使这些更改永久生效，使用 COMMIT 命令提交事务。

COMMIT;

通过查询 accounts 表来验证事务是否成功。

SELECT * FROM accounts;

你应该看到 Alice 的余额减少了 20 美元，而 Bob 的余额增加了 20 美元。

最后，退出 psql shell。

\q

回滚失败的事务

在本步骤中，你将学习如何在 PostgreSQL 中回滚失败的事务。当数据库操作系列中发生错误时，回滚事务至关重要，以确保数据库保持一致状态。

你应该仍然连接到前一步的 PostgreSQL 数据库。如果没有，请使用以下命令重新连接：

sudo -u postgres psql

让我们开始一个新的事务。

BEGIN;

在这个事务中，我们将尝试一个故意失败的操作。让我们尝试插入重复的主键。首先，让我们找到下一个可用的 id 值。

SELECT MAX(id) FROM accounts;

假设结果是 2。现在，尝试插入一个新的账户，id = 1，该 id 已经存在。

INSERT INTO accounts (id, name, balance) VALUES (1, 'Eve', 25.00);

此命令将导致 ERROR: duplicate key value violates unique constraint "accounts_pkey" 错误。

由于事务中发生了错误，请回滚事务以丢弃所做的任何更改。使用 ROLLBACK 命令。

ROLLBACK;

通过查询 accounts 表来验证 ROLLBACK 是否成功。

SELECT * FROM accounts;

你应该看到该表仍然只包含 Alice 和 Bob 的账户及其在步骤 1 结束时的余额。失败的 INSERT 操作已成功回滚。

最后，退出 psql shell。

\q

设置和测试隔离级别

在本步骤中，你将学习 PostgreSQL 中的事务隔离级别，以及如何设置和测试它们。隔离级别控制并发事务彼此隔离的程度。较高的隔离级别能够更好地防止数据损坏，但可能会降低并发性。

你应该仍然连接到前一步的 PostgreSQL 数据库。如果没有，请使用以下命令重新连接：

sudo -u postgres psql

打开两个独立的终端窗口。在每个终端中，连接到 PostgreSQL 数据库，使用 postgres 用户。你应该有两个 postgres=# 提示符。

终端 1：

sudo -u postgres psql

终端 2：

sudo -u postgres psql

在 终端 1 中，将隔离级别设置为 READ COMMITTED（尽管这是默认值，但我们将明确地设置它以进行演示）。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

然后，开始一个事务。

BEGIN;

在 终端 1 中，读取 Alice 的余额。

SELECT balance FROM accounts WHERE name = 'Alice';

注意该余额。现在，在 终端 2 中，开始一个事务并更新 Alice 的余额。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
UPDATE accounts SET balance = 90.00 WHERE name = 'Alice';
COMMIT;

在 终端 1 中，再次读取 Alice 的余额。

SELECT balance FROM accounts WHERE name = 'Alice';

由于隔离级别是 READ COMMITTED，你将看到终端 2 提交的更新后的余额 (90.00)。

现在，让我们测试 REPEATABLE READ 隔离级别。在 终端 1 中，回滚当前事务并设置隔离级别为 REPEATABLE READ。

ROLLBACK;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;

在 终端 1 中，再次读取 Alice 的余额。

SELECT balance FROM accounts WHERE name = 'Alice';

注意该余额。现在，在 终端 2 中，开始一个事务并再次更新 Alice 的余额。

BEGIN;
UPDATE accounts SET balance = 100.00 WHERE name = 'Alice';
COMMIT;

在 终端 1 中，再次读取 Alice 的余额。

SELECT balance FROM accounts WHERE name = 'Alice';

由于隔离级别是 REPEATABLE READ，即使终端 2 已经提交了新的值，你仍然会看到事务开始时的原始余额。

最后，在 终端 1 中，提交事务。

COMMIT;

现在，如果你在 终端 1 中再次读取 Alice 的余额，你将看到最新的已提交值 (100.00)。

退出两个 psql shell。

\q

模拟并发更新中的锁

在本步骤中，你将模拟 PostgreSQL 中并发更新时的锁。锁是用于防止并发事务相互干扰，确保数据完整性的机制。

你应该仍然连接到前一步的 PostgreSQL 数据库。如果没有，请使用以下命令重新连接：

sudo -u postgres psql

打开两个独立的终端窗口。在每个终端中，连接到 PostgreSQL 数据库，使用 postgres 用户。你应该有两个 postgres=# 提示符。

终端 1：

sudo -u postgres psql

终端 2：

sudo -u postgres psql

在 终端 1 中，开始一个事务并更新 Alice 的余额。关键的是，使用 SELECT ... FOR UPDATE 来锁定该行。

BEGIN;
SELECT balance FROM accounts WHERE name = 'Alice' FOR UPDATE;

这条命令检索 Alice 的余额，并对该行加锁，防止其他事务在该事务提交或回滚之前修改它。

在 终端 2 中，开始一个事务并尝试更新 Alice 的余额。

BEGIN;
UPDATE accounts SET balance = balance + 10 WHERE name = 'Alice';

请注意，终端 2 中的这条命令似乎会挂起。这是因为它正在等待 终端 1 持有的锁被释放。

现在，在 终端 1 中，提交事务。

COMMIT;

在 终端 1 中提交事务后，终端 2 中的 UPDATE 命令将继续执行。

在 终端 2 中，提交事务。

COMMIT;

现在，在任意一个终端中，查询 accounts 表以验证更改。

SELECT * FROM accounts;

你应该看到，在终端 1 释放锁之后，终端 2 中的事务更新了 Alice 的余额。

最后，退出两个 psql shell。

\q

此示例演示了如何使用 SELECT ... FOR UPDATE 来模拟锁，并防止并发更新相互干扰。如果没有锁，两个事务可能会读取相同的初始余额，并基于该值应用其更新，从而导致更新丢失。

总结

在本实验中，你学习了如何在 PostgreSQL 中管理事务。你首先使用 psql 连接到 PostgreSQL 数据库，并创建了一个包含初始数据的示例 accounts 表。

然后，你专注于演示事务的使用。你学习了如何使用 BEGIN 命令启动一个事务，执行多个数据库操作（更新 Alice 和 Bob 的余额），然后使用 COMMIT 命令提交事务，使更改永久生效。这阐明了事务的基本原理：将一系列操作视为一个原子单元。你还学习了如何回滚事务和设置隔离级别。最后，你模拟了并发更新中的锁，以了解如何防止数据损坏。