Let me rephrase:
- Currently we use a single DB and encounter SQLITE_BUSY more times than we can afford ( we want to let the monitor handle more probes ) - Separating to two different DBs will enable us to separate the "slow" changing part from the "fast" changing part. but as in the FOREIGN KEY example we loose the logical consistency over the DB's - I understand the locking mechanism, the whole point of separating is to achieve the ability to write to the "fast" changing DB (statistics) without locking the "slow" DB - The Idea of using external table was to write the statistics to some ram file so configuration Db would scarcely get locked, If I can even prevent DB locking on write to external Table I don't mind loosing some DB consistency as it for statistics. On Tue, Oct 11, 2011 at 13:56, Simon Slavin <slav...@bigfraud.org> wrote: > > 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 > -- Waldman Gal Products Manager ------------------------------ Mobile +972 522 558885 Fax +972 3 6209277 Email ga...@tagvs.com Skype waldman_gal Site http://www.tagvs.com _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
