SQLite transactions in a multi-threaded application

Question

I have done some searching and I know that in order to have one SQLite database file be updated from different threads in the same application, the threads have to share the same SQLiteConnection object. I've taken steps in my application to make that happen.

My question has to do with transactions. I need each thread to perform its updates in one transaction, and I need each transaction to be totally isolated from the others. That is, rolling back or committing a transaction in thread A should have no effect on the work being done by thread B.

Do I have to do anything special to make this happen, or is creating one SQLiteTransaction at the start of the operation in each thread and assigning it to the SQLiteCommand's Transaction property enough?

Answer 1

I may be mistaken but for multi threaded applications I think you have to only have one connection to the database at a time. I can't see how your code prevents 2 threads from both trying to connect to the same database at the same time. If 2 threads do try to get a connection at the same time you the last thread to try to get the connection will get a SQLITE_BUSY response when it tries to open the connection used by the other thread. You want to lock on the creation of the connection as such:

public class ConnectionManager
{

    public static int BusyTimeout { get; set; }

    public static object instanceLock;

    static ConnectionManager()
    {
        instanceLock = new object();
        BusyTimeout = Convert.ToInt32(TimeSpan.FromMinutes(2).TotalMilliseconds);
    }

    public static 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;
    }
}

And here is an example of a data access object that uses the connection manager.

class UserDAO
{


    public void UpdateUser(User user)
    {
        int result = -1;
        string connectionString = "Data Source=C:\\Counter\\Counter.sqlite";
        lock (ConnectionManager.instanceLock)
        {
            using (SQLiteConnection conn = ConnectionManager.CreateConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(conn))
                {
                    cmd.CommandText = "UPDATE counter_user " +
                        "SET runnerfirstname=@runnerfirstname, " +
                        "runnerlastname=@runnerlastname, " +
                        "parentlastname=@parentlastname, " +
                        "parentfirstname=@parentfirstname, " +
                        "runnergrade=@runnergrade, " +
                        "email=@email, " +
                        "laps=@laps, " +
                        "vestnumber=@vestnumber, " +
                        "tagid=@tagid " +
                        "WHERE id=@id";

                    cmd.Prepare();
                    cmd.Parameters.AddWithValue("@runnerfirstname", user.RunnerFirstName);
                    cmd.Parameters.AddWithValue("@runnerlastname", user.RunnerLastName);
                    cmd.Parameters.AddWithValue("@parentlastname", user.ParentLastName);
                    cmd.Parameters.AddWithValue("@parentfirstname", user.ParentFirstName);
                    cmd.Parameters.AddWithValue("@runnergrade", user.RunnerGrade);
                    cmd.Parameters.AddWithValue("@email", user.Email);
                    cmd.Parameters.AddWithValue("@laps", user.Laps);
                    cmd.Parameters.AddWithValue("@vestnumber", user.VestNumber);
                    cmd.Parameters.AddWithValue("@tagid", user.TagId);
                    cmd.Parameters.AddWithValue("@id", user.Id);

                    try
                    {
                        result = cmd.ExecuteNonQuery();
                    }
                    catch (SQLiteException e)
                    {
                        Console.WriteLine("test");
                    }
                }
                conn.Close();
            }
        }
    }

Answer 2

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.