SQLite

View Ticket
Login
Ticket Hash: cafeafe6052e93c1536e96f8411dab8664ee7378
Title: UPDATE with complex WHERE condition on rtree results in "database table is locked" error
Status: Closed Type: Feature_Request
Severity: Critical Priority: Immediate
Subsystem: Extensions Resolution: Wont_Fix
Last Modified: 2019-12-24 02:36:52
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-12-23 23:33:36: (text/x-fossil-wiki)
Consider the following test case:

<pre>
CREATE VIRTUAL TABLE rt0 USING rtree(c0, c1, c2);
INSERT INTO rt0(c0) VALUES (0), (1), (2);
UPDATE rt0 SET c0 = 0 WHERE(SELECT ROW_NUMBER() OVER() FROM rt0); -- database table is locked
</pre>

Somewhat unexpectedly, the UPDATE results in an error "database table is locked". This is not the case for non-virtual and FTS tables. This potential issue seems to be similar to [56a74875be].

drh added on 2019-12-24 02:36:52: (text/x-fossil-wiki)
The documentation has been updated
[https://sqlite.org/draft/rtree.html#readwrite|here]
to explain that it is not generally possible to update the
content of an R-Tree while simultaneously conducting a
scan of that same R-Tree.