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 pawlow...@vivisimo.com | Connect: www.vivisimo.com Vivisimo - Information Optimized _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users