This group is a wonderful resource. Some day I'll have enough
experience to contribute in a meaningful way...
I have a small number of read and writer threads. A few months ago I
had some deadlock issues and one simple solution was to run all
transactions as exclusive (ie BEGIN EXCLUSIVE). That works
perfectly--no hint of deadlock. But now that I've been using it for a
few months, I'm starting to feel the pain of serializing all calls to
the database, particularly the readers which previously were able to
overlap.
I've read through most of the documentation but haven't quite found the
best way to increase concurrency. I understand that SQLITE_BUSY is
returned when a statement can't be executed because another thread has
temporarily locked the database. What I'm trying to do is figure out
when I need to just wait and try again (my earlier approach which works
great if deadlock isn't the underlying cause) or let go of the current
resources and then try again (so that the other transaction can
complete). And naturally, in the second case, it would be nice for both
transaction to not let go since one of them (assuming only two are
participating in the deadlock) could run to completion once the other
lets go.
So my questions boil down to:
1. When SQLITE_BUSY is returned, is it possible to tell if a deadlock
state has been reached?
2. If I need to 'let go' of the resources/current transaction, do I:
sqlite3_finalize on the current statement which returned
SQLITE_BUSY
sqlite3_prepare("ROLLBACK TRANSACTION")
sqlite3_step
sqlite3_finalize
...wait... and then try the statement again?
3. Is there any kind of algorithm I could use which would direct one
thread in a deadlock to release while the other tries again (anything in
the API that would give a hint??). Maybe looking at which lock you're
trying to get and if it is a lock of type X you always try again, but if
it is a lock of type Y you always give up immediately?
4. When can sqlite3_prepare return SQLITE_BUSY? I can't think of a
case, but I know I had to write code to handle it in my previous
attempts.
Thank you very much in advance for any insight.
To find out more about Reuters visit www.about.reuters.com
Any views expressed in this message are those of the individual sender, except
where the sender specifically states them to be the views of Reuters Ltd.
