Forum Xamarin.Forms
We are excited to announce that the Xamarin Forums are moving to the new Microsoft Q&A experience. Q&A is the home for technical questions and answers at across all products at Microsoft now including Xamarin!

We encourage you to head over to Microsoft Q&A for .NET for posting new questions and get involved today.

DB Locked in Android SQLite Xamarin Forms

Esaavedra89Esaavedra89 USMember ✭✭
edited June 2019 in Xamarin.Forms

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

        #region Constructors
        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");
            }
        }

        ...
        #endregion
}
}

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.

Best Answers

  • Esaavedra89Esaavedra89 USMember ✭✭
    Accepted Answer

    To ending this post I did finish using an global property bool to know when the app is sync up with the API and create an await before every operation with the local BD in another process, when the global property es true.

  • Esaavedra89Esaavedra89 USMember ✭✭
    Accepted Answer

    @AnaRondon said:
    Did this solution work for you? I am going through the same problem.

    Thank you!!

    @Esaavedra89 said:
    I was searching for a while and I did finish thinking that this maybe is the best approach to work with local database and concurrency (without use BeginTransaction, Commit, Close and Dispose).

    Using "SQLiteOpenFlags.FullMutex, true" in Android project (It seens with this we avoid the SIGSEGV crashes).

      public SQLite.SQLiteConnection GetConn()
            {
                var sqliteFileName = "LocalDB.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.FullMutex, true);
    
                return conn;
            }
    

    Create only one connection with the DB and never close it (Shared project).

    public static SQLiteConnection conn;
    
            public static SQLiteConnection GetConnection()
            {
                if (conn == null)
                {
                    conn = DependencyService.Get<AccesoDatosI>().GetConn();
                }
    
                return conn;
            }
    

    This way, we are going to reuse the connection and SQLite handle everything.

    And as I said above, never close the connection.

    If I'm wrong with something or someone want to add something, I'm going to glad to reed it.

    Hi Ana, yes this solution works for me...

    If you search on the Sqlite Documentation, they recomend open the connection once and never close it.

Answers

  • HarshitaHarshita INMember ✭✭✭✭

    Do not create multple connection on your SQLite.Create a single connection of SQLIte and then use it.Save the connection to a global static class then use it every where.

  • Esaavedra89Esaavedra89 USMember ✭✭

    @Harshita said:
    Do not create multple connection on your SQLite.Create a single connection of SQLIte and then use it.Save the connection to a global static class then use it every where.

    Hello @Harshita , thanks for your anwers.

    Actually I'm a little bit confused of how I can create only one connection with my DB by a global static class.

    Can you show me a little example of how can I achieve that?

    Thanks.

  • HarshitaHarshita INMember ✭✭✭✭

    @Esaavedra89
    create a static global class

    public static class GlobalClass
        {
            public static SQLiteConnection conn;
            static GlobalClass()
            {
                if (conn == null)
                {
                    conn = DependencyService.Get<ISQLite>().GetConnection();
                }
            }
        }
    

    from where you are calling the function first time for getting the connection(Like App.Xaml)

    GlobalClass.conn = DependencyService.Get<ISQLite>().GetConnection();

    thats it.

  • Esaavedra89Esaavedra89 USMember ✭✭

    @Harshita said:
    @Esaavedra89
    create a static global class

    public static class GlobalClass
        {
            public static SQLiteConnection conn;
            static GlobalClass()
            {
                if (conn == null)
                {
                    conn = DependencyService.Get<ISQLite>().GetConnection();
                }
            }
        }
    

    from where you are calling the function first time for getting the connection(Like App.Xaml)

    GlobalClass.conn = DependencyService.Get<ISQLite>().GetConnection();

    thats it.

    Very well, let me implement the solution on my code and I will tell you how it was to me.

  • Esaavedra89Esaavedra89 USMember ✭✭

    @Harshita said:
    @Esaavedra89
    create a static global class

    public static class GlobalClass
        {
            public static SQLiteConnection conn;
            static GlobalClass()
            {
                if (conn == null)
                {
                    conn = DependencyService.Get<ISQLite>().GetConnection();
                }
            }
        }
    

    from where you are calling the function first time for getting the connection(Like App.Xaml)

    GlobalClass.conn = DependencyService.Get<ISQLite>().GetConnection();

    thats it.

    To finish the post,

    this options is really useful but I did not use, because I used the Dispose() in all operations with the local DB.

    thanks for your reply.

  • Esaavedra89Esaavedra89 USMember ✭✭
    Accepted Answer

    To ending this post I did finish using an global property bool to know when the app is sync up with the API and create an await before every operation with the local BD in another process, when the global property es true.

  • Esaavedra89Esaavedra89 USMember ✭✭
    edited October 2019

    I was searching for a while and I did finish thinking that this maybe is the best approach to work with local database and concurrency (without use BeginTransaction, Commit, Close and Dispose).

    Using "SQLiteOpenFlags.FullMutex, true" in Android project (It seens with this we avoid the SIGSEGV crashes).

      public SQLite.SQLiteConnection GetConn()
            {
                var sqliteFileName = "LocalDB.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.FullMutex, true);
    
                return conn;
            }
    

    Create only one connection with the DB and never close it (Shared project).

    public static SQLiteConnection conn;
    
            public static SQLiteConnection GetConnection()
            {
                if (conn == null)
                {
                    conn = DependencyService.Get<AccesoDatosI>().GetConn();
                }
    
                return conn;
            }
    

    This way, we are going to reuse the connection and SQLite handle everything.

    And as I said above, never close the connection.

    If I'm wrong with something or someone want to add something, I'm going to glad to reed it.

  • AnaRondonAnaRondon USMember

    Did this solution work for you? I am going through the same problem.

    Thank you!!

    @Esaavedra89 said:
    I was searching for a while and I did finish thinking that this maybe is the best approach to work with local database and concurrency (without use BeginTransaction, Commit, Close and Dispose).

    Using "SQLiteOpenFlags.FullMutex, true" in Android project (It seens with this we avoid the SIGSEGV crashes).

      public SQLite.SQLiteConnection GetConn()
            {
                var sqliteFileName = "LocalDB.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.FullMutex, true);
    
                return conn;
            }
    

    Create only one connection with the DB and never close it (Shared project).

    public static SQLiteConnection conn;
    
            public static SQLiteConnection GetConnection()
            {
                if (conn == null)
                {
                    conn = DependencyService.Get<AccesoDatosI>().GetConn();
                }
    
                return conn;
            }
    

    This way, we are going to reuse the connection and SQLite handle everything.

    And as I said above, never close the connection.

    If I'm wrong with something or someone want to add something, I'm going to glad to reed it.

  • Esaavedra89Esaavedra89 USMember ✭✭
    Accepted Answer

    @AnaRondon said:
    Did this solution work for you? I am going through the same problem.

    Thank you!!

    @Esaavedra89 said:
    I was searching for a while and I did finish thinking that this maybe is the best approach to work with local database and concurrency (without use BeginTransaction, Commit, Close and Dispose).

    Using "SQLiteOpenFlags.FullMutex, true" in Android project (It seens with this we avoid the SIGSEGV crashes).

      public SQLite.SQLiteConnection GetConn()
            {
                var sqliteFileName = "LocalDB.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.FullMutex, true);
    
                return conn;
            }
    

    Create only one connection with the DB and never close it (Shared project).

    public static SQLiteConnection conn;
    
            public static SQLiteConnection GetConnection()
            {
                if (conn == null)
                {
                    conn = DependencyService.Get<AccesoDatosI>().GetConn();
                }
    
                return conn;
            }
    

    This way, we are going to reuse the connection and SQLite handle everything.

    And as I said above, never close the connection.

    If I'm wrong with something or someone want to add something, I'm going to glad to reed it.

    Hi Ana, yes this solution works for me...

    If you search on the Sqlite Documentation, they recomend open the connection once and never close it.

Sign In or Register to comment.