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

[Michael Brodacz-Geier (Parkside)]

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?

[Guillermo Gutiérrez]

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

[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;
}

[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.

[Guillermo Gutiérrez]

I'm closing this issue. Please feel free to reopen it if you run against this issue again.

[Alfian Ramadhan]

I'm using the same SQLiteConnectionWithLock, and the error is still there

[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.

[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;
        }

[Guillermo Gutiérrez]

@khoacoi Your connection creates a new SQLiteConnectionWithLock every time.

[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.

Thanks,

[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.

[Khoa Nguyen]

Thanks @redent , got your point.

Appreciate your help!

Thanks again.