On 11/21/18, Benjamin Stadin <benjamin.sta...@bridging-it.de> wrote:
> Hi,
>
> I've forked libgpkg on Github to merge fixes from other repositories and
> update the embedded SQLite (was at 3.8.). Though in the RTREE tests I get a
> 'database table is locked' error for the update statements (see exact error
> message below).
Perhaps this is a result of the following change:
https://www.sqlite.org/src/info/d4ce66610851c825
That change is actually a bug fix. So we cannot back it out.
Is your application trying to update the r-tree table while
simultaneously reading from the same table? That is the source of the
problem.
The SQLITE_LOCKED_VTAB error will occur on an attempt to write to the
R-Tree. You can perhaps discover what queries are running
concurrently against the R-Tree by querying the sqlite_stat virtual
table (https://www.sqlite.org/stmt.html) whenever you get the
SQLITE_LOCKED_VTAB error. Perhaps:
SELECT sql FROM sqlite_stmt WHERE busy;
It might that the concurrent queries have actually finished for
practical purposes, but the application merely failed to run
sqlite3_reset() or sqlite3_finalize() on the query and thus it
continues to hold locks that prevent writes against the R-Tree.
Perhaps you can work around this by putting an ORDER BY on the read
queries that run in parallel with the writes while making sure the
ORDER BY clause really does require a sort operation to occur. You
can make that guarantee by adding a unary "+" operator in front of one
of the ORDER BY terms. Perhaps: "ORDER BY +rowid". Adding an ORDER
BY in this way will fix the problem because it will run the entire
R-Tree query to completion, storing the results in a buffer for
sorting, prior to returning any rows.
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
