#74 Multithreading options?

[Anonymous]

Originally created by: RabidMut...@gmail.com

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?

[Anonymous]

Originally posted by: ser...@almworks.com

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

Labels: -Type-Defect Type-Other
Status: Accepted

[Anonymous]

Originally posted by: RabidMut...@gmail.com

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.

[Anonymous]

Originally posted by: RabidMut...@gmail.com

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?

[Anonymous]

Originally posted by: ser...@almworks.com

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