C++ 如何在多线程应用程序中使用 SQLite？

How to use SQLite in a multi-threaded application?

提问by Edan Maor

I'm developing an application with SQLiteas the database, and am having a little trouble understanding how to go about using it in multiple threads (none of the other Stack Overflow questions really helped me, unfortunately).

我正在开发一个使用SQLite作为数据库的应用程序，并且在理解如何在多个线程中使用它时遇到了一些麻烦（不幸的是，其他 Stack Overflow 问题都没有真正帮助我）。

My use case: The database has one table, let's call it "A", which has different groups of rows (based on one of their columns). I have the "main thread" of the application which reads the contents from table A. In addition, I decide, once in a while, to update a certain group of rows. To do this, I want to spawn a new thread, delete all the rows of the group, and re-insert them (that's the only way to do it in the context of my app). This might happen to different groups at the same time, so I might have 2+ threads trying to update the database.

我的用例：数据库有一个表，我们称之为“A”，它具有不同的行组（基于其中的一列）。我有应用程序的“主线程”，它从表 A 中读取内容。此外，我决定偶尔更新某组行。为此，我想生成一个新线程，删除组的所有行，然后重新插入它们（这是在我的应用程序上下文中执行此操作的唯一方法）。这可能同时发生在不同的组中，所以我可能有 2 个以上的线程试图更新数据库。

I'm using different transactions from each thread, I.E. at the start of every thread's update cycle, I have a begin. In fact, what each thread actually does is call "BEGIN", delete from the database all the rows it needs to "update", and inserts them again with the new values (this is the way it must be done in the context of my application).

我从每个线程使用不同的事务，IE 在每个线程的更新周期开始时，我有一个开始。事实上，每个线程实际做的是调用“BEGIN”，从数据库中删除它需要“更新”的所有行，然后用新值再次插入它们（这是必须在我的上下文中完成的方式）应用）。

Now, I'm trying to understand how I go about implementing this. I've tried reading around (other answers on Stack Overflow, the SQLite site) but I haven't found all the answers. Here are some things I'm wondering about:

现在，我试图了解我如何实施这一点。我试过阅读（Stack Overflow 上的其他答案，SQLite 网站），但我还没有找到所有的答案。以下是我想知道的一些事情：

1. Do I need to call "open" and create a new sqlite structure from each thread?
2. Do I need to add any special code for all of this, or is it enough to spawn different threads, update the rows, and that's fine (since I'm using different transactions)?
3. I saw something talking about the different lock types there are, and the fact that I might receive "SQLite busy" from calling certain APIs, but honestly I didn't see any reference that completely explained when I need to take all this into account. Do I need to?

1. 我是否需要调用“open”并从每个线程创建一个新的 sqlite 结构？
2. 我是否需要为所有这些添加任何特殊代码，还是足以产生不同的线程、更新行，这很好（因为我使用的是不同的事务）？
3. 我看到了一些关于不同锁类型的讨论，以及我可能会通过调用某些 API 收到“SQLite busy”这一事实，但老实说，当我需要考虑所有这些时，我没有看到任何完全解释的参考资料。我需要吗？

If anyone can answer the questions/point me in the direction of a good resource, I'd be very grateful.

如果有人可以回答问题/为我指明一个好的资源方向，我将不胜感激。

UPDATE 1:From all that I've read so far, it seems like you can't have two threads who are going to write to a database file anyway.

更新 1：从我目前阅读的所有内容来看，似乎您不能有两个线程无论如何都将写入数据库文件。

See: http://www.sqlite.org/lockingv3.html. In section 3.0: A RESERVED lock means that the process is planning on writing to the database file at some point in the future but that it is currently just reading from the file. Only a single RESERVED lock may be active at one time, though multiple SHARED locks can coexist with a single RESERVED lock.

请参阅：http: //www.sqlite.org/lockingv3.html。在第 3.0 节中：保留锁意味着进程计划在将来的某个时间写入数据库文件，但它目前只是从文件中读取。一次只能激活一个 RESERVED 锁，但多个 SHARED 锁可以与单个 RESERVED 锁共存。

Does this mean that I may as well only spawn off a single thread to update a group of rows each time? I.e., have some kind of poller thread which decides that I need to update some of the rows, and then creates a new thread to do it, but never more than one at a time? Since it looks like any other thread I create will just get SQLITE_BUSY until the first thread finishes, anyway.

这是否意味着我也可以每次只生成一个线程来更新一组行？即，有某种轮询线程决定我需要更新某些行，然后创建一个新线程来执行此操作，但一次不能超过一个？因为它看起来像我创建的任何其他线程都会得到 SQLITE_BUSY，直到第一个线程完成，无论如何。

Have I understood things correctly?

我理解正确吗？

BTW, thanks for the answers so far, they've helped a lot.

顺便说一句，感谢到目前为止的答案，他们帮了很多忙。

采纳答案by Kristian

Check out this link. The easiest way is to do the locking yourself, and to avoid sharing the connection between threads. Another good resource can be found here, and it concludes with:

查看此链接。最简单的方法是自己进行锁定，并避免共享线程之间的连接。另一个很好的资源可以在这里找到，它的结尾是：

1. Make sure you're compiling SQLite with -DTHREADSAFE=1.

2. Make sure that each thread opens the database file and keeps its own sqlite structure.

3. Make sure you handle the likely possibility that one or more threads collide when they access the db file at the same time: handle SQLITE_BUSY appropriately.

4. Make sure you enclose within transactions the commands that modify the database file, like INSERT, UPDATE, DELETE, and others.

1. 确保使用 -DTHREADSAFE=1 编译 SQLite。

2. 确保每个线程打开数据库文件并保持自己的sqlite结构。

3. 确保处理一个或多个线程在同时访问 db 文件时发生冲突的可能性：适当处理 SQLITE_BUSY。

4. 确保在事务中包含修改数据库文件的命令，如 INSERT、UPDATE、DELETE 等。

回答by Snazzer

Some steps when starting out with SQLlite for multithreaded use:

开始使用 SQLlite 进行多线程使用时的一些步骤：

1. Make sure sqlite is compiled with the multi threaded flag.
2. You must call open on your sqlite file to create a connection on each thread, don't share connections between threads.
3. SQLite has a very conservative threading model, when you do a write operation, which includes opening transactions that are about to do an INSERT/UPDATE/DELETE, other threads will be blocked until this operation completes.
4. If you don't use a transaction, then transactions are implicit, so if you start a INSERT/DELETE/UPDATE, sqlite will try to acquire an exclusive lock, and complete the operation before releasing it.
5. If you do a BEGIN EXCLUSIVE statement, it will acquire an exclusive lock before doing operations in that transaction. A COMMIT or ROLLBACK will release the lock.
6. Your sqlite3_step, sqlite3_prepare and some other calls may return SQLITE_BUSY or SQLITE_LOCKED. SQLITE_BUSY usually means that sqlite needs to acquire the lock. The biggest difference between the two return values:
   • SQLITE_LOCKED: if you get this from a sqlite3_step statement, you MUST call sqlite3_reset on the statement handle. You should only get this on the first call to sqlite3_step, so once reset is called you can actually "retry" your sqlite3_step call. On other operations, it's the same as SQLITE_BUSY
   • SQLITE_BUSY : There is no need to call sqlite3_reset, just retry your operation after waiting a bit for the lock to be released.

1. 确保使用多线程标志编译 sqlite。
2. 您必须在 sqlite 文件上调用 open 以在每个线程上创建连接，不要在线程之间共享连接。
3. SQLite 有一个非常保守的线程模型，当你做一个写操作时，包括打开即将执行 INSERT/UPDATE/DELETE 的事务，其他线程将被阻塞，直到这个操作完成。
4. 如果你不使用事务，那么事务是隐式的，所以如果你启动一个 INSERT/DELETE/UPDATE，sqlite 会尝试获取一个排他锁，并在释放它之前完成操作。
5. 如果您执行 BEGIN EXCLUSIVE 语句，它将在该事务中执行操作之前获取排他锁。COMMIT 或 ROLLBACK 将释放锁。
6. 您的 sqlite3_step、sqlite3_prepare 和其他一些调用可能会返回 SQLITE_BUSY 或 SQLITE_LOCKED。SQLITE_BUSY 通常意味着 sqlite 需要获取锁。两个返回值的最大区别：
   • SQLITE_LOCKED：如果你从 sqlite3_step 语句中得到这个，你必须在语句句柄上调用 sqlite3_reset。你应该只在第一次调用 sqlite3_step 时得到这个，所以一旦调用 reset，你实际上可以“重试”你的 sqlite3_step 调用。其他操作同SQLITE_BUSY
   • SQLITE_BUSY ：无需调用 sqlite3_reset，只需稍等片刻等待释放锁即可重试操作。

回答by adechiaro

I realize this is an old thread and the responses are good but I've been looking into this recently and came across an interesting analysis of some different implementations. Mainly it goes over the strengths and weaknesses of connection sharing, message passing, thread-local connections and connection pooling. Take a look at it here: http://dev.yorhel.nl/doc/sqlaccess

我意识到这是一个旧线程，响应很好，但我最近一直在研究这个问题，并对一些不同的实现进行了有趣的分析。主要介绍了连接共享、消息传递、线程本地连接和连接池的优缺点。看看这里：http: //dev.yorhel.nl/doc/sqlaccess

回答by Nikita

Modern versions of SQLite has thread safety enabled by default. SQLITE_THREADSAFEcompilation flag controls whether or not code is included in SQLite to enable it to operate safely in a multithreaded environment. Defaultvalue is SQLITE_THREADSAFE=1. It means Serialized mode. In this mode:

SQLite 的现代版本默认启用线程安全。SQLITE_THREADSAFE编译标志控制代码是否包含在 SQLite 中，以使其能够在多线程环境中安全运行。默认值为SQLITE_THREADSAFE=1。这意味着序列化模式。在这种模式下：

In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time.

在这种模式下（这是 SQLite 使用 SQLITE_THREADSAFE=1 编译时的默认设置），SQLite 库本身将序列化对数据库连接和准备语句的访问，以便应用程序可以自由地在不同线程中使用相同的数据库连接或相同的准备语句同时。

Use sqlite3_threadsafe()function to check Sqlite library SQLITE_THREADSAFEcompilation flag.

使用sqlite3_threadsafe()函数检查 Sqlite 库SQLITE_THREADSAFE编译标志。

Default library thread safety behavior can be changed via sqlite3_config(). Use SQLITE_OPEN_NOMUTEXand SQLITE_OPEN_FULLMUTEXflags at sqlite3_open_v2()to adjust the threading mode of individual database connections.

默认库线程安全行为可以通过sqlite3_config(). 使用SQLITE_OPEN_NOMUTEX和SQLITE_OPEN_FULLMUTEX标志 atsqlite3_open_v2()来调整单个数据库连接的线程模式。

回答by Macarse

Check this code from the SQLite wiki.

从 SQLite wiki 检查此代码。

I have done something similar with C and I uploaded the code here.

我用 C 做了类似的事情，我在这里上传了代码。

I hope it's useful.

我希望它有用。

回答by Adrian_H

Summary

概括

Transactions in SQLite are SERIALIZABLE.

SQLite 中的事务是可序列化的。

Changes made in one database connection are invisible to all other database connections prior to commit.

在提交之前，在一个数据库连接中所做的更改对所有其他数据库连接都是不可见的。

A query sees all changes that are completed on the same database connection prior to the start of the query, regardless of whether or not those changes have been committed.

查询会看到在查询开始之前在同一数据库连接上完成的所有更改，无论这些更改是否已提交。

If changes occur on the same database connection after a query starts running but before the query completes, then it is undefined whether or not the query will see those changes.

如果在查询开始运行之后但在查询完成之前在同一数据库连接上发生更改，则不确定查询是否会看到这些更改。

If changes occur on the same database connection after a query starts running but before the query completes, then the query might return a changed row more than once, or it might return a row that was previously deleted.

如果在查询开始运行之后但在查询完成之前在同一数据库连接上发生更改，则查询可能会多次返回更改的行，或者可能返回先前删除的行。

For the purposes of the previous four items, two database connections that use the same shared cache and which enable PRAGMA read_uncommittedare considered to be the same database connection, not separate database connections.

就前四项而言，使用相同共享缓存并启用 PRAGMA 的两个数据库连接read_uncommitted被视为同一个数据库连接，而不是单独的数据库连接。

---

---

In addition to the above information on multi-threaded access, it might be worth taking a look at this page on isolation, as many things have changed since this original question and the introduction of the write-ahead log (WAL).

除了以上关于多线程访问的信息之外，可能值得一看这个关于隔离的页面，因为自从这个原始问题和预写日志（WAL）的引入以来，很多事情都发生了变化。

It seems a hybrid approach of having several connections open to the database provides adequate concurrency guarantees, trading off the expense of opening a new connection with the benefit of allowing multi-threaded write transactions.

似乎将多个连接打开到数据库的混合方法提供了足够的并发保证，在打开新连接的费用与允许多线程写入事务的好处之间进行权衡。