How to create a Task/TPL based work queue/event loop?

Question

I have a system where all database operations (SQLite) must be run through the use of a work processor so i can schedule them however i like.

Right now the processor has a Task<TResult> PostAsync<TResult>(Func<SQLiteConnection, TResult> dbFunc) but the only sane implementation i've been able to come up with is a stack-based UI-thread worker loop that leverages Task to be able to defer the result up to the moment the actual action is processed.

It works fine but it is clogging up the UI thread with the SQLite calls. I'd rather have that not happen but the only idea i have been able to come up with is to spawn a worker thread to flush the queue or to give each action it's own worker thread. I don't think either one of these is the most sane approach as they'd end up creating threads everytime DB access is needed.

How can i achieve an implementation where the dbFunc arguments in the stack are executed in a background thread but said thread is only created once or is as lightweight as possible?

This has to work in a Xamarin environment. Either in PCL or Android+iOS implementations.

Answer 1

You don't need a pump or queue-type solution to accomplish this. Simply serializing access to your SQLiteConnection via a SemaphoreSlim will do the trick (of course you'll need singleton SQLiteConnection and SemaphoreSlim instances):

private readonly SQLiteConnection _sharedConnection;
private readonly SemaphoreSlim Semaphore = new SemaphoreSlim(1, 1);

public async Task<TResult> PostAsync<TResult>(Func<SQLiteConnection, TResult> dbFunc, CancellationToken ct)
{
    TResult result;
    bool needToRelease = false;

    try
    {
        await Semaphore.WaitAsync(ct).ConfigureAwait(false);

        // If we got this far, Release *must* be called.
        needToRelease = true;

        ct.ThrowIfCancellationRequested();

        // Push the work off to the thread pool in the event that
        // WaitAsync completed synchronously and we're still on the UI thread.
        result = await Task
            .Run(() => dbFunc(_sharedConnection), ct)
            .ConfigureAwait(false);
    }
    finally
    {
        if (needToRelease) {
            Semaphore.Release();
        }
    }

    return result;
}

The above will work fine with Xamarin.iOS and Xamarin.Android - however I understand that SemaphoreSlim is not available in some PCL profiles.

You might think that spinning up multiple Task instances every time you need to access the database is a bit rich, but in reality it's not much work, even for a mobile device.

A bigger problem with the above is that it's still "fake" asynchrony. We're just abusing Task.Run to offload blocking operations to the thread pool.

Therefore a more basic alternative (which I myself use) is worth mentioning. It is to expose your SQLiteConnection via a wrapper class where every public method locks on a shared object before executing the relevant method on the SQLiteConnection instance. In this case it's up to the callers whether they want to execute potentially blocking calls directly, or push them off to the thread pool via Task.Run. Until SQLite.Net starts offering true async I believe this to be the best method of managing concurrency with SQLiteConnection.