Doug Nebeker wrote:
I've written a C++ wrapper for SQLite which has been working great.
Lately though I've started getting what might be deadlocks when
running
two threads against the same database. One thread is a reader and the
other is a writer (at the moment, I'm getting 'database locked'
errors,
but that's after many hours of failures so not sure where it started).
It's possible to have two or three readers and/or writers, but not
more
than that.
Each thread has it's own database handle, however, I keep the database
handle open for many calls (where each 'call' opens a transaction,
executes SQL statements, then closes the transaction).
I've read about concurrency, and it sounds like the best way to work
is
just to retry the last call if SQLITE_BUSY is ever returned, but that
doesn't seem to be working for me in this case.
Do you, by any chance, have two or more concurrent writers where at
least one performs transactions with the following access pattern:
begin
select
modify (insert, update or delete)
end
If you do, you may end up in a "deadlock" situation. The "funny" writer
starts its read part, acquiring a SHARED lock. At this point, another
writer attempts to write, acquires RESERVED and PENDING locks and waits
for the readers to clear. The "funny" writer now reaches its modify
part, tries to acquire the RESERVED lock and fails (one is already held
by the other writer). At this point, neither thread can progress - no
matter how many times they retry, they'll always get SQLITE_BUSY. The
only way out of this situation is for one of the threads to roll back
its transaction.
One possible way out is to use "BEGIN IMMEDIATE" so start the writer
transactions, especially the "funny" mixed ones.
Igor Tandetnik
