SQLite: Sharing Connections across threads to read and write

omggs picture omggs · Jul 31, 2012 · Viewed 8.1k times · Source

I have an application that uses SQLite(version 3.7.2) to store data. I have a SQLite connection shared between multiple threads that writes and reads from the same SQLite db. SQLite is compiled with DSQLITE_THREADSAFE=1 which means SQLite is in Serialized mode.

Quoting from SQLite docs

Serialized: In serialized mode, SQLite can be safely used by multiple threads with no restriction.

On the contrary the SQLite Wiki entry says

Do not use the same database connection at the same time in more than one thread

I tried with a sample application that spawns hundreds of threads and shares an SQLite handle to read & write which is working fine.

So is the SQLite wiki entry outdated or SQLite might not be able to handle read and write happening from different threads at the same time using the same connection?

Answer

Marcelo De Zen picture Marcelo De Zen · Jul 31, 2012

EDIT

DSQLITE_THREADSAFE=2: multi-thread mode The term "multi-thread" is a bit confused in SQLite. Seems like in Multi-thread mode you cannot share a connection with other threads because the connection itself will not use mutexes to prevent one thread to modify the connection while another thread is using it.

DSQLITE_THREADSAFE=1: serialized mode However, in serialized mode, it will lock the datafile and will use mutexes to control the access for the shared connection.

From docs: ... 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.

So, when dealing with connections, serialized mode is thread-safe but multi-thread mode is not, although you still can have multiple connections to the same database.

Source: http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigmultithread

Regards!