SQLite 事务处理 - 保证数据完整性

介绍

在这个实验中，我们将探索 SQLite 事务处理，重点是通过使用事务来维护数据完整性。我们将学习如何开始和提交事务，确保将多个相关操作视为一个单一的、原子性的单元。

本实验将指导你完成使用 BEGIN TRANSACTION 启动事务、对数据库进行更改，然后使用 COMMIT 语句永久保存这些更改的过程。你还将学习如何使用 ROLLBACK 撤消更改，以及使用 SAVEPOINT 进行更精细的控制。这种实践经验将巩固你对事务如何在 SQLite 中保证原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）（ACID 属性）的理解。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/start_trans("Begin New Transaction") sqlite/SQLiteGroup -.-> sqlite/save_trans("Commit Transaction") sqlite/SQLiteGroup -.-> sqlite/undo_trans("Rollback Transaction") subgraph Lab Skills sqlite/get_all -.-> lab-552558{{"SQLite 事务处理"}} sqlite/query_where -.-> lab-552558{{"SQLite 事务处理"}} sqlite/start_trans -.-> lab-552558{{"SQLite 事务处理"}} sqlite/save_trans -.-> lab-552558{{"SQLite 事务处理"}} sqlite/undo_trans -.-> lab-552558{{"SQLite 事务处理"}} end

创建数据库和表

在第一步中，我们将创建一个 SQLite 数据库和一个用于存储用户数据的表。这将为后续步骤中探索事务处理奠定基础。

首先，在 LabEx 虚拟机（VM）中打开你的终端。你的默认路径是 /home/labex/project。

现在，让我们创建一个名为 mydatabase.db 的 SQLite 数据库。运行以下命令来创建数据库文件并打开 SQLite 命令行工具：

sqlite3 mydatabase.db

你将看到一个提示，表明你现在位于 SQLite shell 中：

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

接下来，创建一个名为 users 的表来存储基本用户信息。该表将包含三列：id（唯一标识符）、name 和 balance。在 sqlite> 提示符下输入以下 SQL 命令，然后按 Enter 键：

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    balance REAL
);

此命令设置 users 表，其中：

id 是一个整数，用作每个用户的主键（primary key）。
name 是一个文本字段，表示用户的姓名。
balance 是一个实数，表示用户的账户余额。

现在，将一些初始数据插入到 users 表中：

INSERT INTO users (name, balance) VALUES ('Alice', 100.0);
INSERT INTO users (name, balance) VALUES ('Bob', 200.0);

这些命令添加了两个用户，Alice 和 Bob，他们的初始余额分别为 100.0 和 200.0。

为了确认数据已正确添加，请运行以下命令以查看表中的所有记录：

SELECT * FROM users;

预期输出：

1|Alice|100.0
2|Bob|200.0

此输出显示每个记录的 id、name 和 balance。SELECT * 命令检索指定表中的所有列。

开始和提交事务

在这一步中，你将学习如何开始和提交事务。事务对于在执行多个相关操作时确保数据完整性至关重要。

要开始一个事务，请使用 BEGIN TRANSACTION 命令：

BEGIN TRANSACTION;

此命令告诉 SQLite 开始跟踪更改，但在你显式提交事务之前，不会将这些更改永久应用到数据库。

现在，让我们从 Alice 转账 50.0 给 Bob：

UPDATE users SET balance = balance - 50.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 50.0 WHERE name = 'Bob';

这些命令在事务中更新 Alice 和 Bob 的余额。Alice 的余额减少 50.0，而 Bob 的余额增加 50.0。

要保存更改，请使用 COMMIT 命令：

COMMIT;

此命令提交事务，使更改永久生效。

通过再次查询表来验证更改：

SELECT * FROM users;

预期输出：

1|Alice|50.0
2|Bob|250.0

此输出确认事务已成功提交，并且 Alice 和 Bob 的余额已相应更新。

回滚事务

在这一步中，你将学习如何使用 ROLLBACK 命令来撤销在事务期间所做的更改。这对于处理错误和确保数据完整性至关重要。

首先，开始一个新的事务：

BEGIN TRANSACTION;

现在，让我们尝试从 Alice 转账 100.0 给 Bob，但这次，我们将模拟一个错误情况：

UPDATE users SET balance = balance - 100.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 100.0 WHERE name = 'Bob';

由于 Alice 只有 50.0，因此第一个更新将导致负余额。虽然 SQLite 默认情况下不强制执行约束（constraints），但我们将在该操作之后模拟发生错误。

要撤消这些更改，请使用 ROLLBACK 命令：

ROLLBACK;

此命令回滚事务，撤消自 BEGIN TRANSACTION 语句以来所做的任何更改。

通过查询 users 表来验证更改是否已回滚：

SELECT * FROM users;

预期输出：

1|Alice|50.0
2|Bob|250.0

此输出确认事务已成功回滚，并且 Alice 和 Bob 的余额保持不变。

实现保存点（Savepoints）

在这一步中，你将学习如何在事务中使用保存点（savepoints）。保存点允许你在事务中创建中间点，你可以回滚（rollback）到这些点，而无需回滚整个事务。

首先，开始一个新的事务：

BEGIN TRANSACTION;

创建一个名为 savepoint1 的保存点：

SAVEPOINT savepoint1;

现在，让我们从 Alice 转账 20.0 给 Bob：

UPDATE users SET balance = balance - 20.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 20.0 WHERE name = 'Bob';

创建另一个名为 savepoint2 的保存点：

SAVEPOINT savepoint2;

让我们添加一个名为 'Charlie' 的新用户，初始余额为 300.0：

INSERT INTO users (name, balance) VALUES ('Charlie', 300.0);

现在，假设我们认为添加 Charlie 是一个错误。我们可以回滚到 savepoint1，这将撤消 INSERT 语句以及 Alice 和 Bob 之间 20.0 的转账：

ROLLBACK TO SAVEPOINT savepoint1;

验证回滚到 savepoint1 后的更改：

SELECT * FROM users;

预期输出：

1|Alice|50.0
2|Bob|250.0

你应该看到 Charlie 不在表中，并且 Alice 和 Bob 的余额恢复到 savepoint1 之前的值。

现在，让我们从 Alice 转账 10.0 给 Bob：

UPDATE users SET balance = balance - 10.0 WHERE name = 'Alice';
UPDATE users SET balance = balance + 10.0 WHERE name = 'Bob';

最后，提交事务：

COMMIT;

添加约束以防止负余额

在这一步中，你将向 users 表添加一个约束（constraint）以防止出现负余额。这将通过阻止无效事务来帮助确保数据完整性。

将以下约束添加到 users 表：

ALTER TABLE users ADD CONSTRAINT positive_balance CHECK (balance >= 0);

此命令将一个名为 positive_balance 的 CHECK 约束添加到 users 表。此约束确保 balance 列必须始终大于或等于 0。

现在，让我们尝试从 Alice 转账 1000 给 Bob，这将违反该约束：

BEGIN TRANSACTION;
UPDATE users SET balance = balance - 1000 WHERE name = 'Alice';
UPDATE users SET balance = balance + 1000 WHERE name = 'Bob';

这一次，UPDATE 语句将抛出一个错误，因为它违反了 positive_balance 约束。你将看到类似这样的错误消息：Error: CHECK constraint failed: positive_balance。

回滚事务：

ROLLBACK;

验证更改是否已回滚：

SELECT * FROM users;

预期输出：

1|Alice|40.0
2|Bob|260.0

这确认 ROLLBACK 成功地将数据库恢复到失败事务之前的原始状态。

总结

在这个实验（lab）中，你已经学习了 SQLite 中事务处理的基础知识。你已经了解了如何开始和提交事务、回滚更改、实现保存点（savepoints）以进行更精细的控制，以及添加约束（constraints）以确保数据完整性。这些技能对于构建健壮且可靠的数据库应用程序至关重要。