> -----Original Message-----
> From: [email protected]
> [mailto:[email protected]] 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 );
sqlite> create table source ( name text, value text );
sqlite> insert into source ( name, value ) values ( 'allow', 'yes' );
sqlite> .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;
sqlite> attach database 'db2.db' as src;
sqlite> .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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
