In our Android and iOS MVVMCross app we are experiencing occasional SQLiteException: busy exceptions.
Given the code below, we have several repositories each of which construct a instance of the below and an associated connection to the Sqlite database. Imagine we have a Stocks Repository and a Valuations Repository, two instances of SqliteDataService will be created: SqliteDataService with type Stocks and SqliteDataService with types Valuations, each of which have a connection to the Sqlite database.
Actions on the repositories may operate on background threads which means that we may attempt to insert Stocks into the database at the same time as Valuations.
Now given each repository creates its own SqliteDataService the connectionObject lock will only protect the same repository types from accessing the database at the same time rather than protecting Stocks and Valuations from accessing the database at the same time.
My questions are:
Is it valid to create a connections per repository and if so, how do we guard against SqliteException: busy?
Is there a better pattern? i.e. Should we create a non-generic SqliteDataService class that shares the same connection across threads? We have tried this but on Android we experience fatal exceptions.
Does anybody have a solid Sqlite DAL pattern for Xamarin MVVMCross?
public class SqliteDataService<T> : IDataService<T> where T : new()
{
private static object lockObject = new object();
private static object connectionObject = new object();
private static ISQLiteConnection _connection;
private static SqliteDataService<T> _instance;
public SqliteDataService(ISQLiteConnectionFactory connectionFactory, string dbPath)
{
if (_connection == null)
{
_connection = connectionFactory.Create (dbPath);
_connection.CreateTable<T> ();
}
}
public static SqliteDataService<T> GetInstance(ISQLiteConnectionFactory connectionFactory, string dbPath)
{
if (_instance == null)
{
lock (lockObject)
{
_instance = new SqliteDataService<T> (connectionFactory, dbPath);
}
}
return _instance;
}
public void CreateTable<T> ()
{
}
public void Insert(T value)
{
lock (connectionObject) {
_connection.Insert (value, typeof(T));
}
}
public void InsertAll(IEnumerable<T> values)
{
lock (connectionObject) {
_connection.Insert (values, typeof(T));
}
}
public IEnumerable<T> Read(Expression<Func<T, bool>> predicate)
{
lock (connectionObject) {
return _connection.Table<T> ().Where (predicate);
}
}
public T ReadFirst(Expression<Func<T, bool>> predicate)
{
lock (connectionObject) {
return Read (predicate).FirstOrDefault ();
}
}
public void Update(T value)
{
lock (connectionObject) {
_connection.Update (value, typeof(T));
}
}
public void Delete(Expression<Func<T, bool>> predicate)
{
lock (connectionObject) {
var valuesToDelete = Read (predicate);
if (valuesToDelete == null)
return;
foreach (var value in valuesToDelete) {
_connection.Delete (value);
}
}