View Ticket
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:

Consider the following test case:

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

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:

The documentation has been updated 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.