Re: [sqlite] specific behavior of busy_timeout with different transaction modes
Thanks, Edzard. For the record, we also found that there is a good explanation of this in the book "Using SQLite" from O'Reilly on pages 153-154. We opened an issue [1] on the Rails project. best, Peter [1] https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets/5941 On 11/10/10 2:10 PM, Edzard Pasma wrote: > 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. > -- 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
Re: [sqlite] specific behavior of busy_timeout with different transaction modes
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
[sqlite] specific behavior of busy_timeout with different transaction modes
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
