CODEWITHSUNDEEP
databasemultithreadingsqlitethread-safety
Fabian
Fabian

Reputation: 4211

How threadsafe is SQLite3?

I have a question regarding Multithreading in SQLite3. In my scenario, I have several processes, which want to write into the same SQLite3 database.

In the manual, it says

Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simultaneously in two or more threads.

I have written an example program, where several (say 10) threads INSERT into the same table of the same database using the same database connection.

CREATE TABLE IF NOT EXISTS StatusTable (ID INTEGER PRIMARY KEY, Thread TEXT NOT NULL, Module TEXT NOT NULL, Status INTEGER NOT NULL);

The INSERT is

sprintf(acBuffer, "INSERT INTO StatusTable(Thread, Module, Status) VALUES('%s', 'testnumber%d', %d);", acThread, i, thisThread);

where acThread is a string containing process ID and thread ID, i runs from 1 to 100, and thisThread is the thread ID. The threads do this INSERT 100 times in a while loop and then quit.

According to the above statement, this is not safe although I cannot see any problems.

  1. Using 10 threads with a shared sqlite3 object, each inserting 100 lines: no insertion is lost and no timeout occurs.
  2. Using 10 threads, each with a separate sqlite3 object, each inserting 100 lines: some insertions do not succeed (about 0.5 to 1%) because the database is locked.
  3. Using two processes of case 1: the same occurs as in case 2, there are some losses (about 0.1%).

I am using PRAGMA journal_mode=WAL; and PRAGMA busy_timeout=10000;.

Now my questions:

Upvotes: 15

Views: 17282

Answers (2)

Dennie
Dennie

Reputation: 912

The current SQLite (version 3.23.1) documentation states that the default threading mode is "serialized", and that in this mode:

SQLite can be safely used by multiple threads with no restriction.

Thread safety is controlled at compile-time. You can query the threading mode of your SQLite distribution using:

pragma COMPILE_OPTIONS

Upvotes: 18

H. Guijt
H. Guijt

Reputation: 3365

The documentation (which is written by the people who created this software and lived, breathed, and thought about it for many years) tells you not to share database connections. Why don't you simply believe them? It's not as if it is actually very hard to create multiple database connections.

  1. Yes, it's true. The worst thing that can happen is undefined behaviour, which may or may not include the summoning of black holes, naked singularities, and Cthulhu.

  2. You got lucky. Possibly all hundred inserts in your test already executed before the next thread even started.

  3. You assume correctly. You can avoid the failure by testing for it, and repeating the SQL command if you experience it. Setting a high timeout will not help.

Sqlite is not a particularly good match for any problem that has multithreaded (or multi-process) access to a database, and the "database is locked" message is, in my experience, pretty much unavoidable in such a scenario. You can get better performance by accessing the database from just a single thread, and combining inserts into a single statement (i.e. use a multivalued insert). If that is not enough, hammering a sqlite database with multiple threads will not help performance at all. If you have a use case where such an approach seems necessary, you should consider installing a database built for that purpose, like PostgreSQL.

Upvotes: 20

Related Questions