Some queries with ORDER BY and LIMIT lock DB

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Some queries with ORDER BY and LIMIT lock DB

Firecore
Hi all -

It looks like the latest public version of sqlite v3.24.0 has an issue with processing queries with ORDER BY and LIMIT against some datasets. In essence, sqlite3_step returns SQLITE_LOCKED for some of these statments even if there are no other active connections. We were able to reproduce the issue both on iOS via C API and on Mac OS using the console sqlite3 client posted on the Downloads page (https://www.sqlite.org/download.html).

It's easily reproduced with the sqlite3 client and db file available here (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):

sqlite3 lock_issue.db
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
   ...>     LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...>     WHERE meta_tvshow.MetaID='80349'
   ...>     ORDER BY FileIndex.Enabled DESC
   ...>     LIMIT 1;
<this will never return>

We've investigated the problem here and found the following which may be helpful:

 * The db file is valid (at least PRAGMA INTEGRITY_CHECK returns OK).

 * The issue doesn't reproduce if the query doesn't contain ORDER BY or LIMIT clause.

 * The issue doesn't reproduce on sqlite v3.22.0 and older (https://www.sqlite.org/2018/sqlite-tools-osx-x86-3220000.zip).

 * The issue still reproduces after re-creating db from dump using sqlite v3.24.0 on MacOS:

    sqlite3 lock_issue.db
    sqlite> pragma integrity_check;
        ok
    sqlite> .output backup.db
    sqlite> .dump
    sqlite> .q
    sqlite3 fixed.db
    sqlite> .read backup.db
    sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
        ...>     LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
        ...>     WHERE meta_tvshow.MetaID='80349'
        ...>     ORDER BY FileIndex.Enabled DESC
        ...>     LIMIT 1;
    <this will never return>

  * The issue doesn't reproduce after creating index for FileIndex.Enabled and issuing ANALYZE after it:

    sqlite3 lock_issue.db
    sqlite> CREATE INDEX FileIndex_Enabled ON FileIndex(Enabled DESC);
    sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
        ...>     LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
        ...>     WHERE meta_tvshow.MetaID='80349'
        ...>     ORDER BY FileIndex.Enabled DESC
        ...>     LIMIT 1;
    <will never return>
    sqlite> ANALYZE;
    sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
        ...>     LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
        ...>     WHERE meta_tvshow.MetaID='80349'
        ...>     ORDER BY FileIndex.Enabled DESC
        ...>     LIMIT 1;
    <now it returns data as expected>

Do you know what may be going on here, and how we can work around it?

Thanks in advance!

James


Firecore, LLC
https://firecore.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Some queries with ORDER BY and LIMIT lock DB

David Raymond
Other notes:

Order by ASC works ok.
Removing ORDER BY works ok.
Removing limit works ok.
*Limit of more than 1 works ok.

But order by DESC, limit 1 sits forever


As to work around, leave out the limit and just fetch one row, or do "...limit 2" and just use the first.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Firecore
Sent: Friday, September 07, 2018 1:32 PM
To: [hidden email]
Subject: [sqlite] Some queries with ORDER BY and LIMIT lock DB

Hi all -

It looks like the latest public version of sqlite v3.24.0 has an issue with processing queries with ORDER BY and LIMIT against some datasets. In essence, sqlite3_step returns SQLITE_LOCKED for some of these statments even if there are no other active connections. We were able to reproduce the issue both on iOS via C API and on Mac OS using the console sqlite3 client posted on the Downloads page (https://www.sqlite.org/download.html).

It's easily reproduced with the sqlite3 client and db file available here (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):

sqlite3 lock_issue.db
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
   ...>     LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...>     WHERE meta_tvshow.MetaID='80349'
   ...>     ORDER BY FileIndex.Enabled DESC
   ...>     LIMIT 1;
<this will never return>

We've investigated the problem here and found the following which may be helpful:

 * The db file is valid (at least PRAGMA INTEGRITY_CHECK returns OK).

 * The issue doesn't reproduce if the query doesn't contain ORDER BY or LIMIT clause.

 * The issue doesn't reproduce on sqlite v3.22.0 and older (https://www.sqlite.org/2018/sqlite-tools-osx-x86-3220000.zip).

 * The issue still reproduces after re-creating db from dump using sqlite v3.24.0 on MacOS:

    sqlite3 lock_issue.db
    sqlite> pragma integrity_check;
        ok
    sqlite> .output backup.db
    sqlite> .dump
    sqlite> .q
    sqlite3 fixed.db
    sqlite> .read backup.db
    sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
        ...>     LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
        ...>     WHERE meta_tvshow.MetaID='80349'
        ...>     ORDER BY FileIndex.Enabled DESC
        ...>     LIMIT 1;
    <this will never return>

  * The issue doesn't reproduce after creating index for FileIndex.Enabled and issuing ANALYZE after it:

    sqlite3 lock_issue.db
    sqlite> CREATE INDEX FileIndex_Enabled ON FileIndex(Enabled DESC);
    sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
        ...>     LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
        ...>     WHERE meta_tvshow.MetaID='80349'
        ...>     ORDER BY FileIndex.Enabled DESC
        ...>     LIMIT 1;
    <will never return>
    sqlite> ANALYZE;
    sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path, meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from meta_tvshow
        ...>     LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
        ...>     WHERE meta_tvshow.MetaID='80349'
        ...>     ORDER BY FileIndex.Enabled DESC
        ...>     LIMIT 1;
    <now it returns data as expected>

Do you know what may be going on here, and how we can work around it?

Thanks in advance!

James


Firecore, LLC
https://firecore.com
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Some queries with ORDER BY and LIMIT lock DB

Richard Hipp-3
In reply to this post by Firecore
On 9/7/18, Firecore <[hidden email]> wrote:
>
> It's easily reproduced with the sqlite3 client and db file available here
> (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):

I cannot reproduce it.  It always runs instantly for me.  I also ran
it under valgrind with no issues reported.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Some queries with ORDER BY and LIMIT lock DB

Stephen Chrzanowski
I can reproduce this issue with the SQLite3.exe under Win7.  The first
sqlite3 was pulled from a sqlite3.exe I have somewhere in my path, I then
extracted the 3.24.0 version to the Ramdrive and executed from there.  On
the first run, r:\sqlite3.exe did not exist.

R:\>sqlite3 lock_issue.db
SQLite version 3.8.10.1 2015-05-09 12:14:55
Enter ".help" for usage hints.
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path,
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from
meta_tvshow
   ...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...> WHERE meta_tvshow.MetaID='80349'
   ...> ORDER BY FileIndex.Enabled DESC
   ...> LIMIT 1;
B2A4025D165B97BF07B4489B30E4EC6F|/local/californication.S1.E4.mp4|1536215989.63828|778FF248-0A9E-44BE-BDAE-1B413A0C55A0|401460374.0

R:\>sqlite3.exe
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>

R:\>sqlite3.exe lock_issue.db
SQLite version 3.24.0 2018-06-04 19:24:41
Enter ".help" for usage hints.
sqlite> SELECT meta_tvshow.ItemID, meta_tvshow.Path,
meta_tvshow.DateCached, meta_tvshow.VFS_ID, FileIndex.ModificationDate from
meta_tvshow
   ...> LEFT JOIN FileIndex ON meta_tvshow.ItemID = FileIndex.ItemID
   ...> WHERE meta_tvshow.MetaID='80349'
   ...> ORDER BY FileIndex.Enabled DESC
   ...> LIMIT 1;
Error: interrupted <- Pressed CTRL-C after about 5 seconds.
sqlite>


On Fri, Sep 7, 2018 at 3:20 PM, Richard Hipp <[hidden email]> wrote:

> On 9/7/18, Firecore <[hidden email]> wrote:
> >
> > It's easily reproduced with the sqlite3 client and db file available here
> > (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):
>
> I cannot reproduce it.  It always runs instantly for me.  I also ran
> it under valgrind with no issues reported.
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Some queries with ORDER BY and LIMIT lock DB

Richard Hipp-3
On 9/7/18, Stephen Chrzanowski <[hidden email]> wrote:
> I can reproduce this issue with the SQLite3.exe under Win7.
It depends on compile-time options.  I have a repro case now.
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Some queries with ORDER BY and LIMIT lock DB

Tim Streater-3
In reply to this post by Richard Hipp-3
On 07 Sep 2018, at 20:20, Richard Hipp <[hidden email]> wrote:

> On 9/7/18, Firecore <[hidden email]> wrote:
>>
>> It's easily reproduced with the sqlite3 client and db file available here
>> (https://www.dropbox.com/s/l1xofadq7vi5vjj/lock_issue.db?dl=0):
>
> I cannot reproduce it.  It always runs instantly for me.  I also ran
> it under valgrind with no issues reported.

Running macOS Mavericks:

With SQLite version 3.14.0 2016-08-08 13:40:27 (as supplied with the OS) it worked instantly.

With SQLite version 3.24.0 2018-06-04 19:24:41 (downloaded from sqlite.org today) the reported issue occurred.



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Some queries with ORDER BY and LIMIT lock DB

Richard Hipp-3
In reply to this post by Firecore
On 9/7/18, Firecore <[hidden email]> wrote:
>
> It looks like the latest public version of sqlite v3.24.0 has an issue with
> processing queries with ORDER BY and LIMIT against some datasets.

Thanks for the bug report.  The trouble ticket can be found at

   https://www.sqlite.org/src/tktview/9936b2fa443fec03ff25f9b822528c20a2200a49
--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users