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

2021-12-08 00:00:00 multithreading sqlite c++

我正在使用 SQLite 作为数据库开发应用程序，但遇到了一些问题了解如何在多个线程中使用它(不幸的是，其他 Stack Overflow 问题都没有真正帮助我).

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

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

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.

我使用来自每个线程的不同事务，即在每个线程的更新周期开始时，我有一个开始.事实上，每个线程实际做的是调用BEGIN"，从数据库中删除它需要更新"的所有行，然后用新值再次插入它们(这是必须在我的上下文中完成的方式)申请).

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

现在，我正在尝试了解我是如何实现这一点的.我试过四处阅读(Stack Overflow 上的其他答案，SQLite 网站)，但我还没有找到所有的答案.以下是我想知道的一些事情:

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. 我是否需要调用open"并从每个线程创建一个新的 sqlite 结构?
  2. 我是否需要为所有这些添加任何特殊代码，还是足以产生不同的线程、更新行，这很好(因为我使用的是不同的事务)?
  3. 我看到了一些关于不同锁类型的讨论，以及我可能会从调用某些 API 中收到SQLite 忙"的消息，但老实说，我没有看到任何参考资料可以在我需要采取所有这些措施时得到完整的解释考虑到.我需要吗?

如果有人能回答问题/为我指明好的资源方向，我将不胜感激.

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

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

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.

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

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.

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

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.

我理解正确吗?

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

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. 确保您使用 -DTHREADSAFE=1 编译 SQLite.

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

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

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

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

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.

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

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

相关文章