SQLite Forum

table locked when dropping a temp table
Login

table locked when dropping a temp table

(1.1) By Luca (olivluca) on 2020-06-19 13:34:00 edited from 1.0 [link] [source]

I have a table that I want to keep ordered based on an "order" field. I also have a unique index on the same field to ensure that no two records have the same order.
If I want to insert an element, I cannot simply do an

UPDATE variables SET order=order+1 WHERE order>x

because it would fail the unique constraint, so I did this:

BEGIN;
DROP TABLE IF EXISTS x;
CREATE TEMP TABLE x AS SELECT * FROM variables WHERE order>=:order;
UPDATE x SET order=order+1;
DELETE FROM variables WHERE order>=:order;
INSERT INTO variables SELECT * FROM x;
DROP TABLE x;
COMMIT;

and it worked yesterday. Today it says "Table locked" when it tries to DROP TABLE x. I changed it to

BEGIN;
CREATE TEMP TABLE IF NOT EXISTS x AS SELECT * FROM variables LIMIT 1;
DELETE FROM x;
INSERT INTO x SELECT * FROM variables WHERE order>=:order;
UPDATE x SET order=order+1;
DELETE FROM variables WHERE order>=:order;
INSERT INTO variables SELECT * FROM x;
COMMIT;


And this causes no "table locked" error.
Two questions:

1) is there a simpler way (i.t. like PRAGMA defer_foreign_keys = ON but for unique indexes).

2) why the table is locked?

(2) By Richard Damon (RichardDamon) on 2020-06-19 15:56:44 in reply to 1.1 [link] [source]

Not positive on the reason for table locked, but have you made sure to finalize all the queries after running them?

An alternative, where you don't need to keep on renumbering the table would be to store floating-point numbers for order and to put a new item between two existing numbers just take the value that is the midpoint between them. You may still need to occasionally renumber if you start to get too many inserts in one place and start to use too many digits of precision

(3) By Luca (olivluca) on 2020-06-19 16:33:10 in reply to 2 [link] [source]

I'm not 100% sure but I'd say, yes, the component/library I use does that, and, no, I don't want to change the field to float. And I miss the mailing list, with proper threading and proper quoting.

(4) By anonymous on 2020-06-19 17:15:44 in reply to 1.1 [link] [source]

Perhaps the solution suggested by Mark Lawrence would work: https://sqlite.org/forum/forumpost/fae910011b

(7) By Luca (olivluca) on 2020-06-19 19:50:25 in reply to 4 [link] [source]

Yes that works (by chance or by design?) but it has the drawback that I have to specify all the columns in the insert/select, e.g.:

INSERT INTO variables(id, orden, nombre, descripcion, longitud)
SELECT id,orden+1,nombre,descripcion,longitud FROM variables WHERE orden>=6
ORDER BY orden DESC ON CONFLICT(id) DO UPDATE SET orden=excluded.orden;


I'd prefer a query that only "touches" the column(s) I want to update.

(20) By Mark Lawrence (mark) on 2020-07-08 06:25:30 in reply to 7 [link] [source]

The drawback you describe does not actually exist. The only columns you need to include in the INSERT statement are those sufficient to cover the ON CONFLICT.

INSERT INTO variables(id)
SELECT id FROM variables WHERE orden >= 6 ORDER BY orden DESC
ON CONFLICT(id) DO UPDATE SET orden = excluded.orden + 1;

The above should work just as well as your example code with all columns.

(21) By Keith Medcalf (kmedcalf) on 2020-07-08 06:41:36 in reply to 20 [link] [source]

Only if all the other columns are nullable.

(22) By Mark Lawrence (mark) on 2020-07-08 06:59:05 in reply to 21 [link] [source]

Ah yes, you are right, and that can be a bit of a drawback if most of the columns have NOT NULL constraints. But I think ON CONFLICT still beats the alternatives...

(23) By Keith Medcalf (kmedcalf) on 2020-07-08 07:18:04 in reply to 22 [link] [source]

Though, you could of course, do:

INSERT INTO variables
SELECT * FROM variables WHERE orden >= 6 ORDER BY orden DESC
ON CONFLICT(orden) DO UPDATE SET orden = excluded.orden + 1;

as in

sqlite> create table x(id integer primary key, orden integer not null unique, y not null);
sqlite> insert into x (orden, y) select value, randomv(100) from wholenumber where value between 1 and 10;
sqlite> select * from x;
┌────┬───────┬────┐
│ id │ orden │ y  │
├────┼───────┼────┤
│ 1  │ 1     │ 13 │
│ 2  │ 2     │ 96 │
│ 3  │ 3     │ 84 │
│ 4  │ 4     │ 95 │
│ 5  │ 5     │ 36 │
│ 6  │ 6     │ 89 │
│ 7  │ 7     │ 39 │
│ 8  │ 8     │ 94 │
│ 9  │ 9     │ 32 │
│ 10 │ 10    │ 61 │
└────┴───────┴────┘
sqlite> insert into x
   ...> select * from x where orden >= 5 order by orden desc
   ...> on conflict (orden) do update set orden = excluded.orden+1;
sqlite> select * from x;
┌────┬───────┬────┐
│ id │ orden │ y  │
├────┼───────┼────┤
│ 1  │ 1     │ 13 │
│ 2  │ 2     │ 96 │
│ 3  │ 3     │ 84 │
│ 4  │ 4     │ 95 │
│ 5  │ 6     │ 36 │
│ 6  │ 7     │ 89 │
│ 7  │ 8     │ 39 │
│ 8  │ 9     │ 94 │
│ 9  │ 10    │ 32 │
│ 10 │ 11    │ 61 │
└────┴───────┴────┘

(24) By Luca (olivluca) on 2020-07-08 07:50:05 in reply to 23 [link] [source]

Thank you, I'll keep this in mind but I used the negative numbers approach.

(5) By Simon Slavin (slavin) on 2020-06-19 17:35:37 in reply to 1.1 [link] [source]

I can't solve the locking problem but I came up with a faster, more efficiant, way to do what you want. One that won't require a temp table.

Instead of creating a temp table, add a column to your existing table. Call it tempOrder. When you want to do the increment do this:

UPDATE variables SET tempOrder = order + 1 WHERE order >= orderLimit; UPDATE variables SET order = order + 1000000 WHERE orde r>= orderLimit; UPDATE variables SET order = tempOrder, tempOrder = NULL WHERE order >= orderLimit;

You may or may not need the second line depending on how SQLite works. But even with it, those three lines take less time to execute than the version which requires a new table.

By the way, try not to have both a column and a variable with the same name. It makes your code difficult to understand.

(6.1) By Luca (olivluca) on 2020-06-19 18:56:55 edited from 6.0 in reply to 5 [link] [source]

What I thought (but finally didn't implement) was something like

UPDATE variables SET order = order + 1000001 where order>=x;
UPDATE variables SET order = order - 1000000 where order>=1000001+x;

Edit: even simpler, the second line could just be UPDATE variables SET order = order - 1000000 where order>=1000001;

but for some reason I didn't like it, maybe I'll reconsider.

I also tried 

UPDATE variables SET order=order+1 WHERE id IN (SELECT id FROM variables WHERE order>=x ORDER BY order[*] DESC);

but it still caused a conflict.

[*]I the real db the column is called orden, so no conflict with a reserved word.

(8) By Ryan Smith (cuz) on 2020-06-19 22:29:50 in reply to 6.1 [source]

The best way to do this in SQLite I've found is to go negative. I've suggested this before (and used it) for INT primary keys.

The script basically goes like this for your use case:

UPDATE variables SET order = (0 - (order + 1)) WHERE order >= :order;
INSERT INTO variables ( ... the new row with correct order ...);
UPDATE variables SET order = ABS(order) WHERE order < 0;

Easy, Zero duplicate violations, and orders of magnitude faster than temp tables.

PS: Some brackets and spacing added for clarity, it's not all needed.

(9) By Luca (olivluca) on 2020-06-20 06:25:19 in reply to 8 [link] [source]

Good idea, thank you, I'll use it.
But I'm still puzzled about the locked table.

(10) By Ryan Smith (cuz) on 2020-06-20 12:28:10 in reply to 9 [link] [source]

The locked table is a separate and different problem, and is not a peculiarity of SQLite.

That is to say: There is something going on with your code in the process leading up to the locked table that is not clear to us, and very hard to guess at. Put another way - If everything was done right, that should never happen - which leaves us only with the question of what exactly was done wrong? This is not easy to tell from the post. I suppose a process breakdown and some actual code might help.

Best we can do is to tell you this: Some of the typical reasons why a table would be locked in a way that you cannot WRITE to it, are:

  • because the database prepared some statement that intends to WRITE to that table (in the case of WAL journaling) or
  • a read-transaction is in progress (non-WAL), or
  • an immediate transaction was started or
  • some command is complete (has run its course) but the statement is not yet finalized.

These may not cover ALL reasons even, but it is enough to show you that guessing on our part may take weeks without a very exact description of your code and process and concurrency model used.

I can tell you that the typical new-to-SQLite mistake is to forget to finalize a statement, typically one that returned an error and then the execution forks to some error handler that never finalizes the statement, or some such. If that's not it, then we need to see code to try and establish the reason - but I guarantee that there is a reason.

(11) By Luca (olivluca) on 2020-06-20 17:10:24 in reply to 10 [link] [source]

OK, I'm using https://wiki.freepascal.org/ZeosDBO, specifically the TZSQLProcessor component, which takes an sql script, substitutes the passed parameters (everything starting with :) and executes it.

The script is the one I posted originally and everything is in the same transaction. There is no previous error, since as soon as SQlite returns an error the TZSQLProcessor raises an exception, and the execption is raised on the "DROP TABLE x" statement.

I won't say that zeos is bug free, but it's been quite reliable in the 10 years or so that I've been using it with sqlite, and I'm pretty sure it correctly finalizes the statements.

(13) By Ryan Smith (cuz) on 2020-06-20 18:49:20 in reply to 11 [link] [source]

That's good information already.

The easy test is now to take the script as-is, but substitute the parameters with some actual values so that it can be run multiple times using the sqlite3.exe (or whatever CLI you use) and also run it again through the ZeosDB component like that.

Either both of these fail, or both work, or one fails and one works. Either way we will know what is wrong.

  • If both fail, and it can be made to fail for other people, then there is a bug in SQLite or at least the VFS it uses on your system.
  • If Zeos fails and not SQLite, then there is a bug in the ZeosDB system.
  • If SQLite fails and not ZeosDB - we'll all die of surprise.

If it does fail on both - Post the actual DB file (or if it has sensitive info, at least a version of it that has some info obscured but still causes the error).

If CLI's are not your favourite thing, depending on your OS you can use some GUI like SQLitespeed or DB Browser for SQLite (just Google them), both of which are not the final say on anything SQLite-esque, they do however use the API directly and provide error reporting, so an error in them will also provide more clues.

PS: I realize that your problem is technically already solved, but if you are really interested in finding out the reason of the failure - proper debugging is the way to go, and, if it turns out to be an SQLite bug, you'll get some kudos too.

(14) By Luca (olivluca) on 2020-06-20 20:40:25 in reply to 13 [link] [source]

Running the script directly from the shell works.

It also works in a fresh project with only a TZSQLProcessor.

But if I try again in my original project it fails. It must be a bug somewhere else in my code.

Sorry for the noise.

(15) By Luca (olivluca) on 2020-06-20 20:55:09 in reply to 13 [link] [source]

In any case I traced the calls that my project does (using a zeos provided monitor) and I cannot see where the problem is. These are all the calls until the "table is locked" error:

2020-06-20 22:41:50 cat: Connect, proto: sqlite-3, msg: CONNECT TO "/home/luca/Datos/boda_t10_l3_pc/nuevo_comisionado/datos/ajustes.tmp" AS USER ""
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA cache_size = 10000
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA show_datatypes = ON
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA foreign_keys = 1
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 2 : select * from variables order by orden

2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 3 : PRAGMA table_info('variables')
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 3
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 2
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 4 : select * from variables

2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 4
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 5 : select * from tipocampo

2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 6 : PRAGMA table_info('tipocampo')
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 6
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 5
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 7 : select * from campos order by id

2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 8 : PRAGMA table_info('campos')
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 8
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 7
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 9 : select * from valores where campo=? order by orden
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 9 : 1,
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 10 : PRAGMA table_info('valores')
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 10
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 9
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 9 : 1,
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 9
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 11 : select * from campos

2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 11
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 12 : select * from plcs

2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 13 : PRAGMA table_info('plcs')
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 13
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 12
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 14 : select numero,color_fondo, color_letra from casillas where pantalla=? and numero>0 and numero<=? order by numero
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 14 : 0,0,
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 15 : PRAGMA table_info('casillas')
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 15
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 14
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 14 : 0,0,
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 14
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 16 : select * from plcs

2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 16
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 17 : select * from pantallas order by numero

2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 18 : PRAGMA table_info('pantallas')
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 18
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 17
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 14 : 1,9,
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 14
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 19 : select * from casillas where pantalla=? and numero>0 and numero<=? order by numero
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 19 : 1,9,
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 19
2020-06-20 22:41:50 cat: Prepare, proto: sqlite-3, msg: Statement 20 : select * from casillas where pantalla=? and numero=0
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 20 : 1,
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 20
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 14 : 1,9,
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 14
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 19 : 1,9,
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 19
2020-06-20 22:41:50 cat: Bind prepared, proto: sqlite-3, msg: Statement 20 : 1,
2020-06-20 22:41:50 cat: Execute prepared, proto: sqlite-3, msg: Statement 20
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 21 : BEGIN

2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: Statement 21
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 22 : PRAGMA defer_foreign_keys = on

2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: Statement 22
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 23 : UPDATE TriggerControl set enabled=0

2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: Statement 23
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 24 : DROP TABLE IF EXISTS x

2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: Statement 24
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 25 : CREATE TEMP TABLE x AS SELECT * FROM variables WHERE orden>=?
2020-06-20 22:42:18 cat: Bind prepared, proto: sqlite-3, msg: Statement 25 : 4,
2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: Statement 25
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 26 : UPDATE x SET orden=orden+1

2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: Statement 26
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 27 : DELETE FROM variables WHERE orden>=?
2020-06-20 22:42:18 cat: Bind prepared, proto: sqlite-3, msg: Statement 27 : 4,
2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: Statement 27
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 28 : INSERT INTO variables SELECT * FROM x

2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: Statement 28
2020-06-20 22:42:18 cat: Prepare, proto: sqlite-3, msg: Statement 29 : DROP TABLE x

2020-06-20 22:42:18 cat: Execute prepared, proto: sqlite-3, msg: DROP TABLE x
, errcode: 6, error: Error: database table is locked
Message: database table is locked

(16) By Wout Mertens (wmertens) on 2020-06-21 08:52:11 in reply to 15 [link] [source]

Statement 21 is BEGIN and I don't see an END or ROLLBACK.

(17) By Luca (olivluca) on 2020-06-21 10:35:04 in reply to 16 [link] [source]

Well, that's where I stopped the program due to the error.

(18) By doug (doug9forester) on 2020-06-23 03:51:21 in reply to 15 [link] [source]

I carved your data up using Excel, sorted by statement number, and got this: ``` Stmt Command SQL 1 Prepare PRAGMA cache_size = 10000 1 Execute prepared 1 Prepare PRAGMA show_datatypes = ON 1 Execute prepared 1 Prepare PRAGMA foreign_keys = 1 1 Execute prepared 2 Prepare select * from variables order by orden 2 Execute prepared 3 Prepare PRAGMA table_info('variables') 3 Execute prepared 4 Prepare select * from variables 4 Execute prepared 5 Prepare select * from tipocampo 5 Execute prepared 6 Prepare PRAGMA table_info('tipocampo') 6 Execute prepared 7 Prepare select * from campos order by id 7 Execute prepared 8 Prepare PRAGMA table_info('campos') 8 Execute prepared 9 Prepare select * from valores where campo=? order by orden 9 Bind prepared 9 Execute prepared 9 Bind prepared 9 Execute prepared 10 Prepare PRAGMA table_info('valores') 10 Execute prepared 11 Prepare select * from campos 11 Execute prepared 12 Prepare select * from plcs 12 Execute prepared 13 Prepare PRAGMA table_info('plcs') 13 Execute prepared 14 Prepare select numero,color_fondo, color_letra from casillas where pantalla=? and numero>0 and numero<=? order by numero 14 Bind prepared 14 Execute prepared 14 Bind prepared 14 Execute prepared 14 Bind prepared 14 Execute prepared 14 Bind prepared 14 Execute prepared 15 Prepare PRAGMA table_info('casillas') 15 Execute prepared 16 Prepare select * from plcs 16 Execute prepared 17 Prepare select * from pantallas order by numero 17 Execute prepared 18 Prepare PRAGMA table_info('pantallas') 18 Execute prepared 19 Prepare select * from casillas where pantalla=? and numero>0 and numero<=? order by numero 19 Bind prepared 19 Execute prepared 19 Bind prepared 19 Execute prepared 20 Prepare select * from casillas where pantalla=? and numero=0 20 Bind prepared 20 Execute prepared 20 Bind prepared 20 Execute prepared 21 Prepare BEGIN 21 Execute prepared 22 Prepare PRAGMA defer_foreign_keys = on 22 Execute prepared 23 Prepare UPDATE TriggerControl set enabled=0 23 Execute prepared 24 Prepare DROP TABLE IF EXISTS x 24 Execute prepared 25 Prepare CREATE TEMP TABLE x AS SELECT * FROM variables WHERE orden>=? 25 Bind prepared 25 Execute prepared 26 Prepare UPDATE x SET orden=orden+1 26 Execute prepared 27 Prepare DELETE FROM variables WHERE orden>=? 27 Bind prepared 27 Execute prepared 28 Prepare INSERT INTO variables SELECT * FROM x 28 Execute prepared 29 Prepare DROP TABLE x ?? Execute prepared

``` It helped me see what you are doing a little more clearly, but I didn't see a reason for the locked table. (It would be nice to know which table is locked.) When I examined the sequence, statement 14 caught my eye. It has 2 bind parameters in the SQL, and you call Bind 4 times. That's weird. That should have surfaced an error, I think. Are you checking for errors on each call?

(19) By Luca (olivluca) on 2020-06-23 06:05:29 in reply to 18 [link] [source]

Those calls are generated by zeos and, yes, it checks for errors and raises an exception in case there is one.

AFAIK those bind in the logs are internal[*] and do not correspond to the calls to  sqlite3_bind_*

[*]  the TDataset model inherited from delphi has its own mechanism to bind variables, and each database driver then translates it to the underlying one.

(12) By Luca (olivluca) on 2020-06-20 17:13:43 in reply to 10 [link] [source]

I should add that there's no concurrency involved, just a single process with a single thread executing the above code.

(25) By Dan Kennedy (dan) on 2020-07-08 10:51:16 in reply to 1.1 [link] [source]

Attempting to DROP a table gets an SQLITE_LOCKED error if there are any active statements belonging to the same database connection (sqlite3*). This is true even if the table is a TEMP table.

An statement becomes active when sqlite3_step() is called on it. It ceases being active when sqlite3_step() returns something other than SQLITE_ROW, or when sqlite3_reset() or sqlite3_finalize() is called on it.

(26) By anonymous on 2021-12-26 19:49:30 in reply to 1.1 [link] [source]

I got a similar "table locked" error in sqlite3 while trying to DROP a table which is the target of a foreign key configured in another table. So, if tableA has a foreign key specification toward tableB, then tableB cannot be dropped while tableA exists (or at least its Foreign key link).

(27) By Keith Medcalf (kmedcalf) on 2021-12-27 00:03:57 in reply to 26 [link] [source]

That is untrue, at least insofar as the description of the error:

sqlite> create table x(id integer primary key, x integer references y(id));
sqlite> create table y(id integer primary key, y);
sqlite> analyze main;
sqlite> insert into y select value, value from wholenumber where value between 1 and 10;
sqlite> insert into x select value, (value % 10) + 1 from wholenumber where value between 1 and 1000;
sqlite> drop table y;
Error: stepping, FOREIGN KEY constraint failed (19)
sqlite> delete from x;
sqlite> drop table y;
sqlite>

(28) By Larry Brasfield (larrybr) on 2021-12-27 01:09:27 in reply to 27 [link] [source]

In response to:

... So, if tableA has a foreign key specification toward tableB, then tableB cannot be dropped while tableA exists (or at least its Foreign key link).

, it was asserted:

That is untrue, at least insofar as the description of the error:

While Mr. A's statement is somewhat vague and has too many alternatives to be useful, it was at least true. Your demonstration, with its "delete from x", rid that table of all of its "Foreign Key link(s)". And Mr. A's latter alternative, (with a plausible interpretation), is consistent with the point of your demonstration rather than being falsified by it.

A more useful assertion would have been: (With foreign key enforcement enabled,) a table may not be dropped so long as it is referenced in actuality (by one or more rows) within any other table in the same database. That some other table schema(s) provide for such (potential) references is secondary, only relevant in that without such provision no actual, enforceable-by-the-DBMS references can exist.

(29) By Keith Medcalf (kmedcalf) on 2021-12-27 01:15:52 in reply to 28 [link] [source]

If the error message is "Table is locked" then the issue is NOT a foreign key constraint. Violation of a foreign key constraint will raise the error "foreign key constraint failed".

The error "Table is locked" would not be caused by a foreign key constraint. It is caused by an attempt (in this case) to drop a table while it is in use.

Clearly, you cannot drop a table in the midst of it being used.

(30) By anonymous on 2022-02-01 15:36:04 in reply to 29 [link] [source]

I've just seen that with FireDAC in Delphi, I can get a "table locked" exception in this surprising and simple situation:

  1. I accidentally leaked a FireDAC query object that was using some other tables.
  2. I created a temporary table which just contained a list of IDs and has no relationships with any other table. CREATE TEMPORARY TABLE SelectedIDs (id VARCHAR(38))
  3. attempt to drop the temporary table from step 2 (oops: table locked!). DROP TABLE SelectedIDs

when I corrected step 1, the problem was corrected.