Multithreading options?

Issue #74 resolved
Former user created an issue

Originally reported on Google Code with ID 74

I would like to be able to use connections in more than one thread. The main reason
for this is that I make extensive use of temporary tables: build a list in a background
thread using a number of different sources then present the results. SqlQueues could
possibly do this if they allow java code to execute, but it seems like a bad idea to
use a database queue for, for example, accessing the internet.

My code base already manages transactions and cross-thread locking of the database,
so I am not particularly concerned about design issues from my side.

What would be the ramifications of simply disabling the threading check (and never
using bulk load)?

Is this a valid build/runtim option to include?

Reported by RabidMutantStargoat on 2015-03-17 23:05:13

Comments (6)

  1. Former user Account Deleted
    I can't tell for sure what the ramifications would be without inspecting your code.
    It's possible if you manage all the access nicely that everything is fine. I wouldn't
    advise it though - it's safer to use an SQLiteQueue.
    I'm not sure if we should include that option, although we might need to review how
    we can improve the support of multi-threaded access. You can try building your own
    fork, with SQLiteConnection.checkThread() commented out and using SQLITE_OPEN_FULLMUTEX
    when opening the database.
    > SqlQueues could possibly do this if they allow java code to execute, but it seems
    like a bad idea to use a database queue for, for example, accessing the internet.
    Sorry, I didn't get it. Why SQLiteQueue doesn't work for you in this case?
    Kind regards,

    Reported by on 2015-03-18 22:20:46 - Status changed: Accepted - Labels added: Type-Other - Labels removed: Type-Defect

  2. Former user Account Deleted
    The reason I believe SQLQueues might be a bad idea is that I assume they are limited
    and/or blocking, and sometimes building one of my temp tables can take a long while
    waiting for other resources (which is why it has its own thread); blocking a DB resource
    while this happens is not ideal.

    Reported by RabidMutantStargoat on 2015-03-19 06:38:37

  3. Former user Account Deleted
    It would be really useful to know if there are there particular calls in sqlite4java
    that are known to be thread-UNsafe.
    I assume the bulk-load stuff is, but anything else?

    Reported by RabidMutantStargoat on 2015-03-19 10:02:56

  4. Former user Account Deleted
    Re: SQLiteQueue - Yes, you surely wouldn't want to do anything blocking in SQLiteJob,
    but you can have your own thread doing internet crawling or whatever, and whenever
    it needs to work with the database, you create a job and execute it synchronously (so
    this thread is waiting for the job result). It's like the example in
    - could be cumbersome, but rock solid.
    I can't say immediately what is thread-unsafe, as off the shelf sqlite4java does not
    support multi-threaded access to the same connection. If SQLite docs are to be believed,
    it won't crash if you open the database with SQLITE_OPEN_FULLMUTEX (whether the app
    will work correctly will be up to the app anyway). So I guess all sqlite4java calls
    that translate directly to specific calls to SQLite should be ok.

    Reported by on 2015-03-19 21:22:03

  5. Kees Wesselius

    I found this thread and wanted to share that when synchronizing using locks, instead of the job queue mechanism, the performance is almost 3x better. I have a wrapper around sqlite4java to abstract away the DB implementation and it also takes care of locking where needed. It fetches the connection like this, and all subsequent calls are done with that connection:

    private SQLiteConnection fetchConnection() {
    return db.handler().execute(new SQLiteJob<SQLiteConnection>() {
    protected SQLiteConnection job(final SQLiteConnection connection) throws SQLiteException {
    return connection;

    I wonder why the job queue mechanism cannot be avoided; I would leave that to the user of the code? I am dying to depend of a solution where the checkThread() can be NOP in case some flag was used in the setup.

  6. Log in to comment