/ View Ticket
Ticket Hash: 56a74875be799b85e62b024b6466b18af39c75d9
Title: UPDATE causes "database table is locked" for rtree table
Status: Closed Type: Feature_Request
Severity: Minor Priority: Medium
Subsystem: Unknown Resolution: Wont_Fix
Last Modified: 2019-12-24 21:09:41
Version Found In: 3.30.0
User Comments:
mrigger added on 2019-12-19 23:08:58:

The following statements unexpectedly result in a "database table is locked" error:

CREATE VIRTUAL TABLE t0 USING rtree(c0, c1, c2);
INSERT INTO t0(c1) VALUES(0), (0);
UPDATE t0 SET c0 = (SELECT 1 FROM t0); -- unexpected: database table is locked

drh added on 2019-12-21 11:48:45:

We I run the SQL provided on trunk, I get "UNIQUE constraint failed: t0.c0", which is exactly what one would expect.

mrigger added on 2019-12-21 13:35:05:

I still can reproduce the issue on trunk, but I'll check if I have set any special options.

mrigger added on 2019-12-21 23:28:31:

I can still reproduce this on a clean check-out of trunk (e96fe9d946) on Ubuntu 19.04. The stacktrace seems to not be very useful:

#0  sqlite3VdbeError (p=p@entry=0x5588d93e9f40, zFormat=zFormat@entry=0x5588d8f11cb1 "%s") at sqlite3.c:76850
#1  0x00005588d8ed26af in sqlite3VdbeExec (p=0x5588d93e9f40) at sqlite3.c:92020
#2  0x00005588d8ed7790 in sqlite3Step (p=0x5588d93e9f40) at sqlite3.c:82625
#3  sqlite3_step (pStmt=<optimized out>) at sqlite3.c:17154
#4  0x00005588d8e4635d in exec_prepared_stmt (pStmt=0x5588d93e9f40, pArg=0x7fff3e47d160) at shell.c:11380
#5  shell_exec (pArg=0x7fff3e47d160, zSql=<optimized out>, pzErrMsg=0x7fff3e47cf68) at shell.c:11685
#6  0x00005588d8e479a3 in runOneSqlLine (p=0x7fff3e47d160, 
    zSql=0x5588d93d00c0 "UPDATE t0 SET c0 = (SELECT 1 FROM t0); -- unexpected: database table is locked", in=0x0, startline=3)
    at shell.c:18266
#7  0x00005588d8e52067 in process_input (p=0x7fff3e47d160) at shell.c:18366
#8  0x00005588d8e32221 in main (argc=<optimized out>, argv=<optimized out>) at shell.c:19124

Would some debug output (e.g., PRAGMA vdbe_debug = true;) help in finding the cause of this?

drh added on 2019-12-22 00:21:19:

OK. I was compiling with -DSQLITE_ENABLE_EARLY_CURSOR_CLOSE, which prevents the problem.

But any way you slice it, this is a minor issue. I'll look at it later.

drh added on 2019-12-24 21:09:41:

The original fix here was to try to close the read cursor early so that it would not interfere with the update. There are two things wrong with that approach:

  1. It does not always work. See ticket [cafeafe6052e93c1] for an example where it does not.
  1. Yongheng and Rui found a test case for which the early cursor close fails. See check-in [ddb10f0374e66886]

Hence the change that originally "fixed" this ticket has been backed out. This ticket is now a "Wont-Fix".

As described in ticket [cafeafe6052e93c1], the documentation has been updated to point out that it is not possible to update an R-Tree while simultaneously running a query against that R-Tree because the update might radically restructure the R-Tree and disrupt the query.

The RTree extension is currently coded so that any attempt to update the R-Tree fails if there are pending queries. That could be modified so that the update would continue, but it clears all pending cursors and marks them as at end-of-file. Thus an update would disrupt any pending queries. It is not clear that the alternative approach is any better.