[sqlite] SQLite in multi-thread application

[Simon Slavin]

On 3 Jul 2015, at 4:15pm, ALBERT Aur?lien  
wrote:

> -   Using a mutex, only a single thread can write to the database (but 
> reads can happen during this time)
> 
> But I have sometimes "Database is locked" errors.
> 
> Did I miss something in my configuration ?
> Did I miss something in my mutex protection ?

Set a timeout to a minute or two (really) using this



and see whether anything changes.

Simon.

[sqlite] SQLite in multi-thread application

[Keith Medcalf]

> I'm using SQLite v3.8.8.3 in my muli-threaded application.
> 
> SQLite is configured so these asserts are satisfied :
> 
> assert(sqlite3_threadsafe() > 0);
> assert(sqlite3_config(SQLITE_CONFIG_MULTITHREAD) == SQLITE_OK);
> 
> I have multiple connections to the same database file :
> -   1 single connection is used by only a single thread
> -   1 single thread use only 1 connection
> -   All threads (about 4-8 threads) can read simultaneously
> -   Using a mutex, only a single thread can write to the database (but
> reads can happen during this time)

You need to protect against the same connection being used simultaneously from 
multiple threads.  You do not need to protect against operations on different 
connections being performed simultaneously, each operation using a different 
connection for each entrance.  Multiple threads, provided that only one thread 
per connection has an active call into the SQLITE library at a time is what you 
need to enforce (that is, connections are serially entrant, not multiple 
entrant).

Is there any particular reason why you are using SQLITE_CONFIG_MULTITHREAD and 
not the default SERIALIZED, where SQLITE itself maintains the mutex and makes 
sure you do not use the same connection simultaneously from multiple threads?  
If you use CONFIG_MULTITHREAD and get it wrong, the apocalypse will be upon 
you.  If you use the default SERIALIZED setting, the sqlite3 library will 
ensure that you do not unleash the apocalypse.

> -   1 single connection is used by only a single thread
> -   1 single thread use only 1 connection
> -   All threads (about 4-8 threads) can read simultaneously

I do not quite understand what you are saying.  Are you saying that each thread 
has its own connection, or are you saying that one of the threads uses one 
connection and that all the others use a different, single connection?  If you 
have one connection per thread, you do not need any serialization at all 
(unless you are using shared cache, which is usually a really bad idea, and is 
again often an option chosen "because it is there" rather than because it is 
needed)

> But I have sometimes "Database is locked" errors.

You cannot "read" from the database on connection A, while connection B is 
writing.  Nor can any connection write to the database while any connection is 
reading.  If you wish to do this, you need to enable WAL.

Or set a reasonable timeout on each connection so that it can "wait" for the 
contention to be cleared before returning an error.

> Did I miss something in my configuration ?

Why are you using CONFIG_MULTITHREAD.  While it is more efficient (if coded 
correctly to enforce the entrance requirements of the library) doing so is not 
trivial, and the effort you spend to "get it right" will far outweigh any 
performance increase.  This is called premature optimization.  Is is usually a 
waste of time.  Of course, if you are using one connection per thread *AND* are 
not using shared cache, then you can set CONFIG_MULTITHREAD entrance 
requirements are met by design and checking them with mutexes is a complete 
waste of cycles.

> Did I miss something in my mutex protection ?

Not likely.   You are likely trying to read while the database is locked for 
writing, or write while the database is locked for reading.  This is the 
primary function of a database.  The thing getting the locked error needs to 
retry again later.  Or set a timeout on the connection so that the library does 
this for you.

[sqlite] SQLite in multi-thread application

[ALBERT Aurélien]

Hi,

I'm using SQLite v3.8.8.3 in my muli-threaded application.

SQLite is configured so these asserts are satisfied :

assert(sqlite3_threadsafe() > 0);
assert(sqlite3_config(SQLITE_CONFIG_MULTITHREAD) == SQLITE_OK);

I have multiple connections to the same database file :
-   1 single connection is used by only a single thread
-   1 single thread use only 1 connection
-   All threads (about 4-8 threads) can read simultaneously
-   Using a mutex, only a single thread can write to the database (but 
reads can happen during this time)

But I have sometimes "Database is locked" errors.

Did I miss something in my configuration ?
Did I miss something in my mutex protection ?

Thanks for your help !