Database locked with databaseFactoryFfi after hot restart

[kuhnroyal]

I am using databaseFactoryFfi to create the store.

final factory = getDatabaseFactorySqflite(databaseFactoryFfi);

When the app is processing data and using the Sembast store and I perform a hot reload it happens quite often, that afterwards the database is locked and the app needs to be killed off in order to work again. I have also gotten this error twice in the production app which I don't understand at all.

SqfliteFfiException(sqlite_error: 5, , SqliteException(5): database is locked} DatabaseException(SqliteException(5): database is locked) sql 'BEGIN IMMEDIATE' {details: {database: {path: /data/user/0/com.example/app_flutter/sembast.sqlite, id: 1, readOnly: false, singleInstance: true}, sql: BEGIN IMMEDIATE}}

All the work and Sembast access is done in the main isolate. This happens on iOS and Android. Afterwards I get endless exceptions from everywhere in the app where I access Sembast.

NoSuchMethodError: The method 'query' was called on null.
Receiver: null
Tried calling: query()

From JdbDatabaseSqflite:

  Future<int> _getInfoInt(sqflite.DatabaseExecutor executor, String id) async {
    var map = (await executor.query(_infoStore,
            columns: [_valuePath], where: '$_idPath = ?', whereArgs: [id]))
        .firstWhere((_) => true, orElse: () => null);
    if (map != null) {
      return _decodeValue(map[_valuePath] as String) as int;
    }
    return null;
  }

Do you have any idea on how to get to the bottom of this?
Maybe the SqfliteIsolate still exists or something?

[alextekartik]

Thanks for the report. On iOS and Android I recommend using sqflite instead of sqflite_ffi. This is indeed an issue that could happen during hot reload/restart if you were in a transaction. sqflite should handle this properly (or at least I'll be glad to know).

import 'dart:io';

import 'package:sqflite/sqflite.dart' as sqflite;
import 'package:sqflite_common_ffi/sqflite_ffi.dart' as sqflite_ffi;
import 'package:sembast_sqflite/sembast_sqflite.dart' as sembast;

/// Get sqflite ffi on Windows and Linux, sqflite otherwise
sqflite.DatabaseFactory getSqfliteDatabaseFactory() {
  if (Platform.isLinux || Platform.isWindows) {
    return sqflite_ffi.databaseFactoryFfi;
  } else {
    return sqflite.databaseFactory;
  }
}

void main() {
  // io sembast factory for flutter using sqflite ffi on windows and linux, sqflite otherwise
  var factory =
      sembast.getDatabaseFactorySqflite(getSqfliteDatabaseFactory());

  // ...
}

[kuhnroyal]

Thanks, i'll try it with the sqflite databaseFactory and see if that solves things.

[simolus3]

This is indeed an issue that could happen during hot reload/restart if you were in a transaction. sqflite should handle this properly

@alextekartik If sqflite has special handling for this, can you point me towards it? Is this related to the singleInstance mechanism? If this is an issue that the sqlite3 package could fix too, I can try to add something similar there.

[alextekartik]

@simolus3 The issue happens if you restart while a transaction is in progress. Unfortunately you loose the native database handler. It is unfortunately only available in memory. sqflite stores a map on the native side and return the handler when the user open the same database on hot-restart/reload.

The only solution I could think off is to have a native component that allow storing/restoring global variable. After opening a database you could store you handler there (if that is possible) and restore it on reload (the dart side is killed, loosing the handler but not the native side since the shared lib is still loaded).

Sorry I'm not clear as I write this in a rush. Will spend more time replying later...

[alextekartik]

@simolus3 Here is the native trick
https://github.com/tekartik/sqflite/blob/367fa6d391938d5ac4b4abb148efa6158c7729c1/sqflite/android/src/main/java/com/tekartik/sqflite/SqflitePlugin.java#L722-L746

On the dart side, I call 'ROLLBACK' in this case to revert any pending transaction

[kuhnroyal]

After the change hot-restart now consistently fails on the first hot-restart but works on the second:

DatabaseException(Error Domain=FMDatabase Code=6922 "disk I/O error" UserInfo={NSLocalizedDescription=disk I/O error}) sql 'BEGIN EXCLUSIVE' args []}

[kuhnroyal]

Ok... the last problem was my own stupidity.

[alextekartik]

@kuhnroyal Thanks (I could not reproduce) so you confirm switching to sqflite fixes your original issue?

[kuhnroyal]

Yes it does for hot-restarts.
But this also occurred in production and I have not released the changes yet. There however it only occurs on Android, never on iOS.

I suspect it having to do with the activity being killed when the app goes into background, maybe somehow the FlutterEngine stays around, not completely sure on this.

@simolus3 had some ideas on how to clean up transactions in the future in the sqlite3 library. See simolus3/drift#835

[alextekartik]

Excellent, I was able to adapt @simolus3 very smart solution in sqflite_common_ffi (bumped to 1.1.1) which should now fix the issue for sembast_sqflite when using ffi.