SQLite Locks In Xamarin.Forms

In this blog, you will learn about SQLite locks in Xamarin.Forms.

Introduction

In mobile projects, whenever we do a parallel or concurrent task and insert data into multiple tables at the same time, then we need to implement locks on SQLite code. The reason behind this is SQLite is a file based database and cannot handle multiple calls to write in it. In Xamarin Forms we are using multiple platforms as Android, iOS and Windows and for this we need to implement dependency injection to initialize the SQLite DB.

We will start by interface first

The interface will have two functions, called "GetAsyncConnection" and "GetConnection". We will implement this function in respective projects as shown below.

using SQLite.Net;
using SQLite.Net.Async;

namespace Sample.Mobile.Services.Common
{
    public interface ISQLite
    {
        SQLiteAsyncConnection GetAsyncConnection();

        SQLiteConnection GetConnection();
    }
}

The Android Project SQLite File

In this we will implement the above interface functions by using dependency injection. In this we will store the DB File in APP folder itself. Here we will have a AsyncLockConnection which will tell the DB that the request will come more than one at the same time, so be ready with Locks. This file will pertain to Android projects.

using System.IO;
using Sample.Mobile.Services.Common;
using SQLite.Net;
using SQLite.Net.Async;
using Xamarin.Forms;
using Sample.Mobile.Droid.Helper;

[assembly: Dependency(typeof(SqLiteAndriod))]

namespace Sample.Mobile.Droid.Helper
{
    public class SqLiteAndriod : ISQLite
    {
        public SqLiteAndriod()
        {

        }

        public SQLiteAsyncConnection GetAsyncConnection()
        {
            const string fileName = "BPMobile.db3";

            var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, fileName);

            var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
            var cwLock = new SQLiteConnectionWithLock(platform, new SQLiteConnectionString(path, true));
            var connection = new SQLiteAsyncConnection(() => cwLock);

            return connection;
        }

        public SQLiteConnection GetConnection()
        {
            const string fileName = "BPMobile.db3";

            var documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, fileName);

            var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid();
            var connection = new SQLiteConnection(platform, path);

            return connection;
        }
    }
}

The iOS Project SQLite File

In this we will implement the above interface functions by using dependency injection. In this we will store the DB File in APP folder itself. Here we will have a AsyncLockConnection which will tell the DB that the request will come more than one at same time, so be ready with Locks. This file will pertain to iOS projects

using Sample.Mobile.Services.Common;
using System;
using SQLite.Net;
using System.IO;
using Xamarin.Forms;
using Sample.Mobile.iOS.Helper;
using SQLite.Net.Async;

[assembly: Dependency(typeof(SqLiteiOS))]
namespace Sample.Mobile.iOS.Helper
{
    public class SqLiteiOS : ISQLite
    {
        public SqLiteiOS()
        {

        }

        public SQLiteAsyncConnection GetAsyncConnection()
        {
            const string fileName = "BPMobile.db3";
            var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, fileName);
            var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();
            var cwLock = new SQLiteConnectionWithLock(platform, new SQLiteConnectionString(path, true));
            var connection = new SQLiteAsyncConnection(() => cwLock);
            return connection;
        }

        public SQLiteConnection GetConnection()
        {
            const string fileName = "BPMobile.db3";
            var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, fileName);
            var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS();
            var connection = new SQLiteConnection(platform, path);
            return connection;
        }
    }
}

The Base Operations File

In this we’ll have a common function to insert a single entity or multiple entity. This file will be in your repository or services. In this we have two functions for inserting a single entity and multiple entity (List<entity>).

using System.Collections.Generic;
using System.Threading.Tasks;
using Sample.Mobile.Services.Async;
using Sample.Mobile.Services.Common;
using SQLite.Net;
using SQLite.Net.Async;
using Xamarin.Forms;

namespace Sample.Mobile.Services.TableOperations
{
    public class BaseOperations
    {
        public SQLiteAsyncConnection Connection;
        private static readonly AsyncLock AsyncLock = new AsyncLock();

        public BaseOperations()
        {
            Connection = DependencyService.Get<ISQLite>().GetAsyncConnection();
        }

        public async Task<int> InsertAsync<T>(T entity)
        {
            try
            {
                using (await AsyncLock.LockAsync())
                {
                    if (entity != null) await Connection.InsertAsync(entity);
                    return 1;
                }
            }
            catch (SQLiteException sqliteException)
            {
                if (sqliteException.Result == SQLite.Net.Interop.Result.Busy ||
                    sqliteException.Result == SQLite.Net.Interop.Result.Constraint)
                {
                    return await InsertAsync(entity);
                }
                throw;
            }

        }

        public async Task<int> InsertAllAsync<T>(List<T> entityList)
        {
            try
            {
                using (await AsyncLock.LockAsync())
                {
                    if (Connection != null) await Connection.InsertAllAsync(entityList);
                    return 1;
                }
            }
            catch (SQLiteException sqliteException)
            {
                if (sqliteException.Result == SQLite.Net.Interop.Result.Busy ||
                    sqliteException.Result == SQLite.Net.Interop.Result.Constraint)
                {
                    return await InsertAsync(entityList);
                }
                throw;
            }
        }
    }
}

The AsyncLock File

This file will be in your Repository or Services.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Sample.Mobile.Services.Async
{
    public class AsyncLock
    {
        private readonly AsyncSemaphore m_semaphore;
        private readonly Task<Releaser> m_releaser;

        public AsyncLock()
        {
            m_semaphore = new AsyncSemaphore(1);
            m_releaser = Task.FromResult(new Releaser(this));
        }

        public Task<Releaser> LockAsync()
        {
            var wait = m_semaphore.WaitAsync();
            return wait.IsCompleted ?
                m_releaser :
                wait.ContinueWith((_, state) => new Releaser((AsyncLock)state),
                    this, CancellationToken.None,
                    TaskContinuationOptions.ExecuteSynchronously, TaskScheduler.Default);
        }

        public struct Releaser : IDisposable
        {
            private readonly AsyncLock m_toRelease;

            internal Releaser(AsyncLock toRelease) { m_toRelease = toRelease; }

            public void Dispose()
            {
                if (m_toRelease != null)
                    m_toRelease.m_semaphore.Release();
            }
        }
    }
}

The AsyncSemaPhore File

This file will be in your Repository or Services.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Sample.Mobile.Services.Async
{
    public class AsyncSemaphore
    {
        private readonly static Task s_completed = Task.FromResult(true);
        private readonly Queue<TaskCompletionSource<bool>> m_waiters = new Queue<TaskCompletionSource<bool>>();
        private int m_currentCount;

        public AsyncSemaphore(int initialCount)
        {
            if (initialCount < 0) throw new ArgumentOutOfRangeException("initialCount");
            m_currentCount = initialCount;
        }

        /// <summary>
        /// To insert DB Lock
        /// </summary>
        /// <returns></returns>
        public Task WaitAsync()
        {
            lock (m_waiters)
            {
                if (m_currentCount > 0)
                {
                    --m_currentCount;
                    return s_completed;
                }
                else
                {
                    var waiter = new TaskCompletionSource<bool>();
                    m_waiters.Enqueue(waiter);
                    return waiter.Task;
                }
            }
        }

        /// <summary>
        /// To Release DB Lock
        /// </summary>
        public void Release()
        {
            TaskCompletionSource<bool> toRelease = null;
            lock (m_waiters)
            {
                if (m_waiters.Count > 0)
                    toRelease = m_waiters.Dequeue();
                else
                    ++m_currentCount;
            }
            if (toRelease != null)
                toRelease.SetResult(true);
        }
    }
}

Finally, to call this functionality, we will create an object of Base operation file and call InsertAsync or InsertAllAsync function.