Hi folks;
I may have found a locking-related bug (or not, depending on your
perspective). In my situation, I am using a system where several databases
have been attached, and I am trying to execute a query which hangs in the
busy handler, ultimately because two locks are attempted on the same file.
I have distilled this down to a toy example for clarity -- it's a toy
example, so nevermind that it may look silly, it's representative of the key
features of this problem.
Setup of the demo db:
sqlite3 db1.db
sqlite> create table dest ( name text, value text );
sqlite> create table source ( name text, value text );
sqlite> insert into source ( name, value ) values ( 'allow', 'yes' );
sqlite> .exit
Setup of the problemattic scenario:
sqlite3 db1.db
sqlite> attach database 'db1.db' as aux;
sqlite> .databases
seq name file
--- ---------------
----------------------------------------------------------
0 main C:\temp\spot\db1.db
2 aux C:\temp\spot\db1.db
OK, nevermind if this seems unusual, this is the scenario the system I am
working with has set up beforehand (it is done under programattic control
and is not always this way depending on other circumstances. It is part of
an attempt to give fixed symbolic names to all dbs, effectively aliasing
'main' to something deterministic).
So, here is the (distilled-down) query I am attempting, which is
deadlocking:
sqlite> insert or replace into main.dest ( name, value ) values
('allow',(select value from aux.source where name = 'allow'));
Error: database is locked
For this query, attempts to acquire locks on both 'main' and 'aux' are made,
but because those are really the same physical database, this fails forever.
Thoughts? Can locking be made more clever to know about aux being an alias
for main, and effectively translate the query shown to it's functional
equivalent of:
insert or replace into main.dest ( name, value ) values ('allow',(select
value from main.source where name = 'allow'));
which does work (or both to 'aux' as well)?
Thanks!
-dave
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
