On 13/06/2014 11:04 AM, RSmith wrote:
On 2014/06/13 15:02, Sohail Somani wrote:
My application's architecture looks roughly like this:
- A main thread which does only database reads
- A dedicated database write thread
I needed to create this because while reads are fast, there are
occasions where writes take a significant portion of time and that can
cause the main thread to lock up, which results in a sub-par user
experience.
Let me pause right here. It doesn't matter which thread is writing to
the DB in which Journal mode, the lock is because you cannot change the
state of Data in the DB "while other things are reading it". So whether
it is the same thread or another thread doesn't matter, the moment you
start writing to a DB, the readers have to wait.
There are Pragma settings able to change this behaviour, if you use
proper journal mode and cache-sharing settings in combination with
allowing uncomitted reads, BUT before we even point you to that
literature, let me ask whether it actually matters? In other words,
let's say you have a read operation that is scanning through the table,
would it matter if some of the records are more up-to-date than others?
If this doesn't matter then you might use the said Pragmas to achieve
it. If it /DOES/ matter, then you need to rethink the update concurrency
strategy - such as writing little bits over time in stead of big chunks
etc. (This is a recurring theme on here, so lots of people here, other
than me, are quite knowledgeable on that subject, but a lot more detail
about your strategy or problem is needed).
As to the literature on the said Pragmas, kindly see these links:
http://www.sqlite.org/pragma.html#pragma_read_uncommitted
http://www.sqlite.org/isolation.html
http://www.sqlite.org/sharedcache.html
Have a great day!
Thanks for your response. I had been trying to avoid the "writing little
bits" over time to avoid an inconsistent state in the case of a crash or
user exit, but the inconsistency is not that big of a deal as the data
integrity is still there and eventually, it becomes consistent. So this
solution is not the end of the world.
I actually don't have a problem with readers or writers waiting, but the
problem that I am having is something holds on to the read lock well
beyond where it should. I cannot figure out who (I suspect it's a
third-party framework) and what I'd like to do is figure out what piece
of code is refusing to relinquish the lock regardless of the eventual
strategy I take. Even if I ended up doing everything in the same thread,
with piecemeal updates, this means that there is still something,
somewhere doing the wrong thing and I think that will probably end up
biting me in an unwelcome place.
So rather than actually solve the problem, I'm looking to find the
source of the problem. Does that make sense?
For what it's worth, I did rewire the code to do everything in one
thread, with piecemeal writes and it works fine, but is not a good
solution as far as I am concerned because it is just papering over the
actual problem.
Sohail
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
