|Title:||UPDATE causes "database table is locked" for rtree table|
|Last Modified:||2019-12-24 21:09:41|
|Version Found In:||3.30.0|
mrigger added on 2019-12-19 23:08:58:
The following statements unexpectedly result in a "database table is locked" error: <pre> 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 </pre>
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: <pre> #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 </pre> 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.