SQLite: Sharing Connections across threads to read and write

EDIT

DSQLITE_THREADSAFE=2: multi-thread modeThe 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 modeHowever, 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!

It's a bad idea to share one connection among more than one thread when you have DSQLITE_THREADSAFE=0

Imagine your THREAD 1 is executing this code:

1. connection.setAutoCommit(false);2. statement.executeUpdate(sql);3. connection.commit();

and your THREAD 2 is executing this code at the same time:

1. connection.setAutoCommit(true);

Now what if instruction 1 of THREAD 2 is executed exactly BEFORE instruction 3 of THREAD 1? You'll probably get a SQLException with the message "database in auto-commit mode" (since the auto commit methods are executed upon the same Connection object).

This means one should synchronize his code ore use DSQLITE_THREADSAFE=1

Using connection pooling would also be the best if you're gonna develope multithread code from which you can get better performance when you decide to opt for another DBMS.