How to Use SQLite in a Multi-Threaded Application

How to use SQLite in a multi-threaded application?

Check out this link. The easiest way is to do the locking yourself, and to avoid sharing the connection between threads. Another good resource can be found here, and it concludes with:

1. Make sure you're compiling SQLite with -DTHREADSAFE=1.

2. Make sure that each thread opens the database file and keeps its own sqlite structure.

3. Make sure you handle the likely possibility that one or more threads collide when they access the db file at the same time: handle SQLITE_BUSY appropriately.

4. Make sure you enclose within transactions the commands that modify the database file, like INSERT, UPDATE, DELETE, and others.

using sqlite3 in multithreading

One connection has exactly one transaction, so your program is likely to blow up when multiple threads try to share the same connection without locking around all transactions.

Use one connection per thread.

(If you need high concurrency, SQLite might not be the best choice.)

SQLite transactions in a multi-threaded application

After playing around with this, I have figured out that my earlier conclusion regarding needing to share a single connection across threads was wrong. Each thread now:

1. Uses a special singleton class called ConnectionManager that creates SQLiteConnection instances, opens them, and configures common settings that have to be set after each connection is opened.
2. Creates a new connection before it accesses the database by calling a method in the ConnectionManager class.
3. Starts and manages any transactions in the usual way.
4. Closes and disposes of the SQLiteConnection in the usual way.

The ConnectionManager class allows all code that needs to connect to the database to obtain their connection, with all of the database settings set in a consistent manner. Most of the properties my code needs to set are actually in the connection string, but there is one that can't be specified there, so I needed another mechanism. Here's what the ConnectionManager looks like:

public class ConnectionManager {

    public int BusyTimeout { get; set; }

    public static ConnectionManager Instance {
        get {
            if ( iInstance == null ) {
                lock ( instanceLock ) {
                    if ( iInstance == null )
                        iInstance = new ConnectionManager();
                }
            }
            return iInstance;
        }
    }
    private static ConnectionManager iInstance = null;

    private static object instanceLock;

    private ConnectionManager() {
        BusyTimeout = Convert.ToInt32( TimeSpan.FromMinutes( 2 ).TotalMilliseconds );
    }

    static ConnectionManager() {
        instanceLock = new object();
    }

    public SQLiteConnection CreateConnection( string connectionString ) {
        SQLiteConnection connection = new SQLiteConnection( connectionString );
        connection.Open();

        using ( SQLiteCommand command = connection.CreateCommand() ) {
           command.CommandText = string.Format( "PRAGMA busy_timeout={0}", BusyTimeout );
           command.ExecuteNonQuery();
        }
        return connection;
    }
}

To use the ConnectionManager class, either an instance variable or a local variable is set to a copy of the singleton instance like this:

_connectionManager = ConnectionManager.Instance;

To obtain and use a database connection, code like this is used in each thread:

using ( SQLiteConnection connetion = _connectionManager.CreateConnection( connectionString ) {
    // Thread's database operation code here
}

It turns out the real trick to making it work was to set the busy_timeout pragma to something longer than the default. SQLite internally is fully thread-safe and serializes requests on its own, so your code just needs to tell SQLite to wait for any currently executing operation to finish. Our code is already structured so that failures in the database engine for any reason cause the operation to be retried after waiting a few seconds, so this works well.

The default 2 minute wait time is long enough for 99.99% of all operations to finish. Really, if something take longer than 2 minutes to complete, we need to revisit that area and make it faster any way.

How to share single SQLite connection in multi-threaded Python application

It's not safe to share a connection between threads; at the very least you need to use a lock to serialize access. Do also read http://docs.python.org/2/library/sqlite3.html#multithreading as older SQLite versions have more issues still.

The check_same_thread option appears deliberately under-documented in that respect, see http://bugs.python.org/issue16509.

You could use a connection per thread instead, or look to SQLAlchemy for a connection pool (and a very efficient statement-of-work and queuing system to boot).

Getting SQLite3 to work with multiple threads

The Python sqlite3 module has a threadsafety level of 1, which means that although you can't share database connections between threads, multiple threads can use the module simultaneously. So, you could have each thread create its own connection to the database.

The problem with this approach is that SQLite has poor write concurrency, so having multiple threads doing a ton of INSERTs at once will give you the dreaded “database is locked” error. You can improve things somewhat by using PRAGMA JOURNAL_MODE = 'WAL', but that only goes so far.

If performance is an issue and switching to a client-server database isn't an option, then what you'll probably have to do is keep an in-memory cache of your URLs, and arrange your program so that you have one thread that syncs this cache with the SQLite database.

How to set threading mode in SQLite Java

From the link you posted:

The default mode is serialized.

If you want to explicitly select the serialized mode (or switch back to it), then use SQLiteOpenMode.FULLMUTEX as is also described in the docs.

How should I handle multiple threads accessing a sqlite database in Python?

I was able to write something that meets my needs with the help of this article: http://effbot.org/zone/python-with-statement.htm

My code looks like this:

import threading
import sqlite3

class LockableSqliteConnection(object):
    def __init__(self, dburi):
        self.lock = threading.Lock()
        self.connection = sqlite3.connect(dburi, uri=True, check_same_thread=False)
        self.cursor = None
    def __enter__(self):
        self.lock.acquire()
        self.cursor = self.connection.cursor()
        return self
    def __exit__(self, type, value, traceback):
        self.lock.release()
        self.connection.commit()
        if self.cursor is not None:
            self.cursor.close()
            self.cursor = None

Objects of this class are now directly usable with the with statement:

dburi = "file:TESTING_MEMORY_DB?mode=memory&cache=shared"
lsc = LockableSqliteConnection(dburi)
with lsc:
    lsc.cursor.execute("SQL")

It also conveniently opens a cursor for me and automatically commits changes I make to the database, which means that code which calls my new class is shorter. That's nice, but automatically committing database changes might not actually be the best idea... In more complex scenarios where you might want to start a transaction and then roll it back partway through, that functionality might cause trouble. My SQL needs are very simple, however, and I never roll back, so I've left it in my project for now.

Multithreading in C# sqlite

A clever use of ReaderWriterLockSlim will definitely help you improve performance.

    private ReaderWriterLockSlim _readerWriterLock = new ReaderWriterLockSlim();

    private DataTable RunSelectSQL(string Sql)
    {
        DataTable selectDataTable = null;
        try
        {
            _readerWriterLock.EnterReadLock();                
            //Function to acess your database and return the selected results
        }
        finally
        {
            _readerWriterLock.ExitReadLock();
        }
        return selectDataTable;
    }

    private DataTable RunInsertSQL(string Sql)
    {
        DataTable selectDataTable = null;
        bool isbreaked = false;
        try
        {
            _readerWriterLock.EnterWriteLock();
            if (_readerWriterLock.WaitingReadCount > 0)
            {
                isbreaked = true;
            }
            else
            {
                //Function to insert data in your database
            }
        }
        finally
        {
            _readerWriterLock.ExitWriteLock();
        }

        if (isbreaked)
        {
            Thread.Sleep(10);
            return RunInsertSQL(Sql);
        }
        return selectDataTable;
    }

Try this it will, improve your responsiveness and you have Select query to fire having higher priority over Insert SQL.

Please note, if some insertion is already running then Select will at least wait for that insertion to complete.
This code will always give priority to SELECT over INSERT.

One more point, never perform the long ongoing operation on main thread like you have selecting from database, rather perform the operation in background and then reflect the latest results on UI using main thread. This will ensure that your UI will never freeze.

EDIT There can be a starvation case where all INSERT may be waiting, if there are continuous SELECT queries being fired without any gap.

But I believe in your case, this will not happen as the UI will not always be refreshing to get the latest changes so frequently without any time slice in between.

---

Related Topics