Multithreading with SQLite

[aljoachim]

Hi,

I´m working on an application which has GUI and worker threads. I use SQLite database. Worker and GUI thread might need to access database at the same time (for example worker thread inserts something, while there is DB table open in GUI). From what I have read in SQLite documentation, it should support multithreading. Also, I´m using separate connection for each thread, as I´ve read on multiple forums, and Qt docs. However, when calling query.exec(), it sometimes returns false with message database is locked. Is there additional need for synchronization before each query.exec() call or am I mssing something? All sources suggest that SQLite is perfectly fine for multithreading.

[SGaist]

Hi and welcome to devnet,

Are you also creating your queries using the thread specific connection ?

[aljoachim]

@SGaist thank you.

Yes, I always create queries using connection specific to the current thread.

[SGaist]

Is your database stored on a local drive or on a network drive ?

[aljoachim]

@SGaist local drive.

[ChrisW67]

Sqlite databases will be locked during write operations (Some deeper detail). Attempting to read in this (usually short) period will result in immediate failure (SQLITE_BUSY).
Make sure none of your threads is holding a long-running, uncommitted write transaction.

BTW, unrelenting read operations can also block writes for extended periods.

[DerReisende]

@aljoachim I am using SQLite in a java application where multiple threads concurrently read and write to the database from multiple connections without problems so far (using xerial jdbc sqlite database driver which uses SQLite via JNI API).
I set via SQLite´s pragma statements the following:

- locking_mode = NORMAL
- journal_mode = WAL
- synchronous = OFF --> I know it is dangerous but the speed gain is worth it...

WAL mode might be the one for you if you want to concurrently work on the database, it does not block readers and writers.

[piervalli]

@aljoachim
Show you check the database created with Java this command?

pragma COMPILE_OPTIONS;
PS Run the command with driver Java.

COMPILER=gcc-5.2.0
ENABLE_COLUMN_METADATA
ENABLE_FTS3
ENABLE_FTS5
ENABLE_JSON1
ENABLE_RTREE
THREADSAFE=1

I think that THREADSAFE is different, according with documentation of Sqlite
https://www.sqlite.org/threadsafe.html

I have same problem.

[aljoachim]

@DerReisende The journal_mode = WAL seems to work in most cases, thank you. I wil need to do some further testing and then mark the topic as resolved.

[piervalli]

@aljoachim
If you set journal_mode = WAL we can do the query in multithreading . There are not restrictions.
In my solution when the secondary need an update or insert send the request of main thread and wait the response