On 11 Oct 2011, at 12:28pm, Gal Waldman wrote: > We do encounter locking problems on configuration changes ( currently it is > a single DB file ) > For Statistic write operation optimization we collect all data and write in > a single transaction, we had to do this without any foreign key / trigger > dependencies, which create some DB scheme problems (theoretically)
FOREIGN KEYs must involve TABLEs in the same database file. Remember that any two files can be split up, and one opened on one computer and the other opened on another computer. Since SQLite can have no strategy on what to do if a key table is missing, there's no point in allowing the situation to happen. > The problem with my suggestion is > > - while the DB files are attached ( for example on the web server when > need to supply a mix of configuration + statistics ) there might be a > locking problem, so I need to attach detach based on specific scenario, > wouldn't that have some overhead. SQLite locks only when writing is going on. You can have as many apps reading the database as you like and nothing will get locked. So keep your writing sessions as short as possible. I don't think you need to do any detaching. If a database file is locked, the problems are no worse than if it is detached. And there's an added advantage that once the writing process is finished, the database becomes available immediately without any extra work. I don't think you are gaining anything by detaching a database just because another app has it locked. > - If I wish to preserve logical correlation bewteen the DB files in need > to keep the attachment all around which brings me back to the starting > point. I doubt we're going to understand this unless we know why you want your data in separate files to begin with. > We also checked the option of external table usage, but I am not sure what > is locking scheme for external tables. I don't think there is any advantage to you but we would probably need to know more about why you think you need them. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
