Error DB Locked in Android SQLite Xamarin Forms

Question

I'm working on a App that have more than 50 tables and can work without internet connection, so in background the app can sync up with the API and get all the information and make the CRUD operation in local.

Sometimes when the app is sync up with the API, I'm getting the error "Database is LOCKED", when I'm making another operation on the App.

So I need help to solve this, I know that there are a lot of post about this problem, but base on my implamentation with my database, it seems not to be enough to solve my problem.

Nugets: Xamarin.Forms 3.0.0 482510 sqlite-net-pcl 1.5.166-beta

I use a class DataService.cs where that class connect with the DataContext.cs and make the connection with database and methods CRUD. All methods in the DataService have the same way to connect with DataContext:

    //This is a resume of DataService.cs
        public class DataService 
            {
                public T Insert<T>(T model)
                {
                    try
                    {
                        using (var da = new DataContext())
                        {
                            da.Insert(model);
                            return model;
                        }
                    }
                    catch (Exception error)
                    {
                        error.ToString();
                        return model;
                    }
                }
        }

In DataContext.cs we have the connection with the local database and all the methods with the local database . All methods have the collisionLock (to avoid conflict with database) and cnn.Dispose() (To close connection with the database and avoid the error Fatal signal 11 (SIGSEGV));

DataContext.cs

    public interface IBusinessEntity
        {
            int ID { get; set; }
        }


         //This is a resume of DataContext.cs
            public class DataContext : IDisposable
            {
                #region Attributes
                public SQLiteConnection cnn;
                private static object collisionLock = new object();
                #endregion


                public DataContext()
                {
                    cnn = DependencyService.Get<IConfiguracion>().GetConnection();
        ...
        } 
                #endregion

                #region MetodosGenericosZulu
                public void Insert<T>(T model)
                {
                    try
                    {
                        // Use locks to avoid database collisions
                        lock (collisionLock)
                        {
                            cnn.Insert(model);
                            cnn.Dispose();
                        }
                    }
                    catch (Exception error)
                    {
                        Application.Current.MainPage.DisplayAlert(
                            "Error",
                            "Un error a ocurrido con la DB (Insert): " + error.Message.ToString(),
                            "Ok");
                    }
                }

                public void Update<T>(T model)
                {
                    try
                    {
                        lock (collisionLock)
                        {
                            cnn.Update(model);
                            cnn.Dispose();
                        }
                    }
                    catch (Exception error)
                    {
                        Application.Current.MainPage.DisplayAlert(
                                            "Error",
                                            "Un error a ocurrido con la DB (Actualizar): " + error.Message.ToString(),
                                            "Ok");
                    }
                }

                ...
        }
        }

Implentation on Android project.

    public class Configuracion : IConfiguracion
        {
            public Configuracion(){ }

            public SQLite.SQLiteConnection GetConnection()
            {
                    var sqliteFileName = "FN_Desarrollo.db3";
                    string documentsPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                    var path = Path.Combine(documentsPath, sqliteFileName);
                  var  conn = new SQLite.SQLiteConnection(path, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create | SQLiteOpenFlags.SharedCache);

                return conn;
            }
        }

So I need your help guys to solve the problem (database locked) and review if my implementation with the SQLite is OK.

I'm hearing all the suggestions.

Thanks in advance.

Answer 1

I finished using one connection for all operations with the local DB.

On the Sqlite Documentation, they recommend open the connection once and never close it.

public class DataContext
    {
        public static SQLiteConnection cnn;

        public DataContext()
        {
            if (cnn == null)
            {
               Create your tables.
               cnn.CreateTable<...>();
               ...
            }
        }
   }