I am using SQLite in memory database for my C++ project. In my project, there will be multiple threads reading and writing the SQLite in memory database concurrently. However, different threads are reading and updating different tables and yet I still get SQLite database table locking error. If I'm not wrong, I know that SQLite will lock the whole database file rather than the tables involved when one writer is writing the tables. I assume it is the same for the in memory database although I'm not sure. So I actually want to create multiple in memory databases to get around this. So my question is is it possible and if possible, how? Thank you in advance.
2
-
possible duplicate of How to use SQLite in a multi-threaded application?mmmmmm– mmmmmm2012-07-14 12:23:34 +00:00Commented Jul 14, 2012 at 12:23
-
I don't understand multiple databases, (unless it's some data migration exercise or the like). Data should be stored in one place only!Martin James– Martin James2012-07-14 15:32:34 +00:00Commented Jul 14, 2012 at 15:32
Add a comment
|
2 Answers
From a holistic perspective, even if SQLite doesn't support table or row level locking (which I'm sure it must do) implementing multiple databases just to get round this sounds like the wrong way to go. It will end up with your application being needlessly complex just to get around a single technical issue.
For example, how will you join tables between multiple databases? How will you synchronise transactions? If you don't need to do that, ask yourself whether you actually need the power of an RDBMS - would a series of objects / collections based on Boost / STL not work as well, and probably faster?
As for how it can be done. Some of the best information is actually on StackOverflow. Take a look at this answer for example.
1 Comment
Mooing Duck
sqlite.org/lockingv3.html sqlite doesn't support table or row level locking. They only recently added file level locking
Something like this:
ATTACH ':memory:' as SCHEMA1
ATTACH 'file:SCHEMA2?mode=memory&cache=shared' as SCHEMA2
