Multithreading options?

[GoogleCodeExporter]

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?

Original issue reported on code.google.com by RabidMut...@gmail.com on 17 Mar 2015 at 11:05

[GoogleCodeExporter]

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

Original comment by ser...@almworks.com on 18 Mar 2015 at 10:20

• Changed state: Accepted
• Added labels: Type-Other
• Removed labels: Type-Defect

[GoogleCodeExporter]

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.

Original comment by RabidMut...@gmail.com on 19 Mar 2015 at 6:38

[GoogleCodeExporter]

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?

Original comment by RabidMut...@gmail.com on 19 Mar 2015 at 10:02

[GoogleCodeExporter]

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.htm
l - 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

Original comment by ser...@almworks.com on 19 Mar 2015 at 9:22