Allow better multithreaded use of SqliteConnection

[Brainn]

Originally reported on Google Code with ID 49

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.

Reported by brainn on 2012-10-25 16:48:43

[Igor Sereda]

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

Reported by sereda on 2012-10-30 01:26:00 - Status changed: Accepted - Labels added: Type-Enhancement - Labels removed: Type-Defect

[Former user]

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

Thanks,
Igor

Reported by [e-mail] on 2014-09-21 18:33:01 - Status changed: WontFix