On 9-nov-2010, at 18:22 Peter Pawlowski wrote:
> While debugging an issue with using SQLite with a Rails
> application, we
> discovered that the behavior of SQLite when setting the
> 'sqlite3_busy_timeout' option was unexpected.
>
> After reading and grokking the SQLite documentation about locking (or
> doing our best to, at least!) it seems that this is the expected
> behavior, but we'd like to confirm.
>
> Basically, we were surprised that in a specific case, an INSERT
> statement will fail immediately and not busy wait, even though a busy
> handler has been set.
>
> For example, given two sqlite3 shell processes, A and B:
>
> A1. begin immediate;
> # => locks the db
> B1. .timeout 5000
> B1. select * from foo;
> # => succeeds
> B2. insert into foo values ("bar");
> # => hangs for 5 seconds, then fails with SQLITE_BUSY
> B3. begin deferred transaction;
> B4. insert into foo values ("bar");
> # => hangs for 5 seconds, then fails with SQLITE_BUSY
> B5. rollback;
> B6. begin deferred transaction;
> B7. select * from foo;
> # => succeeds
> B8. insert into foo values ("bar");
> # => fails immediately with SQLITE_RUBY
> B9. rollback;
>
> The surprising behavior is that step B8 fails immediately and does not
> retry for 5 seconds, which is what we were expecting since the
> busy_timeout is set in this case.
>
> Can someone confirm that this is the intended behavior?
>
> If so, we'd suggest some more documentation about how SQLite behaves
> when a busy handler is set. Of course we'd be happy to help by
> contributing content for the documentation.
>
> If you're interested: The specific consequence of this behavior is
> that
> our Rails application with multiple processes fails with SQLITE_BUSY
> whenever concurrent write requests are made to the database. The Rails
> code allows setting busy_timeout, however it doesn't end up having any
> real effect because all SQLite interactions made by ActiveRecord (the
> Rails database abstraction) end up being complex transactions of type
> 'deferred' (the type is set in the sqlite3-ruby gem). This is probably
> one reason why the Rails folks seem to have the opinion that SQLite is
> not suitable for production systems.
>
> Once I confirm that this is the expected behavior of SQLite, we will
> attempt to address this issue with the sqlite3-ruby or Rails folks,
> since this would clearly need to be addressed at the application
> level.
>
> thanks for your help!
>
> Peter
The immediate busy-error is indeed intended behaviour, or rather a
consequence of the technical design.
It took me a while to find where it is documented. That is not in
"Locking and Concurrency in SQLite version 3" but in the "C/C++ API
Reference". And there it is not in the description of
sqlite3_busy_timeout but under sqlite3_busy_handler. There it is
explained clear enough.
What has surprised me that this deadlock is unchanged when accessing
a database in WAL mode. Also then it is intended behaviour, this was
explained in an earlier discussion. But I don't know if it will
remain like this in future versions.
Best Regards, Edzard.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
