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:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Firecore Sent: Friday, September 07, 2018 1:32 PM To: sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
