更多 SQLite 多线程

• 下载源代码 - 2.5 MB

引言

最近，另一篇CP 文章讨论了 SQLite 以及它在 C# 和 .NET 框架下如何处理并发。然而，好奇心驱使人们提出了一些问题，我认为这些问题值得更全面的解答。

• 性能是否受所用框架（.NET）的影响，还是仅仅是 SQLite 的限制？
• 每秒可以期待获得多少行数据？

我拥有解决这个问题的工具，因此，这里有一个 C++ 多线程程序，没有使用任何大型框架，（几乎）直接使用了 SQLite C API。

SQLite 多线程模型

很长一段时间以来，SQLite 都回避多线程，其作者 D. Richard Hipp 以不赞成多线程而闻名。这里有一个例子，突出了他 2005 年的观点：

实际上，这似乎是一个很好的机会来重复我经常被忽视的建议：不要在一个地址空间中使用多个线程。如果你需要多个线程，请创建多个进程。这与 SQLite 无关——这只是良好的编程建议。多年来，我处理过无数多线程程序，但我还没有见过一个程序不包含与线程问题相关的微妙、难以重现且非常难以调试的 bug。

我不断地惊叹于普遍存在的想法（以 Java 为例），即软件应该强类型，并且不应该使用 goto 语句或指针——所有这些都是为了减少 bug——但可以在同一个地址空间中使用多个线程。强类型仅有助于防止那些极其容易定位和修复的 bug。goto 语句和指针的使用同样会产生确定性的问题，这些问题易于测试，并且相对容易追踪和纠正。但是线程 bug 往往表现为与时间相关的故障和死锁，这些故障依赖于硬件和平台，永远不会以相同的方式发生两次，并且只会在部署后出现在客户那里，而永远不会出现在测试环境中。

（我完全同意他的观点）。

随着时间的推移，SQLite 已经发展出在两种模型下提供多线程支持。一种称为“多线程”，它假定相同的数据库连接对象及其派生对象（尤其是 SQL 语句）永远不会被两个不同的线程使用。换句话说，SQLite 会负责同步对数据库文件本身的访问，但所有内存结构都应该为不同的线程分开。

另一种模型称为“串行化”，它允许在线程之间共享连接和查询。线程模型的选择通过 `sqlite3_config` 函数完成。要选择任何一种模式，SQLite 代码必须被编译以包含多线程代码（默认情况下是包含的）。

测试代码

这是一个非常简单的测试。它只是运行一个 INSERT 语句，向一个有 4 列的表中插入数据：时间、计数器、插入行的线程 ID 和插入行所需的重试次数。如果数据库繁忙，线程将放弃其执行时间片，增加重试次数并重复，直到语句成功。

for (int i = 0; i < NSTATEMENTS; i++)
  {
    int retries = 0;
    // bind initial values to each column
    q.bind(1, i);
    q.bind(2, (int)id());
    q.bind(3, retries);

    //try to add row
    while ((result = q.step()) == SQLITE_BUSY)
    {
      //if DB is busy...
      Sleep(0);                 //... give up current time slice...
      q.reset().deactivate();   //... return statement to ready state....
      retries++;                //... increment number of retries...
      q.bind(3, retries);       //... and update the bound value
    }
    if (result != SQLITE_DONE)
    {
      cout << "Thread " << id() << " abnormal exit (" << result << ")\n";
      break;
    }
    q.reset();
  }

初步结果

我将程序配置为运行 8 个线程，每个线程插入 1000 条记录。首先，我们以“多线程”模式进行。

SQLITE version is: 3.43.2
Compile time threading supported: yes
Setting threading model to SQLITE_CONFIG_MULTITHREAD
Thread 16884 finished in 4.9089 sec
Thread 21588 finished in 7.6662 sec
Thread 2784 finished in 18.044 sec
Thread 29940 finished in 20.312 sec
Thread 9312 finished in 21.943 sec
Thread 19716 finished in 24.614 sec
Thread 19884 finished in 28.693 sec
Thread 30340 finished in 31.032 sec

Total rows: 8000
Total retries: 9972 max retries: 1624

嗯，超过 30 秒并不理想！也许是重试次数太多了；我们切换到“串行化”模式。

SQLITE version is: 3.43.2
Compile time threading supported: yes
Setting threading model to SQLITE_CONFIG_SERIALIZED
Thread 25152 finished in 5.0608 sec
Thread 18496 finished in 10.83 sec
Thread 17260 finished in 12.217 sec
Thread 29928 finished in 19.888 sec
Thread 29544 finished in 22.742 sec
Thread 13196 finished in 24.445 sec
Thread 15564 finished in 29.439 sec
Thread 11688 finished in 30.434 sec

Total rows: 8000
Total retries: 0 max retries: 0

我们消除了重试，因为现在是 SQLite 负责同步，但整体时间改进不大。

ACID 正在拖累你！

也许 SQLite 在多线程方面真的很糟糕。我们只用一个线程试试，看看发生了什么。

SQLITE version is: 3.43.2
Compile time threading supported: yes
Setting threading model to SQLITE_CONFIG_SERIALIZED
Thread 18456 finished in 3.8462 sec

所以，如果一个线程需要将近 4 秒才能完成 1000 次 INSERT，那么 8 个线程将需要 4x8=32 秒，这接近我们的初步结果。所以，造成瓶颈的不是多线程，一定有什么更深层的原因。答案是 ACID。

SQLite 是一个事务性数据库，你可能听说过事务的 ACID 要求：它必须是 **A**tomic（原子性）、**C**onsistent（一致性）、**I**solated（隔离性）和 **D**urable（持久性）。默认情况下，SQLite 将每个 SQL 语句视为一个事务，当 INSERT 完成时，数据就会真正写入磁盘。即使断电也不会丢失任何数据，因为所有缓冲区都已刷新，一切都安全。然而，这种安全性是有代价的：每次 INSERT 都会花费相当多的时间。

你可以通过将多个语句包装在 `BEGIN TRANSACTION` 和 `END TRANSACTION` 语句之间来更改“每个语句都是一个事务”的默认方法。我们的循环看起来会像这样：

  db.exec("BEGIN TRANSACTION");
  for (int i = 0; i < NSTATEMENTS; i++)
  {
    //... same stuff
  }
  db.exec ("END TRANSACTION");

我们这样做，然后再次运行我们的 8 个线程的测试。

SQLITE version is: 3.43.2
Compile time threading supported: yes
Setting threading model to SQLITE_CONFIG_MULTITHREAD
Thread 8132 finished in 0.0050677 sec
Thread 5160 finished in 0.010653 sec
Thread 17808 finished in 0.016677 sec
Thread 21988 finished in 0.021636 sec
Thread 25136 finished in 0.027609 sec
Thread 23768 finished in 0.03277 sec
Thread 10568 finished in 0.038499 sec
Thread 28452 finished in 0.045179 sec

Total rows: 8000
Total retries: 188 max retries: 60

是的！我们从 30 多秒缩短到 0.05 秒。我们找到了真正的瓶颈。

还有一个小问题：如果你将所有内容包装在一个事务中，你就不能为所有线程使用同一个连接（即“串行化”模式）。SQLite 不允许你在另一个事务进行中对同一个连接开始新事务。

杰克建造的房子

我说过我只会使用 SQLite C API，但这并非完全真实。我碰巧有一些工具可以让我生活得更轻松。

首先，有 SQLITEPP，一个轻量级的 C++ 包装器，用于 C API。我在另一篇文章中简要描述过它。如该文所述，SQLITEPP 使用“错误代码”模型进行错误处理，因此这也必须包含在内。

使用 C++ 和多线程并非易事，正如我在另一篇文章中讨论过的。所有这些线程基础设施也必须包含在内。

这些组件是我 MLIB 库的一部分，最新代码在GitHub上。别忘了最重要的事情，SQLite 本身。只要有可能，我更喜欢静态链接，对于 SQLite 来说，这并不难。

最终得到的是一个（按当今标准来看）相对较小的 1MB 应用程序，没有外部依赖。

历史

2024 年 3 月 25 日 - 初始版本