Database is locked exception

[oligazar]

I'm getting "database is locked" exception. I don't use any transactions in my code though.

#0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
<asynchronous suspension>
#1      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:321:14)
<asynchronous suspension>
#2      SqfliteDatabaseMixin.beginTransaction (package:sqflite_common/src/database_mixin.dart:461:9)
<asynchronous suspension>
#3      SqfliteDatabaseMixin._runTransaction (package:sqflite_common/src/database_mixin.dart:484:13)
<asynchronous suspension>
#4      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:34:18)
<asynchronous suspension>
#5      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:346:14)
<asynchronous suspension>
#6      DatabaseHelper._insertMap (package:treckerflutter/logic/db/db_helper.dart:1109:5<…>
[VERBOSE-2:ui_dart_state.cc(186)] Unhandled Exception: DatabaseException(Error Domain=FMDatabase Code=5 "database is locked" UserInfo={NSLocalizedDescription=database is locked}) sql 'DELETE FROM table_position_body' args []}
#0      wrapDatabaseException (package:sqflite/src/exception_impl.dart:11:7)
<asynchronous suspension>
#1      SqfliteDatabaseMixin.txnRawUpdate.<anonymous closure> (package:sqflite_common/src/database_mixin.dart:420:22)
<asynchronous suspension>
#2      BasicLock.synchronized (package:synchronized/src/basic_lock.dart:34:18)
<asynchronous suspension>
#3      SqfliteDatabaseMixin.txnSynchronized (package:sqflite_common/src/database_mixin.dart:346:14)
<asynchronous suspension>
#4      FirebaseHelper.saveLocation (package:treckerflutter/logic/apis/fb_helper.dart:513:7)
<asynchronous suspension>
#5      FirebaseHelper.onLocation (package:treckerflutter/logic/apis/fb_helper.dart:476:9)
<asynchronous suspension>

flutter: Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction 
flutter: Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction
flutter: Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction

Methods mentioned in the error trace:

Future<int> deleteAllFrom(String table) async {
    Database db = await database;
    return db.rawDelete("DELETE FROM $table");
  }

Future<void> _insertMap(List<Map<String, dynamic>> items, String tableName,
      {bool shouldClearTable = true}) async {
    Database db = await database;
    final batch = db.batch();
    if (shouldClearTable) {
      print('delete from: $tableName, new items: ${items.length}');
      batch.rawDelete("DELETE FROM $tableName");
    }
    items.forEach((map) {
      map[COL_SAVE_TIME] = DateTime.now().millisecondsSinceEpoch;
      batch.insert(tableName, map,
          conflictAlgorithm: ConflictAlgorithm.replace);
    });
    await batch.commit();

    notify(tableName);
  }

One thing that may be important here is that 'deleteAllFrom' and '_insertMap' are called from different isolates. Also there wasn't huge insert/updates when the lock happened.

Is there any way to avoid the blocking or resolve the issue in some other way?

[alextekartik]

For the time being, sqflite is not cross isolate safe. I don't recommend using it from another isolate as it can bring issues like the one you encounter.

[oligazar]

@alextekartik Thanks for the quick reply! Do you happen to know any isolate-safe database implementation as an alternative? Or any other storage solution for that matter? May having two instances of the db in both isolates be a solution/workaround?

[alextekartik]

@oligazar I am not aware of any easy solution, sorry.

[Mike278]

@oligazar https://moor.simonbinder.eu/docs/advanced-features/isolates/

[oligazar]

@oligazar https://moor.simonbinder.eu/docs/advanced-features/isolates/

Thanks a lot! Looks very promising!

[Mike278]

Just out of curiosity, what are you using isolates for?

The typical reason is that running expensive cpu-bound work on the main UI isolate causes frame drops, so you move that work to a non-UI isolate. It takes longer to communicate over the isolate, so you trade off a bit of overall speed for a smoother UI.

This is pretty much the setup that sqflite already has: the expensive cpu-bound work (using the sqlite api to execute the statement and possibly step through the results) is done in a native thread and communicated over a method channel. This means the only way sqflite should cause frame drops is if you're receiving a very large amount of data over the method channel (where "very large" normally means "more data than should reasonably be displayed on one screen").

Moor, on the other hand, has the option to interact with the sqlite api directly using dart:ffi. In this case, since the expensive cpu-bound work is done directly in Dart, it makes sense to move that work to a separate isolate.

I haven't benchmarked yet, but I'd be curious to know:

• Is it faster to pass data over a method channel or between isolates?
• Does dart:ffi use the sqlite api faster than the iOS/Android implementations? Might beat JNI on Android, but it's probably hard to beat direct access on iOS