On Thu, 2005-07-07 at 12:56 +0200, Gerhard Haering wrote:
> - is it true that after sqlite3_prepare, only the first call to
> sqlite3_step can return SQLITE_BUSY, or can subsequent calls also
> return SQLITE_BUSY?
>
Yes. I think this is true. At least I cannot think of any
path by which you could get SQLITE_BUSY on 2nd or subsequent
calls to sqlite3_step(). All the locks required are obtained
on the very first call.
Note however that a call to sqlite3_exec() is really divided up
into multiple calls to sqlite3_prepare() and sqlite3_step(), one
each for each complete SQL statement in the argument to
sqlite3_exec. So one statement could run to completion then a
second statement could hit a lock. But that cannot happen when
using sqlite3_prepare() directly because sqlite3_prepare() will
only compile one statement at a time.
> - like described in the wiki page, is it true that if sqlite3_step
> returns SQLITE_BUSY, I need to sqlite3_reset or sqlite3_finalize to
> avoid potential deadlocks? If that's true, I will need to
> sqlite3_reset, then rebind the parameters again and try sqlite3_step
> again, right?
This is *not* correct. In fact, I do not see how calling
sqlite3_finalize or sqlite3_reset is even helpful in resolving
the lock issue. To avoid the deadlock, some thread needs to prepare
and run a COMMIT or ROLLBACK. Resetting or finalizing other
statements will not help.
A typical scenario follows. I use the term "thread" to mean either
a traditional thread or a full blown process - both work the same
way.
1. Threads A and B both execute "BEGIN". This acquires no
locks or otherwise touches the database in any way. It just
turns off auto-commit mode in both threads.
2. Threads A and B both execute "SELECT...". This acquires a
shared lock for both threads. This works fine.
3. Threads A and B both execute "UPDATE...". One thread (let's
say thread A) succeeds, the other gets an SQLITE_BUSY reply.
The first thread gets a reserved lock before doing the update.
a reserved lock is a kind of exclusive lock so the other
thread blocks and has to fail. Thread A was successful at
the UPDATE command, but all the changes are still held in
RAM - nothing can be written to disk until thread B releases
its shared lock. If the UPDATE is large so that the changes
will not all fit in SQLite's page cache, then both threads
will get an SQLITE_BUSY return.
4. Thread A tries to COMMIT. It gets an SQLITE_BUSY return
because thread B is still holding a shared lock on the
database.
At this point we are stuck. Either thread A will need to
ROLLBACK to let thread B proceed. Or thread B will have to either
ROLLBACK or COMMIT (the same thing in this case since thread B has
made not changes) to let thread A proceed. One or the other of
the two threads will need to prepare and step a ROLLBACK. Just
resetting or finalizing previous statements and retrying them
will not help.
One way to avoid this scenario is to always use BEGIN EXCLUSIVE
instead of just BEGIN. BEGIN EXCLUSIVE always goes ahead and
gets an exclusive lock on the database file or returns SQLITE_BUSY
if it cannot. That way, you can never get two threads holding
a shared lock and both trying to promote to an exclusive lock.
This avoids the deadlock scenario described above, but it also
reduces the amount of concurrency. So it is a tradeoff.
--
D. Richard Hipp <[EMAIL PROTECTED]>
