System.Data.SQLite

Hello,

I've been trying to find a way to create an in-memory database that multiple threads can concurrently access. Ideally I would like to do this without any explicit locks in my code and have the database do any locking that is necessary.

I have read that it is dangerous to have threads try to access data through connections opened by other threads.

Is this still the case after SQlite version 3.5.0 (when the shared cache enhancement was made)? If not, what do I need to do to enable a shared in-memory database?

If on the other hand we still need a connection per thread, is there a way to open a connection to an existing inmemory database (created by another thread)?

Thanks a lot,

Vijay

I don't think this is possible.  For one thing, :memory: databases have no underlying locking mechanism.  Your mileage may vary, but I think even if you shared a connection cross-thread (which I do not recommend) I still think you'd hit some locking and race conditions that would make your entire database unstable -- and possibly cause application instability as well.

Thanks Robert. 

In the document "Moving From SQLite 3.4.2 to 3.5.0" ( http://www.sqlite.org/34to35.html ), in the section titled "The Mutex Subsection", it says,

"SQLite has always been threadsafe in the sense that it is safe to use different SQLite database connections in different threads at the same time. The constraint was that the same database connection could not be used in two separate threads at once. SQLite version 3.5.0 relaxes this constraint.

In order to allow multiple threads to use the same database connection at the same time, SQLite must make extensive use of mutexes. And for this reason a new mutex subsystem as been added. The mutex subsystem as the following interface...:"

Are you saying that this is not relevant to :memory: databases?

Thanks again

Vijay

I'm saying I don't know.  A code review would be necessary to find out what, if any, problems could or would occur if you tried to use multiple connections on the same memory database using the shared cache code.

Or, you'd have to ask the sqlite developer mailing list.  I'm just warning you in advance that you could be in for problems if you don't find out up front.