SQLite treats each attached database as a separate entity. Attaching the same file twice is just asking for problems.
The query specifies that the destination db be locked for write and the source db for read; which translates to two locks that cannot coexist on one underlying db file. -----Ursprüngliche Nachricht----- Von: dave [mailto:d...@ziggurat29.com] Gesendet: Sonntag, 26. Oktober 2014 00:36 An: 'General Discussion of SQLite Database'; sqlite-...@sqlite.org Betreff: Re: [sqlite] quasi-bug related to locking, and attached databases.... > -----Original Message----- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stephan Beal > Sent: Saturday, October 25, 2014 3:32 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] quasi-bug related to locking,and attached > databases.... ... > > Simon, FYI: this is the "'main' db aliasing" problem i brought up a > couple months ago, which we solved by using your suggestion: re-attach > the db directly after opening it, so that (as Dave said) all DBs have > well-known internal names regardless of what order they get opened in. > > Reproduced here with 3.8.6 on Linux/x64: > > sqlite> insert or replace into main.dest ( name, value ) values > ('allow',(select value from aux.source where name = 'allow')); > Error: database is locked .... Really, it's a fundamental problem irrespective of 'self attached databases', it happens any time you attach a database more than once. I wish I realized that when I first reported it. E.g.: C:\Documents and Settings\person>sqlite3 db2.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table dest ( name text, value text ); create table source sqlite> ( name text, value text ); insert into source ( name, value ) sqlite> values ( 'allow', 'yes' ); .exit C:\Documents and Settings\person>sqlite3 db1.db SQLite version 3.6.16 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> attach database 'db2.db' as dest; attach database 'db2.db' as sqlite> src; .datavases unknown command or invalid arguments: "datavases". Enter ".help" for help sqlite> .databases seq name file --- --------------- ---------------------------------------------------------- 0 main C:\Documents and Settings\person\db1.db 2 dest C:\Documents and Settings\person\db2.db 3 src C:\Documents and Settings\person\db2.db sqlite> insert or replace into dest.dest (name, value) values ('allow',(select value from src.source where name = 'allow' )); SQL error: database is locked I would imagine that a possible clean fix would be for the pager to have a 'lock count', locking only when it goes from 0 to 1. If that is actually workable, then all the rest of sqlite can blythely carry on with no modification. -dave _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
