Moving database calls to a separate thread, without busy wait

Question

In my app, I need to access a database (I use SQLite). Sometimes DB calls can take some time(even though the DB is local) so I want to avoid blocking the main thread. I want to move my database class. A class that holds the DB connection and actively accesses the database to a separate thread.

So far my approach has been scheduling my DB call on the ThreadPool with the help of QueueUserWorkItem and feeding it a delegate callback to the main thread like so:

DB Class:

public void LoadActorBatchThreading(OnGotActorsFromDBDelegate onGotActorsFromDbDelegate,
                                    string                    searchString = "")
{
    ThreadPool.QueueUserWorkItem(state =>
    {
        var ans = new List<ActorModel>();
        using (var conn = new SqliteConnection(DB_PATH))
        {
            conn.Open();
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText =
                        "SELECT * FROM actor where Actor.name like @Search";


                cmd.Parameters.Add(new SqliteParameter
                {
                        ParameterName = "Search",
                        Value         = $"%{searchString}%"
                });

                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    var actorModel = new PopulateScrollView.ActorModel
                    {
                            Id        = reader.GetInt32(0),
                            Name      = reader.GetString(1),
                            ThumbPath = reader.GetString(4),
                            Gender    = reader.GetInt32(2)
                    };
                    ans.Add(actorModel);
                }
            }
        }
        onGotActorsFromDbDelegate?.Invoke(ans);
    });

It works, but the problem with this is that many DB calls can be sent in parallel. For example, a DB write call can be made while another thread is already writing to the DB. Which would result in an exception.

I want to make something like a job queue on my database class where other classes queue jobs with delegate callbacks and the database class executes those jobs sequentially.

Usually, this is achieved by extending the Thread class and implementing the run() method. Usually with a busy-wait loop.

So first of all, I'm not even sure how to implement this by extending the Thread class, because of all the guides on threading in C# I saw show that Thread must take a delegate to run. And as far as I know, extending Thread is not even an option. But even if it was, I would like to avoid a Busy-Wait loop.

In my mind, this should work like this:

1. Main thread schedules DB job in the DB class job queue.
2. Job scheduling wakes up the DB thread.
3. Db thread executes jobs until the job queue is empty and goes to sleep until the next job scheduling.
4. (While DB thread is executing jobs, the main thread can add more jobs to the queue.)

Though I'm not sure how to achieve this in C#. Any help would be greatly appreciated!

This isn't really relevant to the question but this is the calling code I use in the main thread.

Main Calling Thread:

//delegate declaration
public delegate void OnGotActorsFromDBDelegate(List<ActorModel> actors);

// Calling function
private void LoadBatch(string searchString = "")
{
    var syncContext = SynchronizationContext.Current;
    OnGotActorsFromDBDelegate onGotActorsFromDbDelegate =
            new OnGotActorsFromDBDelegate(actors => syncContext.Post(_ => OnGotActorsFromDb(actors), null));

    DbManager.Instance.LoadActorBatchThreading(onGotActorsFromDbDelegate, searchString);
}


// Db Callback
private void OnGotActorsFromDb(List<ActorModel> actors)
{
    foreach (var i in actors)
    {
        var scrollViewItemInstance =
                GameObject.Instantiate(scrollViewActorThumb, scrollViewContent.transform, true) as GameObject;


        scrollViewItemInstance.GetComponent<ActorThumb>().ActorModel = i;
        scrollViewItemInstance.gameObject.name                       = i.Name;
    }
}

Answer 1

please user async and await its perfect for what you want. or if you dont have experience with that use [Tasks] (https://learn.microsoft.com/en-us/dotnet/standard/parallel-programming/how-to-return-a-value-from-a-task) as described below.

var task2 = Task < List < ActorModel >> .Factory.StartNew(() => {
var ans = new List < ActorModel > ();
using(var conn = new SqliteConnection(DB_PATH)) {
conn.Open();
using(var cmd = conn.CreateCommand()) {
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"SELECT * FROM actor where Actor.name like @Search";
cmd.Parameters.Add(new SqliteParameter{
ParameterName = "Search",
Value = $ "%{searchString}%"
});

var reader = cmd.ExecuteReader();
while (reader.Read()) {
var actorModel = new PopulateScrollView.ActorModel{
Id = reader.GetInt32(0),
Name = reader.GetString(1),
ThumbPath = reader.GetString(4),
Gender = reader.GetInt32(2)
};
ans.Add(actorModel);
}
}
}
onGotActorsFromDbDelegate ? .Invoke(ans);

return ans;
});

Answer 2

You could offload the data access invocations to the ThreadPool by using the Task.Run method, and enforce a no-concurrency policy by using a SemaphoreSlim with capacity 1. To avoid repeating the same boilerplate code on every DB call, you could wrap it inside a generic method like the one below:

private readonly SemaphoreSlim _dbSemaphore = new(1, 1);

public Task<TResult> DBCall<TResult>(Func<TResult> function)
{
    return Task.Run(async () =>
    {
        await _dbSemaphore.WaitAsync().ConfigureAwait(false);
        try { return function(); }
        finally { _dbSemaphore.Release(); }
    });
}

Then you could safely call the database, without blocking the main thread:

int result = await DBCall(() => (int)cmd.ExecuteScalar());

Another idea is to go one step further, and limit all database access to a single dedicated thread. I am not sure if this offers any tangible advantages, but seems a bit tidier and possibly safer as well. To achieve this you could consider implementing a custom TaskScheduler that queues tasks to a long-running thread:

public class SingleThreadTaskScheduler : TaskScheduler, IDisposable
{
    private readonly BlockingCollection<Task> _queue;
    private readonly Thread _thread;

    public SingleThreadTaskScheduler()
    {
        _queue = new BlockingCollection<Task>();
        _thread = new Thread(() =>
        {
            foreach (var task in _queue.GetConsumingEnumerable())
                TryExecuteTask(task);
        })
        { IsBackground = true };
        _thread.Start();
    }

    protected override void QueueTask(Task task) => _queue.Add(task);

    protected override bool TryExecuteTaskInline(Task task,
        bool taskWasPreviouslyQueued) =>
            Thread.CurrentThread == _thread && TryExecuteTask(task);

    public override int MaximumConcurrencyLevel => 1;
    protected override IEnumerable<Task> GetScheduledTasks() => _queue;

    public void Dispose()
    {
        _queue.CompleteAdding();
        _thread.Join();
        _queue.Dispose();
    }
}

The DBCall method can then make use of the custom TaskScheduler like this:

private readonly SingleThreadTaskScheduler _stts = new();
private readonly TaskFactory _singleThreadTaskFactory = new(_stts);

public Task<TResult> DBCall<TResult>(Func<TResult> function)
{
    return _singleThreadTaskFactory.StartNew(function);
}

No locking mechanism is required. The exclusiveness is guaranteed by the task scheduler.

A good idea is to Dispose the SingleThreadTaskScheduler instance before closing the program, in order to ensure that all pending data access operations have completed before exiting. Otherwise the background thread might be aborted in the midst of executing some fired-and-forgotten DB command.