Concurrency with SQLITE

Question

Hi There,

we are planning to use SQLite in our project , and bit confused with the concurrency model of it (because i read many different views on this in the community). so i am putting down my questions hoping to clear off my apprehension.

we are planning to prepare all my statements during the application startup with multiple connection for reads and one connection for write.so basically with this we are create connection and prepare the statement in one thread and use another thread for binding and executing the statement.

i am using C APIs on windows and Linux.

1. Creating connection on one thread and using it in another . does this pose any problem.
2. should i use "Shared cache Mode".
3. i am thinking of using one lock to synchronize between reads and writes and there would not be any sync between Reads. should i sync between reads as well?
4. does concurrent multiple read work on same connection
5. does concurrent multiple read work on different connection

EDIT : one more question , how to validate the connection i,e we are opening the connection at the application startup ,the same connection will be used till the application exits, so in this process, how do we validate the connection before using it

Thanks in Advance

Regards DEE

Answer 1

1) I do not think SQLite uses any thread specific data, therefore creating a connection on one thread and using on another should be fine (They say its so for version 3.5 onwards)

2) I don't think it will have any significant performance benefit to use shared cache mode, experiment and see, it takes only a single statement to enable it per thread

3) You need to use a Single-Writer-Multiple-Reader kind of lock, using simple locks will serialize all reads and writes and nullify any performance benefits of using multiple threads.

4 & 5) Any read operation should work concurrently without any problem.

Answer 2

SQL Lite faq covers threading in detail. Specific FAQ on threads As of 3.3.1 it is safe to do what you say, under certain conditions (see FAQ).