Allow better multithreaded use of SqliteConnection

Issue #49 wontfix
Brainn created an issue

Originally reported on Google Code with ID 49

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

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

Comments (2)

  1. 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.

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

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

    Reported by on 2014-09-21 18:33:01 - Status changed: WontFix

  3. Log in to comment