/ 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: (text/x-fossil-wiki)
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: (text/x-fossil-wiki)
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: (text/x-fossil-wiki)
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: (text/x-fossil-wiki)
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: (text/x-fossil-wiki)
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: (text/x-fossil-wiki)
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.