drift
drift copied to clipboard
simolus3
SqliteException(5): database is locked
I am currently working on a project that use both drift and sqlite3 to store data locally in a database. In production and only in Android phones throws this error: SqliteException(5): database is locked.
The complete stacktrace is as follow:
Nw: SqliteException(5): database is locked
Causing statement: DELETE FROM customer_table;
libapp 0xdfe0d312 DriftCommunication.request (communication.dart:109)
libapp 0xe01b83e8 _BaseExecutor._runRequest (client_impl.dart:75)
libapp 0xe01be328 _BaseExecutor.runDelete (client_impl.dart:92)
libapp 0xe01be380 LazyDatabase.runDelete (lazy_database.dart:74)
libapp 0xe0312890 DeleteStatement.go.<T> (delete.dart:41)
libapp 0xdfe26df4 DeleteStatement.go.<T> (delete.dart:40)
libapp 0xdfdf43b0 DatabaseConnectionUser.doWhenOpened.<T> (connection_user.dart:160)
libapp 0xdfe0f114 Stream.firstWhere.<T>.<T> (stream.dart:1659)
libapp 0xe042aaa0 StackZoneSpecification._run (stack_zone_specification.dart:208)
libapp 0xe02e2e50 StackZoneSpecification._registerUnaryCallback.<T> (stack_zone_specification.dart:126)
libapp 0xe02e7ab4 _rootRunUnary (zone.dart:1434)
libapp 0xdfdad280 _rootRunUnary (zone.dart)
libapp 0xdfdad644 _CustomZone.runUnary (zone.dart:1335)
libapp 0xdfdad644 _FutureListener.handleValue (future_impl.dart:147)
libapp 0xe02e7d34 Future._propagateToListeners.handleValueCallback (future_impl.dart:766)
libapp 0xdfda9014 Future._propagateToListeners (future_impl.dart:795)
libapp 0xdfda8b08 Future._completeWithValue (future_impl.dart:566)
libapp 0xdfdadb84 Future._asyncCompleteWithValue.<T> (future_impl.dart:639)
libapp 0xe042aaa0 StackZoneSpecification._run (stack_zone_specification.dart:208)
libapp 0xe02e31f4 StackZoneSpecification._registerCallback.<T> (stack_zone_specification.dart:116)
libapp 0xe02e82c0 _rootRun (zone.dart:1426)
libapp 0xdfdad800 _rootRun (zone.dart)
libapp 0xe0260ae4 _CustomZone.run (zone.dart:1328)
libapp 0xe0408098 _CustomZone.runGuarded (zone.dart:1236)
libapp 0xe0264e58 _CustomZone.bindCallbackGuarded.<T> (zone.dart:1276)
libapp 0xdfda87a8 _microtaskLoop (schedule_microtask.dart:40)
libapp 0xe02e4fb8 _startMicrotaskLoop (schedule_microtask.dart:49)
libapp 0xdfda8730 _startMicrotaskLoop (schedule_microtask.dart)
File "
The version of drift that I am using is: 1.7.1 The version of sqlite3 is: 1.7.2 The version of sqlite3_flutter_libs is 0.5.7 Flutter version: 3.0.3
If anyone can give me al little light about this and how to solve this problem I would be grateful.
It looks like you're using a DriftIsolate, are you opening additional connections to the same sqlite3 file? If you have multiple connections running concurrently (without a DriftIsolate coordinating all of them), there's a chance to run into this issue. If you're just opening the database once this shouldn't happen.
Does this error happen a lot or just occasionally?
We use DriftIsolate and open only one connection to sqlite3. This error occurs a lot in production and we can't reproduce it consistently in dev environment.
Hi, in order to add more information about this problem, our app is made in Flutter, and we published it both in iOS and Android. In iOS we are not getting this SqliteException(5): database is locked, this only happens on Android.
Unfortunately, I still don't have enough information to properly act on this issue, as I've never been able to reproduce it locally or in any of my apps.
Are you able to reproduce this locally or do you see this error on crash reports only @nicobritos? I still think the most likely cause is that you somehow open two NativeDatabases to the same database. Could you check whether that might be happening?
Hi @simolus3
Nope. We cant reproduce it locally either, but there doesnt seem to be a particular reason for why this happens. Key points:
- The issue happens when performing a write query to the DB right after opening the app. Sentry doesn't show other lifecycle events so we assume the app was killed in background or manually closed before opening and seeing the issue.
- iOS is not affected, in Android this seems to happen in any version (6.0.1, 10, 11, 12).
- We use Drift in Isolate mode, and instantiate it using GetIt. We never reset the instance. We haven't tested creating an instance outside GetIt on a global variable, but this shouldnt be the problem.
- We dont know if a failed migration or if the app was killed when writing to the DB, performing a migration or a transaction locks the DB.
- We only use 1 connection to the DB.
var dbDisposeCompleter = Completer<void>();
getIt.registerLazySingleton<QueryExecutor>(
() => AppDb.sqliteConnection(
arguments.logger.sqlite,
arguments.logger.sqliteIsolate,
asIsolate: dbIsolate,
onIsolateDispose: dbDisposeCompleter.future,
),
dispose: (sql) async {
await sql.close();
if (!dbDisposeCompleter.isCompleted) {
dbDisposeCompleter.complete();
dbDisposeCompleter = Completer();
}
},
);
static QueryExecutor sqliteConnection(bool logStatements, bool logIsolate,
{bool asIsolate = false, Future? onIsolateDispose}) {
if (asIsolate) {
return _sqliteConnectionIsolate(logStatements, logIsolate, onIsolateDispose);
} else {
return _sqliteConnectionDirect(logStatements);
}
}
static Future<File> sqliteFile() async {
// Put the database file in documents folder.
final dbFolder = await getApplicationDocumentsDirectory();
return File(join(dbFolder.path, 'db.sqlite'));
}
static QueryExecutor _sqliteConnectionIsolate(bool logStatements, bool logIsolate, Future? onIsolateDispose) {
return DatabaseConnection.delayed(() async {
final isolate = await _createDriftIsolate(logStatements);
unawaited(onIsolateDispose?.then((_) => isolate.shutdownAll()));
return isolate.connect(isolateDebugLog: logIsolate);
}())
.executor;
}
static LazyDatabase _sqliteConnectionDirect(bool logStatements) {
return LazyDatabase(() async {
return NativeDatabase(
await sqliteFile(),
logStatements: logStatements,
setup: (rawDb) {
rawDb.execute('PRAGMA journal_mode=WAL;');
},
);
});
}
static Future<DriftIsolate> _createDriftIsolate(bool logStatements) async {
final file = await sqliteFile();
final receivePort = ReceivePort();
await Isolate.spawn(
_startBackgroundIsolate,
_DriftIsolateInitial(receivePort.sendPort, file, logStatements),
);
return await receivePort.first;
}
static void _startBackgroundIsolate(_DriftIsolateInitial request) {
final executor = NativeDatabase(
request.targetFile,
logStatements: request.logStatements,
setup: (rawDb) {
rawDb.execute('PRAGMA journal_mode=WAL;');
},
);
final driftIsolate = DriftIsolate.inCurrent(() => DatabaseConnection(executor));
request.sendDriftIsolate.send(driftIsolate);
}
class _DriftIsolateInitial {
final SendPort sendDriftIsolate;
final File targetFile;
final bool logStatements;
_DriftIsolateInitial(this.sendDriftIsolate, this.targetFile, this.logStatements);
}
Hope that you see something that we are missing! Thanks
Nice call with the completer to stop the drift isolate after closing the database.
Unfortunately, I still don't see any obvious problem. From everything I know, sqlite3 error 5 can only mean that two concurrent processes are using the database. This error should not occur when the database is locked due to misuse in a single connection, or even due to a previous crash or an unexpected process stop.
One thing that stands out: On iOS, we compile sqlite with SQLITE_THREADSAFE=1 (always threadsafe). On Android, we use SQLITE_THREADSAFE=2 (threadsafe as long as two threads don't share a database connection). Technically, two threads should never share a database connection. But I wonder if the isolate group feature, where Dart isolates are multiplexed onto fewer OS threads and might switch across threads, could cause an issue with that option too. As a precaution, I'll release a new version of sqlite3_flutter_libs that also uses SQLITE_THREADSAFE=1 on Android. Then we can check if that changes anything. I think it's worth it just to keep the compile options between Android and iOS in sync.
I've just released sqlite3_flutter_libs version 0.5.10, which switches to SQLITE_THREADSAFE=1 on Android as well. If you could use that version and see if it improves the situation at all, that would help a lot!
Might also be worth checking if the issue still happens when using a non-WAL journal mode. Note you'll need to explicitly set the journal mode to some non-WAL value (like DELETE), since WAL mode is persistent.
The biggest benefit of WAL mode is that you can have multiple reader connections that don't block each other or the writer connection, so if you're only using one connection you're not really taking full advantage of WAL anyway.
First of all, thanks for your answers! We've already migrated to ObjectBox as this was blocking the app's deploy to production on Android, and we were already passed our deadline.
@simolus3 we've tested the new build but as we weren't able to reproduce the issue earlier, we cannot say if it was fixed or not, and a deploy to production of the new build risking breaking the experience to some customers doesn't make sense. However, it begs the question of why the Android's SQLite3 lib was built using SQLITE_THREADSAFE=2 and SQLITE_THREADSAFE=1 in iOS.
@Mike278 you mean removing the line rawDb.execute('PRAGMA journal_mode=WAL;');? Because this was something we've tried to see if the database wouldn't lock, although we knew this was only useful when using multiple (read) connections, but we've wanted to try it anyways. Anyways, it didn't work.
What would be ideal is to find a way to reproduce this issue. However, I'm inclined to think that the lock happens when dart switches threads in the same isolate. Some of this is explained here.
Thanks for your response!
risking breaking the experience to some customers doesn't make sense
Yeah that's definitely understandable.
However, it begs the question of why the Android's SQLite3 lib was built using
SQLITE_THREADSAFE=2andSQLITE_THREADSAFE=1in iOS.
When I started work on the ffi implementation, I was convinced that SQLITE_THREADSAFE=2 was the right option to use. It most likely was at the time, since isolates didn't switch between multiple OS threads back then. Using SQLITE_THREADSAFE=2 brings a very tiny speed gain since some mutexes can be skipped.
On Android, I had to compile sqlite3 myself, and so I've picked SQLITE_THREADSAFE=2 as a compile-time option. On iOS, we're using an existing pod to compile sqlite3. That pod keeps the default value of SQLITE_THREADSAFE=1 and I didn't see a reason to change that.
That was causing an unfortunate divergence which I've fixed now. We now use the default SQLITE_THREADSAFE=1 on all platforms.
I'm inclined to think that the lock happens when dart switches threads in the same isolate.
Yes, that is my best guess as well. This would explain why this has not been a problem initially, as isolates were bound to a fixed OS thread before Dart 2.15.
We have exactly the same problem in production
- Android only
- journal_mode=WAL
- drift: 1.7.1
- concurrent writing
It's hard to have a reproduction scenario in dev environment.
I try to upgrade to sqlite3_flutter_libs 0.5.10 but I still get the error. To reproduce, I force insert/update requests on separate threads (some in foreground, and on the other in background with the workManager). All threads use the same DatabaseConnection (singleton), but the error always occurs.
I/flutter (31172): ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
I/flutter (31172): │ SqliteException(5): database is locked, database is locked (code 5)
I/flutter (31172): ├┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄
I/flutter (31172): │ #0 DriftCommunication.request (package:drift/src/remote/communication.dart:109:66)
I/flutter (31172): │ #1 _BaseExecutor._runRequest (package:drift/src/remote/client_impl.dart:75:28)
I/flutter (31172): │ #2 _BaseExecutor.runInsert (package:drift/src/remote/client_impl.dart:102:12)
I/flutter (31172): │ #3 LazyDatabase.runInsert (package:drift/src/utils/lazy_database.dart:78:17)
I/flutter (31172): │ #4 InsertStatement.insert.<anonymous closure> (package:drift/src/runtime/query_builder/statements/insert.dart:74:26)
I/flutter (31172): │ #5 InsertStatement.insert.<anonymous closure> (package:drift/src/runtime/query_builder/statements/insert.dart:73:40)
I/flutter (31172): │ #6 DatabaseConnectionUser.doWhenOpened.<anonymous closure> (package:drift/src/runtime/api/connection_user.dart:160:64)
I/flutter (31172): │ #7 _rootRunUnary (dart:async/zone.dart:1434:47)
I/flutter (31172): ├┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄┄
@simolus3 I also have the same issue, either the database gets locked or we don't get any values in db
