sql-transactions

掌握 SQL 事务：确保数据完整性

事务 (Transaction) 是作为单个逻辑工作单元执行的一系列操作。经典的例子是银行转账：您从一个账户扣款，然后向另一个账户存款。这两个操作必须同时成功。如果其中任何一个失败，整个操作都必须撤销。这种“全有或全无”的原则是事务的核心。

事务的 ACID 属性

为了保证可靠性，事务遵循四个属性，统称为 ACID：

• 原子性 (Atomicity)：确保事务内的所有操作作为一个单一的、不可分割的单元成功完成。如果事务的任何部分失败，整个事务将回滚，数据库保持不变。
• 一致性 (Consistency)：保证事务将数据库从一个有效状态带到另一个有效状态。写入数据库的任何数据都必须符合所有定义的规则，包括约束、级联和触发器。
• 隔离性 (Isolation)：确保并发事务产生与按顺序执行时相同的结果。这可以防止诸如一个事务读取另一个事务不完整数据的问题。
• 持久性 (Durability)：确保一旦事务被提交，即使发生断电、崩溃或错误，它也将保持不变。更改会永久保存。

事务控制命令

这些命令与 DML（Data Manipulation Language，数据操作语言）语句（如 INSERT、UPDATE 和 DELETE）一起使用来管理事务。DDL（Data Definition Language，数据定义语言）命令（如 CREATE TABLE）通常会自动提交。

• BEGIN TRANSACTION（或 START TRANSACTION）：标记事务的开始。
• COMMIT：将事务中进行的所有更改保存到数据库，使其永久生效。
• ROLLBACK：放弃事务中进行的所有更改，将数据库恢复到事务开始时的状态。
• SAVEPOINT：在事务中设置一个命名标记，以便以后可以回滚到该标记。

示例：COMMIT 和 ROLLBACK

让我们使用一个电商网站的 products 表。当用户下单时，我们需要减少库存。

-- 首先，我们创建并填充表。
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    stock_quantity INT NOT NULL CHECK (stock_quantity >= 0)
);

INSERT INTO products (product_id, product_name, stock_quantity) VALUES
(101, 'Laptop', 50),
(102, 'Mouse', 200);

一个成功的事务 (COMMIT)

客户购买了 2 台笔记本电脑。这是一个成功操作。

START TRANSACTION;

-- 减少产品的库存
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 101;

-- 更改现在是永久性的
COMMIT;

在此之后，查询表会显示“Laptop”的 stock_quantity 现在是 48。

一个不成功的事务 (ROLLBACK)

客户试图购买 250 只鼠标，但我们只有 200 只。我们的 CHECK 约束将导致错误，我们应该回滚。

START TRANSACTION;

-- 此 UPDATE 将失败，因为 stock_quantity 将变为 -50，违反 CHECK 约束。
-- 在实际应用中，您的代码会捕获此错误。
UPDATE products
SET stock_quantity = stock_quantity - 250
WHERE product_id = 102;

-- 由于发生错误，我们放弃更改。
ROLLBACK;

在 ROLLBACK 之后，“Mouse”的 stock_quantity 保持在 200。没有进行任何更改。

使用 SAVEPOINT

A SAVEPOINT 允许在较大的事务中进行部分回滚。这对于复杂的多步骤操作非常有用。

START TRANSACTION;

-- 步骤 1：更新笔记本电脑库存
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE product_id = 101;

-- 在下一步之前创建保存点
SAVEPOINT before_mouse_update;

-- 步骤 2：更新鼠标库存。假设这是一个错误。
UPDATE products SET stock_quantity = stock_quantity - 10 WHERE product_id = 102;

-- 我们意识到鼠标更新是错误的，因此我们回滚到保存点。
ROLLBACK TO SAVEPOINT before_mouse_update;

-- 最后，我们提交事务的有效部分（笔记本电脑更新）。
COMMIT;

最终状态是，笔记本电脑库存为 49，但鼠标库存恢复到 SAVEPOINT 之前的原始值。

常见陷阱与最佳实践

• 保持事务简短：长时间运行的事务会锁定数据库资源，阻塞其他用户并降低性能。尽快完成工作并 COMMIT 或 ROLLBACK。
• 不要等待用户输入：绝不要开始一个事务然后等待用户点击按钮。事务应该包含在单个、快速的服务器端操作中。
• 隐式与显式事务：请注意数据库的自动提交设置。最佳实践是使用显式的 START TRANSACTION 和 COMMIT/ROLLBACK 来使您的逻辑清晰且可预测。
• 错误处理是关键：您的应用程序代码必须能够捕获数据库错误并适当地发出 ROLLBACK 命令。