How threadsafe is SQLite3?

Question

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:

• Is it true that I should not share connection objects among threads? What bad things can happen if I do?
• Why does the first case (with shared db connection) work (all inserts succeed, no timeout)? What would I have to do to "break" SQLite? What could happen then?
• I assume that each thread should have its own connection. How can I avoid a fail of insertion? In my example, there are only insertions, no readers. The ridiculously high busy_timeout (10 seconds) does not work as expected, there are still "database is locked" insertion fails. I would not mind waiting 10 seconds per insert as long as the insert is successful.

Answer 1

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.

Answer 2

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