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