Doug Nebeker <[EMAIL PROTECTED]>
wrote:
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.
If you know up front which transactions are readers-only and which would
write, you could start readers with plain BEGIN and only writers with
BEGIN EXCLUSIVE. Also, BEGIN EXCLUSIVE may be too drastic - BEGIN
IMMEDIATE prevents deadlocks too, and does not block readers. The
difference between IMMEDIATE and EXCLUSIVE is that with the former, you
may get SQLITE_BUSY (but not the deadlock kind) at any place in the
middle of the transaction and will have to implement "wait and retry"
logic. With EXCLUSIVE, you can only get SQLITE_BUSY for the BEGIN
statement itself - once it succeeds, you'll never get SQLITE_BUSY to the
end of the transaction (but of course everyone else will).
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).
My understanding of the previous discussions is as follows. You should
register a busy handler with sqlite3_busy_handler. SQLite encounters a
lock that it believes would clear eventually, it would call your handler
and, if the handler returns zero, return SQLITE_BUSY to the caller. If
however SQLite encounters a deadlock, it will return SQLITE_BUSY without
calling the busy handler. This is your cue that retrying is futile.
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.
I don't think both transactions will get the deadlock cue. The deadlock
occurs when the writer waits for the reader to clear, while the reader
tries to become a writer. Only the latter case is diagnosed as a
deadlock: whoever manages to become writer first will get a regular busy
signal.
So my questions boil down to:
1. When SQLITE_BUSY is returned, is it possible to tell if a deadlock
state has been reached?
Yes, with a careful coordination between the calling code and a busy
handler.
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_reset would be sufficient. After sqlite3_finalize you will have
to prepare the statement again and bind all parameters.
sqlite3_prepare("ROLLBACK TRANSACTION")
sqlite3_step
sqlite3_finalize
...wait... and then try the statement again?
I think you should reset the failed statement before you run ROLLBACK
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??).
See above. In SQLite, deadlock is asymmetric. If coded correctly, you
should not get into a livelock situation.
4. When can sqlite3_prepare return SQLITE_BUSY?
Never, as far as I can tell.
Igor Tandetnik
