On 11 Oct 2011, at 3:19pm, Gal Waldman wrote: > - 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 )
Okay, sorry, you didn't say you were actually getting a problem. A lot of posts here come because people are doing premature optimisation. I now understand you aren't. Have you tried changing the timeout value as described in http://www.sqlite.org/c3ref/busy_timeout.html (your API may not allow this) ? > - 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. If you don't mind inconsistency due to changes not being included in the same transaction, it may be that doing faster changes but leaving a tiny gap between each one will allow your other processes to access the database without exceeding the busy-wait time. When you do your writing to the database are you using transactions ? This considerably reduces the amount of time a database stays locked in total. On the other hand it increases the total number of locks. If you're doing one, and it makes sense within how your code works, try the other. I would also comment that TRIGGERs are slow, and leave the database locked for quite a long time in comparison with single changes. Although you sacrifice some database integrity it might be better to move consistency from TRIGGERs to your programming language, so you can better control when the changes are made. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
