On Mar 9, 10:16 pm, "David Crawshaw" <[EMAIL PROTECTED]> wrote:
> I may have to subclass SQLException and throw a LockedException, so
> people can do a neater job of catching it.
This would be ideal, imho.
> But before doing this,consider one important question: why do you have
> a select open for 2-5 seconds? There are some legitimate reasons,
> complex aggregating joins on 100s of millions of records, but most
> likely, you open the select, do a bunch of work not dependent on the
> select being open, and close it. Far better than locking your users up
> for many seconds at a time would be shortening your code path.
>
> d.
What I'm seeing is that the select/insert from the servlet thread is
the one timing out waiting for the worker thread to finish and commit
its log entries to the database. If the servlet thread wins the race
at the start then I have yet to see the 'db locked' exception thrown.
The average run of the worker thread produces ~1100 log entries (each
entry being a line of text, some a child of the previous, etc.). I
learned quickly that auto commit performs horribly in this scenario
with SQLite (though it would alleviate the locking issues) and I
realize why (each commit requires a physical write to disk and
invalidates SQLite's internal caches, etc.). So to counter that, the
worker thread starts a transaction and I commit to the db after every
1000 rows are inserted, before an operation expected to take any
significant time is started (basically this worker may call some
external process to act on a media file of some type, which takes 5-20
minutes per run), and again after the run is complete. If no external
process is to be called on a given run then the average run time is
~2-5 seconds, but is directly proportional to the size of each user's
media file collection. So what takes 2-5 seconds on my system, may
take much less or much, much more time on other systems. Using the
transaction increases the performance by a factor of over 60x or more
on my system (what takes 2-5 seconds now was taking upwards of 2 mins
with auto commit enabled), but the "cost" of this approach is that
during this time, under just the right circumstances, the insert/
delete/select attempts from the servlet thread will timeout. This is
why I'd like an increase in the timeout, which I see I'll have to
build, which isn't a big deal.
Other than perhaps increasing the frequency of log commits by the
worker, I don't know of any other way to handle this using SQLite?
The trick is finding the right size of the log transactions. A 1000
seems high on my system, but most users are going to have media
libraries much larger than the size being used on my test system for
development of the app.
--~--~---------~--~----~------------~-------~--~----~
Mailing List: http://groups.google.com/group/sqlitejdbc?hl=en
To unsubscribe, send email to [EMAIL PROTECTED]
-~----------~----~----~----~------~----~------~--~---
