SQLite

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

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

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

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?

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.

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.

2. 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.