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
--
Peter Pawlowski | Senior Software Engineer
Office: +1.412.422.2499 x116
[email protected] | Connect: www.vivisimo.com
Vivisimo - Information Optimized
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
