如何在多线程应用程序中使用SQLite ?

I'm developing an application with SQLite as 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为数据库的应用程序，并且在理解如何在多个线程中使用它时遇到了一些麻烦(不幸的是，没有其他的堆栈溢出问题真正帮助了我)。

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中读取内容。此外，我偶尔决定更新一组特定的行。为此，我希望生成一个新线程，删除组中的所有行，并重新插入它们(这是在我的应用程序上下文中唯一的方法)。这可能同时发生在不同的组中，因此可能有两个以上的线程试图更新数据库。

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).

我使用每个线程的不同事务，也就是说，在每个线程更新周期的开始，我有了一个开始。实际上，每个线程实际做的是调用“BEGIN”，从数据库中删除它需要“update”的所有行，然后再次将它们插入到新的值中(这是在我的应用程序上下文中必须执行的方式)。

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. 我是否需要调用“open”并从每个线程创建一个新的sqlite结构?
3. 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)?
4. 我是否需要为所有这些添加任何特殊的代码，或者是否足够生成不同的线程、更新行，这很好(因为我使用不同的事务)?
5. 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?
6. 我看到了一些关于不同锁类型的讨论，以及我可能会因为调用某些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部分中:一个保留的锁意味着该进程计划在将来的某个时刻写入数据库文件，但它目前只是从文件中读取。虽然多个共享锁可以与一个保留锁共存，但是一次只能激活一个保留锁。

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.

顺便说一句，到目前为止，谢谢你的回答，他们帮了我很多。

6 个解决方案

#1

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.

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

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

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

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.

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

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

   确保在事务中包含修改数据库文件的命令，如插入、更新、删除等。

#2

Some steps when starting out with SQLlite for multithreaded use:

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

1. Make sure sqlite is compiled with the multi threaded flag.
2. 确保用多线程标记编译sqlite。
3. You must call open on your sqlite file to create a connection on each thread, don't share connections between threads.
4. 必须在sqlite文件上调用open来在每个线程上创建连接，不要在线程之间共享连接。
5. 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.
6. SQLite有一个非常保守的线程模型，当您执行写操作(包括打开即将执行插入/更新/删除操作的事务)时，其他线程将被阻塞，直到此操作完成。
7. 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.
8. 如果不使用事务，则事务是隐式的，因此如果启动插入/删除/更新，sqlite将尝试获取独占锁，并在释放它之前完成操作。
9. 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.
10. 如果您执行一个BEGIN独占语句，它将在执行该事务中的操作之前获得一个独占锁。提交或回滚将释放锁。
11. 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_LOCKED:如果从sqlite3_step语句中获得，则必须在语句句柄上调用sqlite3_reset。您应该只在对sqlite3_step的第一次调用中得到这个，因此一旦调用了reset，您就可以实际地“重试”您的sqlite3_step调用。在其他操作上，它与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.
    • SQLITE_BUSY:不需要调用sqlite3_reset，只需在等待释放锁之后重试您的操作。
12. 您的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，只需在等待释放锁之后重试操作即可。

#3

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

#4

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.

我希望它是有用的。

#5

Modern versions of SQLite has thread safety enabled by default. SQLITE_THREADSAFE compilation flag controls whether or not code is included in SQLite to enable it to operate safely in a multithreaded environment. Default value 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_THREADSAFE compilation flag.

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

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

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

#6

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_uncommitted are 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).

除了以上关于多线程访问的信息之外，您还可以单独查看这个页面，因为自从这个最初的问题和引入write-ahead log (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.

让多个连接对数据库开放似乎是一种混合方法，它提供了足够的并发性保证，以允许多线程写事务的好处换取打开一个新连接的费用。