On Tue, Oct 11, 2011 at 16:39, Simon Slavin <slav...@bigfraud.org> wrote:
> > 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 Using Busy_handler doesn't helps in my case ( I have already implemented a sleep with growing interval ) as busy scenario itself is what I am trying to prevent. > > (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. > > Is there a way to prevent locking on external table write operation ? > 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. > I guess the issue is data integrity vs Db logical scheme, I thought about the external table as a bypass, but than again I need to implement the DB scheme logic on the application level. > > 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. > > We use transaction with ready made queries for optimization. > 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. > I guess we can drop the triggers although I need mechanism for row level time-stamps I guess I can take it out from the DB > > 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
