database is locked: database is locked

[guojikang]

Hello, our project integrates sqlite_orm, and now we have a problem. Since the business requires many threads to insert data into the database, the system crashes due to the database is locked: database is locked. How can I solve this problem? There are more than 70 tables in the project. Do you have any good suggestions for the encapsulation of make_storage? It is a bit too bloated to write them all together.

[fnc12]

hello. Try to solve this issue without sqlite_orm but with raw libsqlite3. The issue will persist cause one cannot write in different threads simultaneously. One can read from different threads simultaneously for sure for write can be performed only by one thread one time. So if you have a bunch of threads and you expect all of them to write to SQLite you need to either add mutex and check that performance is not reduced or make only one writing thread and pass all write requests to it.

[guojikang]

// 启用 SQLite 的线程安全模式：SQLITE_CONFIG_SERIALIZED
if (sqlite3_config(SQLITE_CONFIG_SERIALIZED) != SQLITE_OK) {
std::cerr << "Failed to configure SQLite for serialized mode." << std::endl;
return 1;
}
DataBaseManager::getStorage().on_open = [](sqlite3 *db) {
sqlite3_exec(db, "PRAGMA cache_size = 1000;", nullptr, nullptr, nullptr);
sqlite3_exec(db, "PRAGMA journal_mode = WAL;", nullptr, nullptr, nullptr);
};
I tried various settings but it still doesn't work. Do I need to re-encapsulate the write operations such as update and insert_range and lock them to solve this problem?

[fnc12]

as I said

hello. Try to solve this issue without sqlite_orm but with raw libsqlite3. The issue will persist cause one cannot write in different threads simultaneously. One can read from different threads simultaneously for sure for write can be performed only by one thread one time. So if you have a bunch of threads and you expect all of them to write to SQLite you need to either add mutex and check that performance is not reduced or make only one writing thread and pass all write requests to it.

[guojikang]

Thank you. Do you have any good suggestions for the encapsulation of orm in the project? I am now piling them all together and then exposing getStorage to the outside world. Thank you very much.

[fnc12]

perform write operations in a single thread

[fnc12]

it is not ORM issue, it is SQLite issue

[guojikang]

I mean this is about 800 lines, not easy to maintain

[fnc12]

Boilerplate code can be refactored by ChatGPT easily

[guojikang]

Many thanks 万分感谢

[guojikang]

According to the author's tips, this problem can be solved by secondary encapsulation of the ORM writing method.