SQLite - 事务

SQLite - 使用事务（Transactions）管理数据一致性

事务是一系列作为单个逻辑工作单元执行的操作。在数据库系统中，事务对于维护数据完整性至关重要，尤其是在多用户环境或复杂的多步骤操作期间。

事务中的所有操作都被视为“全有或全无”的命题：要么所有操作都成功，要么它们都不会保存到数据库中。

ACID 特性

事务由四个标准属性定义，统称为 ACID：

• 原子性（Atomicity）：保证事务中的所有命令都成功完成。如果其中一个失败，整个事务将被回滚，数据库将保持不变。
• 一致性（Consistency）：确保任何事务都将使数据库从一个有效状态转换到另一个有效状态。所有完整性约束都必须满足。
• 隔离性（Isolation）：确保并发事务产生与事务串行执行时相同的数据库状态。在一个事务中进行的更改在提交之前对其他事务不可见。
• 持久性（Durability）：保证一旦事务被提交，它将永久保存，即使发生断电、崩溃或错误。

核心事务命令

• BEGIN TRANSACTION：开始一个新事务。您可以使用简写 BEGIN。
• COMMIT：将事务期间进行的所有更改保存到数据库，使它们永久生效。您也可以使用 END TRANSACTION。
• ROLLBACK：丢弃自事务开始以来所做的所有更改，将数据库恢复到事务开始时的状态。

这些命令主要适用于 DML 语句（INSERT、UPDATE、DELETE）。DDL 语句（如 CREATE TABLE 或 DROP INDEX）在其自己的自动提交事务中运行，并且不能回滚。

实际示例：COMMIT 和 ROLLBACK

让我们看看事务如何工作。首先，我们将尝试删除两行，然后 ROLLBACK 这些更改。

-- 检查初始状态：我们有 2 名 AGE = 25 的员工
SELECT NAME, AGE FROM COMPANY WHERE AGE = 25;
-- NAME  | AGE
-- Allen | 25
-- Mark  | 25

BEGIN TRANSACTION;

-- 删除员工
DELETE FROM COMPANY WHERE AGE = 25;

-- 在事务内部，这些行已消失
SELECT COUNT(*) FROM COMPANY WHERE AGE = 25; -- returns 0

-- 现在，撤销删除
ROLLBACK;

-- 回滚后，数据已恢复
SELECT NAME, AGE FROM COMPANY WHERE AGE = 25;
-- NAME  | AGE
-- Allen | 25
-- Mark  | 25

现在，让我们执行相同的删除操作，但 COMMIT 更改以使其永久生效。

BEGIN TRANSACTION;

DELETE FROM COMPANY WHERE AGE = 25;

COMMIT;

-- 提交后，删除操作是永久性的
SELECT COUNT(*) FROM COMPANY WHERE AGE = 25; -- returns 0

高级控制：保存点（Savepoints）

对于更复杂的操作，SQLite 通过 SAVEPOINT 支持嵌套事务。这允许您回滚到更大事务中的中间点。

• SAVEPOINT name;：创建一个新保存点。
• ROLLBACK TO SAVEPOINT name;：撤销指定保存点之后所做的更改，但保持事务活跃。
• RELEASE SAVEPOINT name;：删除一个保存点。如果您 COMMIT 或 ROLLBACK 主事务，所有保存点都会自动释放。

BEGIN TRANSACTION;

-- 初始更改
UPDATE COMPANY SET SALARY = SALARY + 1000 WHERE NAME = 'Paul';

SAVEPOINT my_savepoint;

-- 第二次更改，我们可能想要撤销它
DELETE FROM COMPANY WHERE NAME = 'Teddy';

-- 出了点问题，让我们撤销删除但保留薪水更新
ROLLBACK TO SAVEPOINT my_savepoint;

-- Paul 的薪水更新仍然处于暂存状态，但 Teddy 已恢复
COMMIT;
-- 现在 Paul 的薪水增加是永久性的。