Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQLite: Sharing Connections across threads to read and write

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?

like image 409
omggs Avatar asked Jul 31 '12 11:07

omggs


People also ask

Can SQLite handle multiple connections?

SQLite does support multiple concurrent connections, and therefore it can be used with a multi-threaded or multi-process application. The catch is that when SQLite opens a write transaction, it will lock all the tables.

Does SQLite support concurrent writes?

Overview. Usually, SQLite allows at most one writer to proceed concurrently. The BEGIN CONCURRENT enhancement allows multiple writers to process write transactions simultanously if the database is in "wal" or "wal2" mode, although the system still serializes COMMIT commands.

Does SQLite support multiprocessing?

and to add to that, sqlite works fine in a multi-process environment, as long as your aware that locking may cause some calls to time-out (fail), and that they then need to be re-tried. I know the thread/process -difference, and i use multiple processes (multiprocessing module with pools).

How many concurrent connections can SQLite handle?

The default limit is 1,024.


1 Answers

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!

like image 100
Marcelo De Zen Avatar answered Sep 20 '22 05:09

Marcelo De Zen