Issue #60 closed

Async DB operations => SQLiteException (busy / database locked)

Michael Brodacz-Geier
created an issue

When using async insert, update or read operations I often get SQLiteExceptions with message "Busy" or "database is locked".

Operations running asyncly are accessing the database and doing some other work in the background. If the user then starts another operation which also accesses the database, the database locking issues start to appear.

Shouldn't the SQLite Extensions handle such potential concurrency problems internally? A fix which seems to work for me now is to force the sequential execution of the database operations by using a semaphore, for example:

await semaphore.WaitAsync();
try
{
    return await DBAsyncConnection().GetAllWithChildrenAsync<T>(predicate, recursive: true);
}
finally
{
    semaphore.Release();
}

So to avoid those SQLiteExceptions I have to wrap all code where I access the database in such a try ... finally block + semaphore.

Is this the way to go, or maybe should this behavior actually really be implemented inside the SQLite Extensions? Or am I just using it the wrong way?

Comments (11)

  1. Guille Guti

    I think that that code creates a SQLiteConnectionWithLock every time. Can you try this code and try to reproduce it again?

    private SQLiteAsyncConnection DBAsyncConnection()
    {
        if (sqliteAsyncConnection == null)
        {
                var connectionString = new SQLiteConnectionString(DBPath, false);
                var connectionWithLock = new SQLiteConnectionWithLock(GetSQLitePlatform(), connectionString);
                sqliteAsyncConnection = new SQLiteAsyncConnection(() => connectionWithLock);
        }
        return sqliteAsyncConnection;
    }
    
  2. Guille Guti

    I don't think so. The same SQLiteConnectionWithLock must be used, otherwise more than one thread could access the database file at the same time (because the lock is per connection) and you could run into 'Busy' database errors.

  3. Khoa Nguyen

    I have the same issue. Below is my code to create connection:

            private static readonly Lazy<SQLiteAsyncConnection> _connection = new Lazy<SQLiteAsyncConnection>(() =>
            {
                var databasePath = GetDatabasePath();
                var connectionString = new SQLiteConnectionString(databasePath, storeDateTimeAsTicks: false);
                var connection = new SQLiteAsyncConnection(() => new SQLiteConnectionWithLock(new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS(), connectionString));
                return connection;
            });
    
            SQLiteAsyncConnection ISQLite.GetAsyncConnection()
            {
                return _connection.Value;
            }
    
  4. Guille Guti

    Khoa Nguyen let me explain it again...

    Change this line:

    var connection = new SQLiteAsyncConnection(() => new SQLiteConnectionWithLock(new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS(), connectionString));
    

    That creates a new SQLiteConnectionWithLock each time, to this:

    var connectionWithLock =  new SQLiteConnectionWithLock(new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS(), connectionString);
    var connection = new SQLiteAsyncConnection(() => connectionWithLock);
    

    That reuse the same SQLiteConnectionWithLock.

  5. Log in to comment