使用信心和 ACID 原则更改数据

伟大的引言“万物皆变，无物常驻”出自赫拉克利特，据苏格拉底所说，据柏拉图所说，这并没有什么争议：变化和宇宙一样古老。然而，在处理数据时，我们常常仅仅将变化视为事后的想法。

静态数据集只是世界在某一时刻样子的快照。但是很快，世界就发生了变化，数据集需要跟上才能保持有用。在表的世界中，可以添加新行，删除旧行，有时必须更改行以反映新的情况。通常，更改是相互关联的。如果没有 orders 表中的相应条目，则表中将订单映射到客户的行就不是很有效。大多数（如果不是全部）数据集最终都会被更改。因此，作为数据管理系统，管理更改不是可选的。但是，正确管理更改是很困难的。

早期的数据管理系统研究人员发明了一个称为“事务”的概念，其概念于 首次正式确定 在 20 世纪 80 年代。本质上，事务性和众所周知的 ACID 原则描述了一组保证，数据管理系统必须提供这些保证才能被认为是安全的。ACID 是原子性 (Atomicity)、一致性 (Consistency)、隔离性 (Isolation) 和持久性 (Durability) 的首字母缩写词。

ACID 原则不是一种理论上的练习。就像管理飞机或火车的规则一样，它们是用“鲜血写成的”——它们是几十年数据管理实践中来之不易的经验教训。当处理非 ACID 系统时，应用程序很难正确地进行推理。此类问题的最终结果通常是数据损坏或不再准确反映现实的数据。例如，行可能会重复或丢失。

DuckDB 默认提供完整的 ACID 保证，无需额外配置。在这篇博文中，我们将详细描述这意味着什么，并提供具体的示例，并展示如何利用此功能。

原子性

原子性 意味着一组更新中的所有更改要么全部发生，要么全部不发生。考虑下面的示例，我们在两个单独的表中插入两行。这些插入本身是单独的语句，但可以通过将它们包装在事务中来实现原子性

CREATE TABLE customer (id INTEGER, name VARCHAR);
CREATE TABLE orders (customer_id INTEGER, item VARCHAR);

BEGIN TRANSACTION;
INSERT INTO customer VALUES (42, 'DuckDB Labs');
INSERT INTO orders VALUES (42, 'stale bread');
COMMIT;

SELECT * FROM orders;

┌─────────────┬─────────────┐
│ customer_id │    item     │
│    int32    │   varchar   │
├─────────────┼─────────────┤
│          42 │ stale bread │
└─────────────┴─────────────┘

通过将更改包装在事务中，我们可以确保要么写入两行，要么都不写入。BEGIN TRANSACTION 语句表示所有后续语句都属于该事务。COMMIT 表示事务结束 – 并将更改持久保存到磁盘。

也可以通过在事务结束时发出 ROLLBACK 来撤消一组更改。这将确保事务中所做的任何更改都不会被持久保存。

BEGIN TRANSACTION;
INSERT INTO orders VALUES (42, 'iceberg lettuce');
INSERT INTO orders VALUES (42, 'dried worms');
ROLLBACK;
SELECT * FROM orders;

┌─────────────┬─────────────┐
│ customer_id │    item     │
│    int32    │   varchar   │
├─────────────┼─────────────┤
│          42 │ stale bread │
└─────────────┴─────────────┘

正如我们所看到的，两个新行尚未永久插入。

拥有原子性非常好，因为它允许应用程序将数据库从一个一致状态移动到另一个一致状态，而无需担心中间状态对应用程序可见。

我们应该注意，默认情况下，查询以所谓的“自动提交”模式运行，其中每个查询都将自动在其自己的事务中运行。也就是说，即使对于这些单语句查询，事务也非常有用。例如，当使用 INSERT 或 COPY 命令将数据批量加载到表中时，要么加载所有数据，要么不加载任何数据。系统不会将 CSV 文件部分加载到表中。

我们还应该注意，在 DuckDB 中，模式更改也是事务性的。这意味着您可以在事务的安全性范围内创建或删除表，以及更改表的模式。这也意味着您可以通过发出 ROLLBACK 来撤消任何这些操作。

一致性

一致性 意味着 数据库中定义的所有约束 必须始终成立，无论是在事务之前还是之后。约束永远不能被违反。约束的示例包括 PRIMARY KEY 或 FOREIGN KEY 约束。

CREATE TABLE customer (id INTEGER, name VARCHAR, PRIMARY KEY (id));

INSERT INTO customer VALUES (42, 'DuckDB Labs');
INSERT INTO customer VALUES (42, 'Wilbur the Duck');

在上面的示例中，customer 表要求 id 列对于所有条目都是唯一的，否则多个客户将与相同的订单相关联。我们可以通过在该列上定义所谓的 PRIMARY KEY 来强制执行此约束。当我们插入两个具有相同 id 的条目时，一致性检查失败，并且我们收到一条错误消息

Constraint Error: Duplicate key "id: 42" violates primary key
constraint. (...)

拥有这些类型的约束是一种很好的方式，可以确保数据保持一致，即使在发生多次更新之后也是如此。

隔离性

隔离性 意味着并发事务彼此隔离。一个数据库可以有许多客户端同时与它交互，导致许多事务同时发生。隔离这些事务的一种简单方法是依次执行它们。但是，这将会非常慢。成千上万的请求可能必须等待一个特别慢的请求。

为了避免这个问题，事务通常以交错的方式执行。但是，由于这些事务更改数据，因此必须确保每个事务在逻辑上是隔离的 – 它只能看到数据库的一致状态，并且（例如）永远不能读取尚未提交的事务中的数据。

DuckDB 没有传统意义上的连接 – 因为它不是一个允许单独的应用程序连接到它的客户端/服务器数据库。但是，DuckDB 在单个应用程序中具有 完整的多个客户端支持。用户可以创建多个客户端，这些客户端都连接到同一个 DuckDB 实例。事务可以并发运行，并且它们使用 快照隔离进行隔离。

创建多个连接的方式因客户端而异。下面是一个示例，我们展示了使用 Python 客户端的系统的事务性。

import duckdb

con1 = duckdb.connect(":memory:mydb")
con1.sql("CREATE TABLE customer (id INTEGER, name VARCHAR)")

con1.sql("INSERT INTO customer VALUES (42, 'DuckDB Labs')")

con1.begin()
con1.sql("INSERT INTO customer VALUES (43, 'Wilbur the Duck')")
# no commit!

# start a new connection
con2 = duckdb.connect(":memory:mydb")
con2.sql("SELECT name FROM customer").show()

# ┌─────────────┐
# │    name     │
# │   varchar   │
# ├─────────────┤
# │ DuckDB Labs │
# └─────────────┘

# commit from the first connection
con1.commit()

# now the changes are visible
con2.sql("SELECT name FROM customer").show()

# ┌─────────────────┐
# │      name       │
# │     varchar     │
# ├─────────────────┤
# │ DuckDB Labs     │
# │ Wilbur the Duck │
# └─────────────────┘

如您所见，我们有两个连接到同一个数据库，并且第一个连接插入了 Wilbur the Duck 客户，但尚未提交更改。同时，第二个连接从 customer 表中读取数据。结果尚未显示新条目，因为这两个事务在未提交的更改方面彼此隔离。在第一个连接提交后，第二个连接可以读取其更改。

持久性

最后，持久性 是系统在发生故障时的行为。这一点很重要，因为进程可能会崩溃，或者计算机可能会断电。现在，数据库系统需要确保所有已提交的事务都是持久的，这意味着它们的效果在重新启动数据库后是可见的。尚未完成的事务不能留下任何可见的痕迹。数据库通常通过密切关注各种缓存来保证此属性，例如，通过使用 fsync 强制在事务完成时将更改写入磁盘。跳过 fsync 是一种常见的“优化”，会危及持久性。

这是一个示例，再次使用 Python

import duckdb
import os
import signal

con = duckdb.connect("mydb.duckdb")
con.sql("CREATE TABLE customer (id INTEGER, name VARCHAR)")
con.sql("INSERT INTO customer VALUES (42, 'DuckDB Labs')")

# begin a transaction
con.begin()
con.sql("INSERT INTO customer VALUES (43, 'Wilbur the Duck')")
# no commit!

os.kill(os.getpid(), signal.SIGKILL)

重新启动后，我们可以检查 customer 表

import duckdb

con = duckdb.connect("mydb.duckdb")
con.sql("SELECT name FROM customer").show()

┌─────────────┐
│    name     │
│   varchar   │
├─────────────┤
│ DuckDB Labs │
└─────────────┘

在此示例中，我们首先在数据库文件 mydb.duckdb 中创建 customer 表。然后，我们首先插入一个包含 DuckDB Labs 的行作为第一个事务。然后，我们开始但不提交第二个添加 Wilbur the Duck 条目的事务。如果我们然后终止进程以及数据库，我们可以看到重新启动后只有 DuckDB Labs 条目幸存。这是因为第二个事务未提交，因此不受持久性的约束。当然，当必须考虑操作系统崩溃等非干净退出时，情况会变得更加复杂。DuckDB 还在这些情况下保证持久性，以下是一些更多信息。

为什么 OLAP 需要 ACID？

数据管理系统主要分为两类：事务系统 (OLTP) 和分析系统 (OLAP)。顾名思义，事务系统比分析系统更关心保证 ACID 属性。像历史悠久的 PostgreSQL 这样的系统理所当然地为默认情况下在提供事务保证方面“做正确的事情”而感到自豪。甚至像 MongoDB 这样的 NoSQL 事务系统，早期就放弃了保证 ACID 原则“以提高性能”，但最终不得不 “回滚”以提供 ACID 保证，并在过程中 遇到了一两个障碍。

像 DuckDB 这样的分析系统 – 原则上 – 提供强大的事务保证的必要性较小。它们通常不是所谓的“记录系统”，即被认为是真实来源的数据管理系统。事实上，DuckDB 提供了各种连接器来从记录系统加载数据，例如 PostgreSQL 扫描器。如果 OLAP 数据库损坏，通常可以从该真实来源恢复。当然，这首先需要用户注意到出现了一些问题，而这并不总是容易检测到。例如，一个常见的错误是将来自同一个 CSV 文件的数据两次摄取到数据库中，因为第一次尝试在某个时候出错了。这可能导致重复的行，从而导致不正确的聚合结果。ACID 可以防止这些类型的问题。ACID 属性在 OLAP 系统中启用有用的功能。例如

并发摄取和报告。 由于变化是持续的，我们经常有数据摄取流将新数据添加到数据库系统中。在分析系统中，通常由单个连接将新数据附加到数据库，而其他连接从数据库读取数据，以便（例如）生成图形和报告。如果这些连接是隔离的，那么生成的图形和聚合将始终在数据库的完整且一致的快照上执行，从而确保生成的图形和聚合是正确的。

回滚不正确的转换。 在分析数据时，一种常见的模式是从存储在平面文件中的数据集中加载数据，然后对该数据执行一些转换。例如，我们可能会从 CSV 文件加载数据集，然后清理 NULL 值，然后删除不完整的行。如果我们进行了不正确的转换，则可能会意外删除太多的行。

这不是世界末日，因为我们可以通过从原始 CSV 文件中重新读取来恢复。但是，我们可以通过将转换包装在事务中并在出现问题时回滚来节省大量时间。例如

CREATE TABLE people AS SELECT * FROM 'people.csv';

BEGIN TRANSACTION;
UPDATE people SET age = NULL WHERE age = -99;
-- oops, we deleted all rows!
DELETE FROM people WHERE name <> 'non-existent name';
-- we can recover our original table by rolling back the delete
ROLLBACK;

SQL 断言。 当事务中发生（非语法）错误时，事务会自动中止，并且无法提交更改。我们可以使用事务的此属性将断言添加到我们的事务中。当触发这些断言之一时，会引发错误，并且无法提交事务。我们可以使用 error 函数来定义我们自己的 assert 宏

CREATE MACRO assert(condition, message) AS
    CASE WHEN NOT condition THEN error(message) END;

然后，我们可以使用此 assert 宏来断言 people 表不为空

CREATE TABLE people AS SELECT * FROM 'people.csv';

BEGIN TRANSACTION;
UPDATE people SET age = NULL WHERE age = -99;
-- oops, we deleted all rows!
DELETE FROM people WHERE name <> 'non-existent name';
SELECT assert(
           (SELECT count(*) FROM people) > 0,
           'People should not be empty'
       );
COMMIT;

当断言触发时，事务会自动中止，并且更改会被回滚。

完整的 TPC-H 基准测试实现

事务处理性能委员会 (TPC) 是一个由数据管理系统和硬件供应商组成的行业协会。TPC 发布数据库基准规范并监督基准结果的审核，然后将其发布在其网站上。有各种针对不同用例的基准。 TPC-H 决策支持基准 专门针对大量数据上的分析查询处理。它的 22 个著名的 SQL 查询和数据生成器规范已被数据库供应商和 学者 反复分析。

鲜为人知的是，官方 TPC-H 基准包括需要 ACID 合规性的数据修改事务，考虑到该组织的名称，这并不令人惊讶。对于一次性的性能对比，通常会忽略更新，仅报告静态数据集上 22 个查询的运行时间。此类结果纯粹是信息性的，不能由 TPC 进行审核或正式发布。但是正如我们上面所说的，变化是不可避免的，所以让我们使用 DuckDB 执行带有更新的 TPC-H 实验。

TPC-H 为一家销售商品的虚构公司生成数据。最大的表是 orders 和 lineitem，其中包含每个订单的元素。基准可以生成不同大小的数据，大小由所谓的“比例因子 (SF)”控制。该规范定义了两个“刷新函数”，用于修改数据库。第一个刷新函数会将 SF * 1500 个新行添加到 orders 表中，并为每个订单随机添加 1 到 7 个新条目到 lineitem 表中。第二个刷新函数将从 orders 表中删除 SF * 1500 个条目以及关联的 lineitem 条目。基准数据生成器 dbgen 可以生成任意数量的刷新函数 CSV 文件，其中包含 orders 和 lineitem 的新条目，以及要删除的行。

指标

TPC-H 的主要基准指标来自“功率”和“吞吐量”测试结果的组合。

功率测试将运行第一个刷新函数并计时，然后运行 22 个查询，然后运行第二个刷新函数，并计算所有计时的几何平均值。在配备 M3 Max CPU 和 64 GB RAM 的 MacBook Pro 上，比例因子为 100 且 DuckDB 1.1.1，我们得到一个大小功率值为 650 536。

吞吐量测试将运行多个并发查询“流”，这些流以随机顺序并行执行 22 个基准查询。此外，单个刷新流将多次运行两个刷新函数。查询流的数量和刷新集数来自比例因子。对于 SF100，有 5 个查询流和 10 个刷新集。对于我们的实验，我们得到一个吞吐量@大小为 452 571。结果很难比较，但与 官方结果列表 相比，结果看起来还不错。

ACID 测试

TPC-H 基准规范的第 3 节详细讨论了 ACID 属性。该规范定义了一组测试来强调数据管理系统的 ACID 保证。该规范适当地指出，没有测试可以证明完全支持 ACID 属性，通过它们是合规性的“必要但非充分条件”。下面，我们将概述测试的内容。

这些测试指定了一个“ACID 事务”，它以这样一种方式修改 lineitem 和 orders 表，使一个不变量成立：orders 表包含属于此订单的所有行项目的总价格之和。该事务选择一个随机订单，并修改最后一个行项目以具有新价格。然后它重新计算订单总价，并使用该价格更新 orders 表。最后，事务将有关何时更新哪一行以及在 history 表中使用的价格增量的信息插入到 history 表中。

为了测试原子性，ACID 事务针对一个随机订单运行，然后提交。验证数据库是否已按照指定的值进行了相应更改。重复该测试，但这次事务中止。验证数据库是否未更改。

对于一致性，许多线程并行运行 ACID 事务 100 次在随机订单上。在测试之前和之后，检查一致性条件，这基本上确保订单的所有行项目价格的总和与订单中的总和一致。

为了测试隔离性，一个线程将运行该事务，但尚未提交或回滚。另一个连接将确保更改对其不可见。另一组测试将让两个线程在同一订单上运行事务，并确保其中一个线程由于冲突而被系统中止。

最后，为了测试持久性，许多线程运行 ACID 事务并记录结果。允许它们各自完成至少 100 个事务。然后，导致发生故障，在我们的例子中，我们只是终止了该进程（使用 SIGKILL）。然后，允许数据库系统从 预写日志 中恢复已提交的更改。检查日志以确保没有未反映在 history 表中的日志条目，并且没有没有日志条目的历史记录条目，减去极少数可能在传输过程中丢失的条目（即，由数据库持久保存但尚未由基准驱动程序记录）。最后，再次检查一致性。

我们很高兴地报告 DuckDB 通过了所有测试。

我们运行基准测试的脚本 可在 GitHub 上找到。我们计划在未来对我们的结果进行正式审核。发生这种情况时，我们将更新此帖子。

结论

数据集中的更改是不可避免的，数据管理系统需要能够安全地管理更改。DuckDB 支持强大的 ACID 保证，允许安全和并发的数据修改。我们使用 TPC-H 的事务验证测试进行了广泛的实验，发现它们通过了。