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 firstname.lastname@example.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users