Multithreading options?

[Former user]

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

[Former user]

Hi,

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,
Igor

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

[Former user]

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

[Former user]

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

[Former user]

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 http://almworks.com/sqlite4java/javadoc/com/almworks/sqlite4java/SQLiteQueue.html
- 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.

Igor

Reported by [e-mail] on 2015-03-19 21:22:03

[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>() {
@Override
protected SQLiteConnection job(final SQLiteConnection connection) throws SQLiteException {
return connection;
}
}).complete();
}

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.