Brad House <[EMAIL PROTECTED]> wrote:
> I've read http://www.sqlite.org/cvstrac/wiki?p=MultiThreading under the
> "Case in point: a benchmark application I've written for this purpose"
> and found that current releases of SQLite do not appear to behave in
> this manner. I cannot find any documentation which clearly states
> the intended behavior.
>
> It appears that if 2 threads start transactions at the same time,
> both inserting into the same table, neither thread can finish until
> one has rolled back.
>
> The first thread succeeds until the COMMIT is issued, then returns BUSY
> on the COMMIT. The second thread keeps returning BUSY on the INSERT
> statement. I've created a test case which will retry on BUSY (up to
> 25x to prevent infinite loops). You'll notice the test ultimately fails.
> Also in this test case, I've created a test which does a ROLLBACK when
> a BUSY is hit just to show that it does succeed.
>
> Is this intended functionality? It appears at least a release at some
> point in time did not behave this way (Jan 10, 2003 from the Wiki).
> Considering
> the second thread never gets a successful response to an INSERT statement,
> it would seem that it should not have tried to obtain a lock on that table,
> preventing the COMMIT from succeeding... but it is... It seems to be a bug
> to me.
>
The behavior is deliberate because it gives you, the programmer,
more control and better concurrency in some situations. But it
can also result in the deadlock behavior that you observe.
You can easily work around this problem by always doing
BEGIN IMMEDIATE
Instead of just
BEGIN
When you start a transaction in which you intend to write.
The plain BEGIN will succeed even if another process is
already expressed and interest in writing to the database.
This allows you to read from the database concurrently
with the writer, if that is what you want to do. But
because another process is already writing, you will not be
able to write. And if you try to write, you will get into a
deadlock.
The BEGIN IMMEDIATE, on the other hand, will fail with SQLITE_BUSY
if another process has already started writing. It will not
succeed until the other process commits. This is the perferred
thing to do if you intend to write within your transaction.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
