【PostgreSQL】提高篇——数据库事务管理与隔离级别

在现代数据库管理系统中，事务是确保数据一致性和完整性的重要机制。本文将深入探讨事务的概念、ACID 特性，以及不同的隔离级别（如 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE）。

1. 事务的概念

1.1 什么是事务？

事务是数据库管理系统（DBMS）中的一个重要概念，它是一个由一系列操作组成的逻辑单元，这些操作要么全部成功，要么全部失败。事务确保了数据的一致性和完整性。常见的事务场景包括银行转账、订单处理等。

1.2 事务的特性

事务具有以下四个特性，通常称为 ACID 特性：

原子性（Atomicity）：事务中的所有操作要么全部执行成功，要么全部不执行。如果事务中的某个操作失败，整个事务将被回滚到事务开始之前的状态。
一致性（Consistency）：事务在执行前后，数据库的状态必须保持一致。即使在事务执行过程中出现错误，数据库也不会处于不一致的状态。
隔离性（Isolation）：多个事务并发执行时，彼此之间不会互相干扰。每个事务的执行是独立的，仿佛它是唯一在系统中执行的事务。
持久性（Durability）：一旦事务提交，其结果是永久性的，即使系统崩溃，已提交的事务的结果也不会丢失。

2. 隔离级别

隔离级别定义了一个事务与其他事务之间的可见性和影响程度。SQL 标准定义了四种隔离级别，每种隔离级别都有其优缺点，适用于不同的应用场景。

2.1 READ UNCOMMITTED

在此隔离级别下，一个事务可以读取另一个事务未提交的数据。这可能导致脏读（Dirty Read）。

优缺点

优点：
- 允许更高的并发性，性能较好。
- 适用于对数据一致性要求不高的场景。
缺点：
- 可能导致脏读，数据不可靠。
- 不适合需要严格数据一致性的应用。

示例：READ UNCOMMITTED

假设我们有一个简单的 accounts 表，记录用户的账户余额。

CREATE TABLE accounts (id SERIAL PRIMARY KEY,name VARCHAR(100),balance DECIMAL
);INSERT INTO accounts (name, balance) VALUES ('Alice', 1000), ('Bob', 500);

现在，我们开始两个事务：

事务 1：增加 Alice 的余额，但尚未提交。

BEGIN;  -- 开始事务 1
UPDATE accounts SET balance = balance + 100 WHERE name = 'Alice';  -- Alice 的余额变为 1100

事务 2：读取 Alice 的余额。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- 设置隔离级别为 READ UNCOMMITTED
BEGIN;  -- 开始事务 2
SELECT * FROM accounts WHERE name = 'Alice';  -- 读取 Alice 的余额

结果：

事务 2 可以看到 Alice 的余额是 1100，尽管事务 1 尚未提交。这是一个脏读的例子。

2.2 READ COMMITTED

在此隔离级别下，一个事务只能读取已提交的数据。脏读被防止，但不可重复读（Non-repeatable Read）仍然可能发生。

优缺点

优点：
- 防止脏读，数据更可靠。
- 适用于大多数应用场景，平衡了并发性和一致性。
缺点：
- 可能出现不可重复读，数据在事务执行期间可能发生变化。

示例：READ COMMITTED

继续使用 accounts 表，假设我们现在将事务 2 的隔离级别更改为 READ COMMITTED。

事务 1：仍然在增加 Alice 的余额。

BEGIN;  -- 事务 1
UPDATE accounts SET balance = balance + 100 WHERE name = 'Alice';  -- Alice 的余额变为 1100

事务 2：读取 Alice 的余额。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  -- 设置隔离级别为 READ COMMITTED
BEGIN;  -- 事务 2
SELECT * FROM accounts WHERE name = 'Alice';  -- 读取 Alice 的余额

结果：

事务 2 只能看到 Alice 的余额为 1000，因为事务 1 尚未提交。
如果事务 1 提交后，事务 2 再次读取 Alice 的余额，则会看到 1100。这是一个不可重复读的例子。

2.3 REPEATABLE READ

在此隔离级别下，一个事务在执行期间可以多次读取同一数据集，并且每次读取的结果都是一致的。脏读和不可重复读都被防止，但幻读（Phantom Read）仍然可能发生。

优缺点

优点：
- 防止脏读和不可重复读，数据一致性更高。
- 适合需要多次读取同一数据的场景。
缺点：
- 可能出现幻读，尤其是在插入或删除操作时。

示例：REPEATABLE READ

我们将事务 2 的隔离级别更改为 REPEATABLE READ。

事务 1：增加 Alice 的余额并提交。

BEGIN;  -- 事务 1
UPDATE accounts SET balance = balance + 100 WHERE name = 'Alice';  -- Alice 的余额变为 1100
COMMIT;  -- 提交事务 1

事务 2：读取 Alice 的余额。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- 设置隔离级别为 REPEATABLE READ
BEGIN;  -- 事务 2
SELECT * FROM accounts WHERE name = 'Alice';  -- 读取 Alice 的余额

结果：

事务 2 将始终看到 Alice 的余额为 1000，即使事务 1 已提交并将其余额更新为 1100。这是因为 REPEATABLE READ 确保了在事务 2 的整个执行期间，读取的结果保持一致。

2.4 SERIALIZABLE

在此隔离级别下，事务完全隔离，仿佛它们是串行执行的。脏读、不可重复读和幻读都被防止。

优缺点

优点：
- 提供最高级别的数据一致性，适合关键业务操作。
- 防止所有类型的读问题。
缺点：
- 性能开销较大，可能导致事务冲突和等待。
- 适合对一致性要求极高的场景，但不适合高并发的环境。

示例：SERIALIZABLE

我们将事务 2 的隔离级别更改为 SERIALIZABLE。

事务 1：增加 Alice 的余额并提交。

BEGIN;  -- 事务 1
UPDATE accounts SET balance = balance + 100 WHERE name = 'Alice';  -- Alice 的余额变为 1100
COMMIT;  -- 提交事务 1

事务 2：尝试读取 Alice 的余额。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- 设置隔离级别为 SERIALIZABLE
BEGIN;  -- 事务 2
SELECT * FROM accounts WHERE name = 'Alice';  -- 读取 Alice 的余额

结果：

事务 2 将看到 Alice 的余额为 1100，因为事务 1 已提交。
如果事务 2 试图在读取后更新 Alice 的余额，可能会引发错误，提示由于并发冲突而无法完成操作。

3. 实际应用案例

3.1 银行转账

在银行转账的场景中，确保数据一致性至关重要。我们需要确保在从一个账户扣款的同时，另一个账户能够正确接收这笔款项。

BEGIN;  -- 开始转账事务
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';  -- 从 Alice 的账户扣款
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';    -- 向 Bob 的账户存款
COMMIT;  -- 提交事务

在这个例子中，使用 SERIALIZABLE 隔离级别可以确保在整个转账过程中，数据的一致性和完整性。

3.2 电子商务订单处理

在电子商务系统中，订单处理通常涉及多个数据库操作，例如更新库存、生成订单记录等。使用 REPEATABLE READ 隔离级别可以确保在处理订单时，读取的库存数量在整个事务期间保持一致。

BEGIN;  -- 开始订单处理事务
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- 设置隔离级别
SELECT stock FROM products WHERE id = 1;  -- 查询商品库存
UPDATE products SET stock = stock - 1 WHERE id = 1;  -- 更新库存
INSERT INTO orders (product_id, quantity) VALUES (1, 1);  -- 生成订单
COMMIT;  -- 提交事务

4. 错误处理

在处理事务时，可能会遇到各种错误，例如死锁、数据完整性约束失败等。为了确保事务的安全性和可靠性，通常会使用 ROLLBACK 语句来撤销未完成的事务。

示例：错误处理

BEGIN;  -- 开始事务
BEGIN TRYUPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';  -- 从 Alice 的账户扣款UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';    -- 向 Bob 的账户存款COMMIT;  -- 提交事务
END TRY
BEGIN CATCHROLLBACK;  -- 如果出现错误，撤销事务PRINT 'Error occurred: ' + ERROR_MESSAGE();  -- 输出错误信息
END CATCH;