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

Issue #60 closed
Michael 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();
    return await DBAsyncConnection().GetAllWithChildrenAsync<T>(predicate, recursive: true);

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. Guillermo Gutiérrez

    Are you using the same database connection or are you creating a new connection every time?

  2. Guillermo Gutiérrez

    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;
  3. Guillermo Gutiérrez

    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.

  4. Guillermo Gutiérrez

    @alfian try reusing the same connection as the code above. If that doesn't work, please report the issue in SQLite.Net project.

  5. 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;
  6. Khoa Nguyen

    Thanks for your response!

    @redent I'm using the Lazy to initial the connection. Therefore, It should have only one connection, it is the singleton pattern.


  7. Guillermo Gutiérrez

    @khoacoi 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.

  8. Log in to comment