在多线程环境中管理sqlite数据库 [英] Managing sqlite database in a multithreaded environment

问题描述

使用sqlite C ++库时，可以使用sqlite3_open_v2打开数据库.这将产生数据库的句柄，并将设置指向该句柄的指针.

When using the sqlite C++ library, I can use sqlite3_open_v2 to open a database. This will produce an handle to the database, and a pointer to that handle will be set.

使用该指针，我可以调用sqlite3_prepare_v2来准备sqlite语句，然后可以使用sqlite3_step逐步查询结果.

Using that pointer, I can call sqlite3_prepare_v2 to prepare a sqlite statement, then I can use sqlite3_step to step through the results of the query.

现在，我正在一个有多个连续创建和销毁多个线程的环境中工作(这是一个服务器应用程序，它产生新的线程来为传入的(可能是并发的)连接提供服务).现在，据我所知，我应该在每次创建新线程时通过调用sqlite3_open_v2来创建同一数据库的新句柄.但是，这会增加大量的计算开销，因为创建数据库的新连接可能要花一些时间，并且我需要处理 lot 个连接.

Now, I am working in an environment where I have several multiple threads that continuously get created and destroyed (it is a server application that spawns new threads to serve incoming, possibly concurrent connections). Now, as far as my understanding goes, I should be creating new handles to the same database with a call to sqlite3_open_v2 every time a new thread is created. However, this adds a significant computational overhead since it can take a while to create a new connection to the database and I need to handle a lot of connections.

所以我想知道是否有更有效的方法来实现这一目标.例如，是否有一种方法可以使所有内容互斥以解决我的问题?我可以将对我唯一的连接对象的调用互斥:这将我与数据库的通信序列化.

So I was wondering if there was a more efficient way to achieve this. Is there a way, for example, to just mutex everything to solve my problems? I can mutex my calls to the only connection object I have: this serializes my communications with the database.

这项工作吗?还是有一个原因，即使我避免任何形式的并发，也不能从多个不同的线程中使用同一连接对象?

Would this work? Or is there a reason why I can't use the same connection object from several different threads, even if I avoid any form of concurrency?

如果这行得通，我应该序列化对sqlite3_prepare_v2的调用，还是对sqlite3_step的第一个调用，或对sqlite3_step的所有调用?我的意思是:当我第一次调用step时，每次调用step都会加载所有结果或与实际的数据库文件进行通信?

And if this can work, should I just serialize my calls to sqlite3_prepare_v2, or my first call to sqlite3_step, or all my calls to sqlite3_step? I mean: when I call step for the first time, all the results get loaded or communication with the actual database file takes place every time I call step?

区别在于仅将对prepare的调用静音，然后锁定所有内容，直到完成对结果的step ping.

The difference would be between mutexing only the call to prepare, and locking everything until I have finished stepping through the results.

这样可行吗，我应该每次都创建与数据库的新连接并让sqlite处理所有并发性吗?或者我错过了一些可以轻松解决问题的重要内容?

Is something like this feasible, should I just create new connections to the database every time and let sqlite handle all of the concurrency, or am I missing something important that trivially solves my problem?

推荐答案

您可以让sqlite3为您处理所有这些操作，并且默认情况下应该如此. sqlite3库默认应使用 SQLITE_THREADSAFE=1 (强调方式):

You can just let sqlite3 handle all of this for you and by default it should. The sqlite3 libraries should use SQLITE_THREADSAFE=1 by default (empahsis mine):

SQLITE_THREADSAFE =< 0或1或2>
此选项控制是否在SQLite中包含代码以使其能够在多线程环境中安全运行. 默认值为SQLITE_THREADSAFE = 1 ，可以在多线程环境中安全使用.

SQLITE_THREADSAFE=<0 or 1 or 2>
This option controls whether or not code is included in SQLite to enable it to operate safely in a multithreaded environment. The default is SQLITE_THREADSAFE=1 which is safe for use in a multithreaded environment.

并且SQL_CONFIG_SERIALIZED还应该默认情况下也使用(强调我):

And SQL_CONFIG_SERIALIZED should also be used by default also (emphasis mine):

SQLITE_CONFIG_SERIALIZED
此选项没有参数.此选项将线程处理模式设置为序列化".换句话说，此选项启用所有互斥锁，包括数据库连接和准备好的语句对象上的递归互斥锁. 在这种模式下(这是使用SQLITE_THREADSAFE = 1编译SQLite时的默认设置)，SQLite库本身将序列化对数据库连接和已准备好的语句的访问，以便应用程序可以自由使用相同的数据库连接或相同的准备好的语句同时在不同的线程中.

SQLITE_CONFIG_SERIALIZED
There are no arguments to this option. This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. 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.

但是，您也可以在初始化之前通过调用sqlite3_config自己更改它:

However, you can also change it yourself with a call to sqlite3_config before initialisation:

sqlite3_config(SQL_CONFIG_SERIALIZED);

然后，您应该可以使用打开数据库 ://www.sqlite.org/c3ref/c_open_autoproxy.html"rel =" nofollow> SQLITE_OPEN_FULLMUTEX :

You should then be able to open your database using SQLITE_OPEN_FULLMUTEX:

sqlite3* pDatabase;
sqlite3_open_v2("MyDatabase.db", &pDatabase, SQLITE_OPEN_FULLMUTEX, nullptr);

您还可以使用std::mutex阻止访问sqlite3调用，但这不是必需的，因为sqlite3会为您处理该调用(但是如果由于某些原因由于某些原因您以不同的方式构建库，则可能会可行).

You can also use a std::mutex to prevent access to your sqlite3 calls, but this shouldn't be necessary since sqlite3 handles it for you (but if for some reason you have built the libraries differently for some reason, this would be viable).

这篇关于在多线程环境中管理sqlite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！