SQLite is busy or locked

Hello all,
I am using sqlite to develop apps using Xamarin.Forms

I had a problem. Some times i got an exception "Busy" or "DataBase is locked"

Here is my code:

`
public class Repository where T : class
{
SQLiteAsyncConnection database;

    public Repository()
    {
        database = DependencyService.Get<ISqlite>().GetConnection();
    }

    /// <summary>
    /// Get an entity by primary key
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public async Task<T> FindByKey(int id)
    {
        return await database.FindAsync<T>(id);
    }

/// <summary>
/// Finds the by key.
/// </summary>
/// <returns>The by key.</returns>
/// <param name="id">Identifier.</param>
public async Task<T> FindByKey(string id)
{
    return await database.FindAsync<T>(id);
}

    /// <summary>
    /// Select data
    /// </summary>
    /// <typeparam name="Tvalue"></typeparam>
    /// <param name="where">Expression where statment </param>
    /// <param name="orderBy">Expression order by statment</param>
    /// <returns></returns>
    public async Task<List<T>> GetItem<Tvalue>(Expression<Func<T, bool>> where = null, Expression<Func<T, Tvalue>> orderBy = null)
    {
        var query = database.Table<T>();

        if(where != null)
        {
            query = query.Where(where);
        }

        if(orderBy != null)
        {
            query = query.OrderBy<Tvalue>(orderBy);
        }

        return await query.ToListAsync();
    }

    /// <summary>
    /// Insert an entity
    /// </summary>
    /// <param name="entity"></param>
    /// <returns></returns>
    public async Task<int> Insert(T entity)
    {
        return await database.InsertAsync(entity);
    }

    /// <summary>
    /// Update an entity
    /// </summary>
    /// <param name="entity"></param>
    /// <returns></returns>
    public async Task<int> Update(T entity)
    {
        return await database.UpdateAsync(entity);
    }

    /// <summary>
    /// Delete an entity
    /// </summary>
    /// <param name="id">Primary key</param>
    /// <returns></returns>
    public async Task<int> Delete(object id)
    {
        var entity = database.FindAsync<T>(id);

        if(entity != null)
        {
            return await database.ExecuteAsync ("delete from " + typeof(T).Name + " where id = " + id);
        }

        return -1;
    }
}

`

Anyone can help me? I will appreciate it.

Answers

  • LanLeHoangLanLeHoang USMember
    edited March 2016

    at SQLite.Net.PreparedSqlLiteInsertCommand.ExecuteNonQuery (System.Object[] source) [0x0016b] in <filename unknown>:0 at SQLite.Net.SQLiteConnection.Insert (System.Object obj, System.String extra, System.Type objType) [0x000bc] in <filename unknown>:0 --- End of stack trace from previous location where exception was thrown --- at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw () [0x0000c] in /Users/builder/data/lanes/2689/962a0506/source/maccore/_build/Library/Frameworks/Xamarin.iOS.framework/Versions/git/src/mono/external/referencesource/mscorlib/system/runtime/exceptionservices/exceptionservicescommon.cs:143 at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess (System.Threading.Tasks.Task task) [0x00047] in /Users/builder/data/lanes/2689/962a0506/source/maccore/_build/Library/Frameworks/Xamarin.iOS.framework/Versions/git/src/mono/external/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:201 at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification (System.Threading.Tasks.Task task) [0x0002e] in /Users/builder/data/lanes/2689/962a0506/source/maccore/_build/Library/Frameworks/Xamarin.iOS.framework/Versions/git/src/mono/external/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:170 at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd (System.Threading.Tasks.Task task) [0x0000b] in /Users/builder/data/lanes/2689/962a0506/source/maccore/_build/Library/Frameworks/Xamarin.iOS.framework/Versions/git/src/mono/external/referencesource/mscorlib/system/runtime/compilerservices/TaskAwaiter.cs:142 at System.Runtime.CompilerServices.TaskAwaiter1[TResult].GetResult () [0x00000] in
    ......

  • RobertBoskoRobertBosko USMember

    It's possible that your application using few asyncConnection at the same time. Solution for it is using lock.

  • LanLeHoangLanLeHoang USMember
    edited March 2016

    Thank @RobertBosko . I think so, I tried to use lock to lock the sqlite query, but i haven't yet found a "LOCK" that can lock asynchronous sqlite. :(

  • AshleyJacksonAshleyJackson GBMember ✭✭

    Could you use something like this https://github.com/StephenCleary/AsyncEx/wiki/AsyncLock for the lock?

  • NetworkappNetworkapp NLMember ✭✭
    edited March 2016

    How are you initializing your connection? We had the same issue and eventually solved it by doing:

    var plat = new SQLitePlatformIOS();
    var cwLock = new SQLiteConnectionWithLock (plat, new SQLiteConnectionString (path, false));
    var conn = new SQLiteAsyncConnection(() => cwLock);
    

    As according to: https://github.com/oysteinkrog/SQLite.Net-PCL/issues/254#issuecomment-159837369 the often described way, creates multiple connections.

  • hvaughanhvaughan USMember ✭✭✭

    I was aready using the initialization code @Networkapp provided and also using the Async lock that @AshleyJackson showed and just received this error. The weird thing is that it has worked great for months and months in production and dev. Just got this error for the first time, on a simulator.

  • PeterFarrerPeterFarrer USMember ✭✭

    @Networkapp said:
    How are you initializing your connection? We had the same issue and eventually solved it by doing:

    var plat = new SQLitePlatformIOS();
    var cwLock = new SQLiteConnectionWithLock (plat, new SQLiteConnectionString (path, false));
    var conn = new SQLiteAsyncConnection(() => cwLock);

    As according to: https://github.com/oysteinkrog/SQLite.Net-PCL/issues/254#issuecomment-159837369 the often described way, creates multiple connections.

    I had the same issue on UWP , solved with the UWP version of this.

  • RahmiTugrulAltinRahmiTugrulAltin USMember ✭✭

    Make it static and lock connection, each connection needs to open database and refresh connection.
    just use below it will help you.

        private static SQLiteConnection privatecon;
    
        public static  SQLiteConnection GetConnection()
        {
            if (privatecon==null)
            {
                string devicePath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal);
                var path = System.IO.Path.Combine(devicePath, "mydb.db3");
                privatecon = new SQLiteConnection(path);
            }
    
            return privatecon;
        }
    
Sign In or Register to comment.