Ensuring exactly one SQLite connection per thread

Question

I'm using Microsoft.Data.Sqlite.Core with SQLitePCLRaw.lib.e_sqlcipher to access an encrypted SQLite database. There are a few challenges I need to address:

I must ensure that the same instance of Microsoft.Data.Sqlite.SqliteConnection is not shared between multiple threads (a SQLite connection is not thread-safe);
Opening a SQLCipher encrypted database is expensive, so I must limit the number of times that happens to the minimum.

I came up with the solution, but I'm not sure how fail proof it is, so I was hoping someone could poke some holes into it.

Nicke · Answer 1 · 2021-04-16T12:04:50.447

I'm faced with the same realization as you and will have to do something similar. I see no issues with your code.

I will try to optimize a bit by using ConcurrentDictionary. It should allow me to avoid locking readers while updating.

If you stay with Dictionary, you may want to change the ContainsKey() call to a TryGetValue() as the documentation suggests it may be more efficient if using a key that does not exist (we would often see new threads?)

In case someone else drives by, here is my background research on the issue: Microsoft.Data.Sqlite derives from ADO.NET objects, e.g. DbConnection that, per design, are not thread safe. The designers of ADO.NET sacrificed thread safy on the altar of high performance. For this reason any code using anything derived from ADO.NET eventually needs to go down this rabbit hole just to make sure really weird stuff do not happen.

As for Microsoft.Data.Sqlite my issue has been SqliteCommand.Dispose that internally encounters a null and crashes. This is when the framework has a lot of parallel calls all on different threads.

You may also note that sqlite itself has multi thread settings and believe that this rabbit hole is the one for you. Unfortunately fiddling with those settings, as beneficial as they may be, does nothing to change the ADO.NET object design and hence the problem remains as long as you use Microsoft.Data.Sqlite to access sqlite.

Regards for posting your code! Hope it made it onto production with great success :-)

/Nicke

Adding after posting: The Dictionary holding references to Thread and SqliteConnection will have an impact on resources since it will stop garbage collection from reclaiming their resources for as long as the reference is there. The same goes for SqliteConnections. Might not be a real problem, but one might also want to think about some kind of clean up algorithm that would remove stale Thread references after some time. SqliteConnections could still be reused if moved over and stored separately.

/Nicke

score 0 · Answer 2 · answered Oct 02 '20 at 09:17

Here is my solution, please let me know if/where I messed up:

public class SqliteConnectionPool
{
    private readonly object lockObject_ = new object();

    private readonly string connectionString_;
    private readonly Dictionary<Thread, SqliteConnection> pool_;

    public SqliteConnectionPool(string connectionString)
    {
        new SqliteConnectionStringBuilder
        {
            ConnectionString = connectionString // throws if connection string is invalid
        };

        connectionString_ = connectionString;
        pool_ = new Dictionary<Thread, SqliteConnection>();
    }

    public SqliteConnection GetConnection()
    {
        lock (lockObject_)
        {
            Thread currentThread = Thread.CurrentThread;

            // If this thread owns a connection, just retrieve it.
            if (pool_.ContainsKey(currentThread))
            {
                return pool_[currentThread];
            }

            // Looking for a thread that doesn't need its connection anymore.
            (Thread inactiveThread, SqliteConnection availableConnection) = pool_.Where(p => p.Key.ThreadState != ThreadState.Running)
                .Select(p => (p.Key, p.Value))
                .FirstOrDefault();

            // If all existing connections are being used, create a new one.
            if (availableConnection is null)
            {
                var connection = new SqliteConnection(connectionString_);
                pool_[currentThread] = connection;
                return connection;
            }

            // Otherwise, just use the existing free connection.
            pool_.Remove(inactiveThread);
            pool_[currentThread] = availableConnection;
            return availableConnection;
        }
    }
}

Ensuring exactly one SQLite connection per thread

2 Answers2