> 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.
