如何在多线程应用程序中使用SQLite？ [英] How to use SQLite in a multi-threaded application?

问题描述

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

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

我使用不同的事务从每个线程，I.E。在每个线程的更新周期的开始，我有一个开始。事实上，每个线程实际上做的是调用BEGIN，从数据库中删除它需要更新的所有行，并用新值重新插入它们（这是必须在我的上下文中应用程序）。

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

1. 我需要调用open并从每个线程创建一个新的sqlite结构？


2. 我需要为所有这些添加任何特殊代码，或者足以生成不同的线程，更新行，这很好（因为我使用不同的事务）？


3. 我看到了一些关于不同锁类型的事情，以及我可能会收到SQLite busy从调用某些API的事实，但老实说，我没有看到完全解释当我需要考虑所有这一切。我需要吗？

如果任何人可以回答问题/指向一个好的资源的方向，我会非常感谢。

更新1：从我到目前为止阅读的所有内容中，似乎你不能有两个线程

请参阅： http：// www。 .sqlite.org / lockingv3.html 。在3.0节中：RESERVED锁定意味着进程正计划在将来的某个时刻写入数据库文件，但它当前正在从文件中读取。只有单个RESERVED锁可能一次处于活动状态，但多个SHARED锁可以与单个RESERVED锁共存。

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

我理解的事情吗？

解决方案

感谢您的回答此链接。最简单的方法是自己做锁定，并避免共享线程之间的连接。另一个好的资源可以在这里找到，其结论是：

1. 请确保您正在使用-DTHREADSAFE = 1编译SQLite。






3. 确保处理一个或多个线程在访问数据库文件时发生冲突的可能性



4. 请务必在事务中包含修改数据库文件的命令，例如INSERT，UPDATE，DELETE和其他。

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

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.

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

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:

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?

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.

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.

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.

Have I understood things correctly?

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

解决方案

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.

这篇关于如何在多线程应用程序中使用SQLite？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！