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
[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
Re: [sqlite] severe slowdown for sub-queries, solved in 3.7?
Michael -- I just wanted to follow up that we used your instructions to patch sqlite and ran our tests again with success. Thanks so much for your help! Peter On 8/11/10 8:17 AM, Black, Michael (IS) wrote: > My patches are awating moderator approval so I figured I'd just send out the > info in case they get killed. > See below to fix your problem with a minimal patch. > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: Black, Michael (IS) > Sent: Tue 8/10/2010 4:41 PM > To: General Discussion of SQLite Database > Subject: RE:[sqlite] severe slowdown for sub-queries, solved in 3.7? > > > The patch that fixed your problem was on 2010-04-07 > > I've attached a patch from 3.6.23 to that version. > > It's 1849 lines of so that's not too bad for QA. > > I've also attached a context diff too. A bit easier to read if you need QA > to look at it. > > The sqlite3 source is too big for an attachment. > > If you want to reproduce you just need to clone the fossil repository and > then update like this: > fossil clone http://www.sqlite.org/src private-project.fossil > fossil open private-project.fossil > fossil update 2010-04-07 > mkdir bld > cd bld > ../configure > make > > If you update to any prior dates your slowdown will appear again. You need > to "make clean" in between fossil updates. > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > > > From: sqlite-users-boun...@sqlite.org on behalf of Peter Pawlowski > Sent: Tue 8/10/2010 2:31 PM > To: General Discussion of SQLite Database > Subject: EXTERNAL:[sqlite] severe slowdown for sub-queries, solved in 3.7? > > > > Hello -- we recently noticed that some SQL statements containing > subqueries (see below) are very slow in 3.6.23 as compared to the > version we were using before, 3.3.17. I tested the newest release of > 3.7.0.1 and the queries are fast again. > > The problem is that this is a showstopper for us, but we cannot > immediately upgrade to 3.7, it is just too new and it takes a lot of QA > resources to do so. We'd prefer a more stable solution in the short term. > > So I'm trying to figure out if this is a known issue that was fixed for > 3.7 in a way that we might be able to patch 3.6. I've been looking for > clues in changelogs and tickets but haven't found anything promising > yet. Can anyone help? > > I'm including an example of our query and database... > > This is slow: > > select count (*) from reporting > left outer join (select * from type) as A0 > on a0.id = reporting.id; > > This is fast: > > select count (*) from reporting > left outer join type as A0 > on a0.id = reporting.id; > > Download the database (~100kb) from here: > > http://peterpawlowski.com/db.gz > > > Thanks, > Peter Pawlowski > > -- > 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 > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- 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
[sqlite] severe slowdown for sub-queries, solved in 3.7?
Hello -- we recently noticed that some SQL statements containing subqueries (see below) are very slow in 3.6.23 as compared to the version we were using before, 3.3.17. I tested the newest release of 3.7.0.1 and the queries are fast again. The problem is that this is a showstopper for us, but we cannot immediately upgrade to 3.7, it is just too new and it takes a lot of QA resources to do so. We'd prefer a more stable solution in the short term. So I'm trying to figure out if this is a known issue that was fixed for 3.7 in a way that we might be able to patch 3.6. I've been looking for clues in changelogs and tickets but haven't found anything promising yet. Can anyone help? I'm including an example of our query and database... This is slow: select count (*) from reporting left outer join (select * from type) as A0 on a0.id = reporting.id; This is fast: select count (*) from reporting left outer join type as A0 on a0.id = reporting.id; Download the database (~100kb) from here: http://peterpawlowski.com/db.gz Thanks, Peter Pawlowski -- 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] different output and performance for my query between 3.3.6 and 3.6.23.1
Pavel,
Thank you so much!! You are right, this change solves all of my
problems. Looking at your suggestion, the syntax makes a lot more sense
this way. I can't tell you how helpful this is.
very best,
Peter
Pavel Ivanov wrote:
> Looking at the query I guess that 3.3.6 processed it incorrectly and
> 3.6.23.1 gives the correct result for it.
> But I also think that it's written incorrectly and not as was
> intended. The correct query will be like this:
>
> select
> count (*) from (select * from reporting) as foo
> left outer join (select id from type) as A0
> on A0.id = foo.id
> left outer join (select id from level) as A1
> on A1.id = foo.id
> left outer join (select id from message_id) as A2
> on A2.id = foo.id
> left outer join (select id from message) as A3
> on A3.id = foo.id
> group by foo.id
>
> And I bet this query will give the same results both on 3.3.6 and on 3.6.23.
>
>
> Pavel
>
>
> On Tue, Apr 27, 2010 at 2:49 PM, Peter Pawlowski <pawlow...@vivisimo.com>
> wrote:
>> I have an unusual query that takes about 15 seconds to process on a tiny
>> database using version 3.6.23.1. In older versions of sqlite3 this query
>> executes in about 20ms and produces different output (I tested 3.3.6).
>>
>> I tried to simplify the query and data as much as possible. The query is
>> weird, I'm not even sure I know what I would expect the output to be.
>> But the fact that the behavior has changed is certain. See my test case
>> below.
>>
>> My question is, is this a bug or is this unsupported or unspecified
>> functionality?
>>
>> thanks!
>> Peter Pawlowski
>>
>>
>> SQL statement
>>
>> select
>> count (*) from (select * from reporting) as foo
>> left outer join (select id from type) as A0
>> left outer join (select id from level) as A1
>> left outer join (select id from message_id) as A2
>> left outer join (select id from message) as A3
>> on ( A0.id = foo.id and A1.id = foo.id and A2.id = foo.id and A3.id
>> = foo.id )
>> group by foo.id
>> ;
>>
>>
>> Test run with sqlite-3.3.6
>>
>> $ sqlite3 -version
>> 3.3.6
>>
>> $ time sqlite3 db < sql
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>> 1
>>
>> real0m0.017s
>> user0m0.001s
>> sys 0m0.009s
>>
>>
>> Test run with sqlite 3.6.23.1
>>
>> $ ./sqlite3 -version
>> 3.6.23.1
>>
>> $ time ./sqlite3 db < sql
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>> 27000
>>
>> real0m15.296s
>> user0m15.144s
>> sys 0m0.030s
>>
>>
>> Database dump
>>
>> BEGIN TRANSACTION;
>> CREATE TABLE reporting ( ID VARCHAR PRIMARY KEY, DATE INTEGER);
>> INSERT INTO "reporting" VALUES('S9XnCAo2JBvX', 1272309512);
>> INSERT INTO "reporting" VALUES('S9XnCAo+bxvX', 1272309512);
>> INSERT INTO "reporting" VALUES('S9XnCQG92xvX', 1272309513);
>> INSERT INTO "reporting" VALUES('S9XnCQH5shvX', 1272309513);
>> INSERT INTO "reporting" VALUES('S9XnCQIBzRvX', 1272309513);
>> INSERT INTO "reporting" VALUES('S9XnCQp6EBvX', 1272309513);
>> INSERT INTO "reporting" VALUES('S9XnCQqmiBvX', 1272309513);
>> INSERT INTO "reporting" VALUES('S9XnCQqvCxvX', 1272309513);
>> INSERT INTO "reporting" VALUES('S9XnCgaTChvX', 1272309514);
>> INSERT INTO "reporting" VALUES('S9XnCga9EhvX', 1272309514);
>> INSERT INTO "reporting" VALUES('S9XnCgbF-hvX', 1272309514);
>> INSERT INTO "reporting" VALUES('S9XnCg1sVRvX', 1272309514);
>> INSERT INTO "reporting" VALUES('S9XnCg2VwxvX', 1272309514);
>> INSERT INTO "reporting" VALUES('S9XnCg2dnxvX', 1272309514);
>> INSERT INTO "reporting" VALUES('S9XnCwgyBxvX', 1272309515);
>> INSERT INTO "reporting" VAL
[sqlite] different output and performance for my query between 3.3.6 and 3.6.23.1
I have an unusual query that takes about 15 seconds to process on a tiny
database using version 3.6.23.1. In older versions of sqlite3 this query
executes in about 20ms and produces different output (I tested 3.3.6).
I tried to simplify the query and data as much as possible. The query is
weird, I'm not even sure I know what I would expect the output to be.
But the fact that the behavior has changed is certain. See my test case
below.
My question is, is this a bug or is this unsupported or unspecified
functionality?
thanks!
Peter Pawlowski
SQL statement
select
count (*) from (select * from reporting) as foo
left outer join (select id from type) as A0
left outer join (select id from level) as A1
left outer join (select id from message_id) as A2
left outer join (select id from message) as A3
on ( A0.id = foo.id and A1.id = foo.id and A2.id = foo.id and A3.id
= foo.id )
group by foo.id
;
Test run with sqlite-3.3.6
$ sqlite3 -version
3.3.6
$ time sqlite3 db < sql
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
real0m0.017s
user0m0.001s
sys 0m0.009s
Test run with sqlite 3.6.23.1
$ ./sqlite3 -version
3.6.23.1
$ time ./sqlite3 db < sql
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
27000
real0m15.296s
user0m15.144s
sys 0m0.030s
Database dump
BEGIN TRANSACTION;
CREATE TABLE reporting ( ID VARCHAR PRIMARY KEY, DATE INTEGER);
INSERT INTO "reporting" VALUES('S9XnCAo2JBvX', 1272309512);
INSERT INTO "reporting" VALUES('S9XnCAo+bxvX', 1272309512);
INSERT INTO "reporting" VALUES('S9XnCQG92xvX', 1272309513);
INSERT INTO "reporting" VALUES('S9XnCQH5shvX', 1272309513);
INSERT INTO "reporting" VALUES('S9XnCQIBzRvX', 1272309513);
INSERT INTO "reporting" VALUES('S9XnCQp6EBvX', 1272309513);
INSERT INTO "reporting" VALUES('S9XnCQqmiBvX', 1272309513);
INSERT INTO "reporting" VALUES('S9XnCQqvCxvX', 1272309513);
INSERT INTO "reporting" VALUES('S9XnCgaTChvX', 1272309514);
INSERT INTO "reporting" VALUES('S9XnCga9EhvX', 1272309514);
INSERT INTO "reporting" VALUES('S9XnCgbF-hvX', 1272309514);
INSERT INTO "reporting" VALUES('S9XnCg1sVRvX', 1272309514);
INSERT INTO "reporting" VALUES('S9XnCg2VwxvX', 1272309514);
INSERT INTO "reporting" VALUES('S9XnCg2dnxvX', 1272309514);
INSERT INTO "reporting" VALUES('S9XnCwgyBxvX', 1272309515);
INSERT INTO "reporting" VALUES('S9XnCwhcEhvX', 1272309515);
INSERT INTO "reporting" VALUES('S9XnDAhuBBvX', 1272309516);
INSERT INTO "reporting" VALUES('S9XnDAh2OBvX', 1272309516);
INSERT INTO "reporting" VALUES('S9XnDQQBVRvX', 1272309517);
INSERT INTO "reporting" VALUES('S9XnDQQu-xvX', 1272309517);
INSERT INTO "reporting" VALUES('S9XnDQQ3BBvX', 1272309517);
INSERT INTO "reporting" VALUES('S9XnDQrNLhvX', 1272309517);
INSERT INTO "reporting" VALUES('S9XnDQr2axvX', 1272309517);
INSERT INTO "reporting" VALUES('S9XnDgFfli74', 1272309518);
INSERT INTO "reporting" VALUES('S9XnDgsHSBvX', 1272309518);
INSERT INTO "reporting" VALUES('S9XnkgP0yBvX', 1272309650);
INSERT INTO "reporting" VALUES('S9Xnkg1QBBvX', 1272309650);
INSERT INTO "reporting" VALUES('S9Xnkg17UxvX', 1272309650);
INSERT INTO "reporting" VALUES('S9Xnkg2W1BvX', 1272309650);
INSERT INTO "reporting" VALUES('S9XnlAKYNFwW', 1272309652);
CREATE TABLE type (id varchar);
INSERT INTO "type" VALUES('S9XnCAo2JBvX');
INSERT INTO "type" VALUES('S9XnCAo+bxvX');
INSERT INTO "type" VALUES('S9XnCQG92xvX');
INSERT INTO "type" VALUES('S9XnCQH5shvX');
INSERT INTO "type" VALUES('S9XnCQIBzRvX');
INSERT INTO "type" VALUES('S9XnCQp6EBvX');
INSERT INTO "type" VALUES('S9XnCQqmiBvX');
INSERT INTO "type" VALUES('S9XnCQqvCxvX');
INSERT INTO "type" VALUES('S9XnCgaTChvX');
INSERT INTO "type" VALUES('S9XnCga9EhvX');
INSERT INTO "type" VALUES('S9XnCgbF-hvX');
INSERT INTO "type" VALUES('S9XnCg1sVRvX');
INSERT INTO "type" VALUES('S9XnCg2VwxvX');
INSERT INTO "type" VALUES('S9XnCg2dnxvX');
INSERT INTO "type" VALUES('S9XnCwgyBxvX');
INSERT INTO "type" VALUES('S9XnCwhcEhvX');
INSERT INTO "type" VALUES('S9XnDAhuBBvX');
INSERT INTO "type" VALUES('S9XnDAh2OBvX');
INSERT INTO "type" VALUES('S9XnDQQBVRvX');
INSERT INTO "type" VALUES('S9XnDQQu-xvX');
INSERT INTO "type" VALUES('S9XnDQQ3BBvX');
INSERT INTO "type" VALUES('S9XnDQrNLhvX');
INSERT INTO "type" VALUES('S9XnDQr2axvX');
INSERT INTO "type" V
