Hello All,
I'm sure this questions has already been asked thousands of times, but after searching the archive I still don't know the definite answer.
Is it safe to access the same instance of an sqlite3 database (the same pointer returned by sqlite3_open) in several threads, provided that the threads perform proper locking so that only one thread accesses the data structures at one time?
I have read that at least on Linux it's not safe, since the thread identifier is used in the implementation of file locking, and using the sqlite3* in several threads will break locking. But what about Windows? Windows is the only platform I care about, and if it works on Windows (and there are no plans for changes that make it break), it would be okay for me.
I have read the suggestions of letting each thread open the database by itself. However, I'm afraid that this will not lead to the best possible performance. Each sqlite3* instance will have its own page chache, wasting precious memory (which can have quite a bad effect, because too much CPU cache is wasted). So letting all threads share the same sqlite3* seems like a good thing performance-wise.
Another alternative would be to have on dedicated thread for interaction with the database. Other threads interact only with this thread by putting commands into a queue. The db thread one after the other retrieves the commands from the queue, executes them, and returns the result to the issuer of the command. This approach saves memory, but I'm afraid the the thread switching involved in this design causes too much overhead.
Does anyone have some real-life numbers that show how these two approaches scale? The usage pattern in my application is lots of requests with a rather small result (either inserts, or queries with <1000 rows in the result set).
Thanks for your help and best regards
Rolf Schäuble
