Allow better multithreaded use of SqliteConnection

[GoogleCodeExporter]

http://www.sqlite.org/threadsafe.html

Sqlite, by default, is compiled in "serialized mode".  This means that sqlite 
connections can be used by multiple threads with no restrictions.

I would like to get some discussion going to determine if it makes sense for 
this light SQLite wrapper to have similar multithreading capabilities.

Currently, SqliteConnection will ensure that all SQL that is executed will 
execute on the same thread that the connection was opened with.  This doesn't 
seem necessary.  Are these protections left over from previous versions of 
SQLite that were not threadsafe?  What dangers and pitfalls are there for 
allowing an SqliteConnection to be used by multiple threads, besides proper 
locking and maintenance of the state inside of the java object SqliteConnection?

I realize that there is already an SqliteQueue and SqliteJob class that helps 
users dedicate a thread to sqlite and queue jobs on it, but this is not always 
desired or convenient.

Original issue reported on code.google.com by bra...@gmail.com on 25 Oct 2012 at 4:48

[GoogleCodeExporter]

The problem with sharing a connection between the threads is that when used 
carelessly, it may lead to obscure errors that will be hard to find. 

For example, if one thread leaves a transaction going, and the the connection 
is used by another thread, which changes something else, then commits. 99% of 
the time everything will work, but then the second thread will fail and will 
roll back also the changes made by the first thread. 

Or, if you try to issue statements in parallel, the boundaries of transactions 
will be completely undefined.

Another reason is that protecting data structures that are used by sqlite4java 
(including the buffers for bulk load) will require overhead, not sure how 
large, but we try to avoid any overhead.

What is your case for accessing a connection from different threads?

A case for multi-threaded connections that seems to be valid is a connection 
pool. It  should be indeed possible to implement a connection pool, which makes 
sure that no transaction is left open when a connection is returned to the 
pool. We can consider that. However, that would work for concurrent read 
transactions only - making changes is allowed only for a single connection at a 
time by Sqlite.

Cheers,
Igor

Original comment by ser...@gmail.com on 30 Oct 2012 at 1:26

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

[GoogleCodeExporter]

There were no other requests for connection pooling so we'll close this issue 
as wontfix at this time.

Thanks,
Igor

Original comment by ser...@almworks.com on 21 Sep 2014 at 6:33

• Changed state: WontFix