How to debug "database is locked" errors?

[mrclauss]

Unfortunately my issue was not resolved by asserting only a single isolate accesses the database (see discussion below).

Observed with drift 2.21.0..

Stacktrace/Environment:
SqliteException(5): while executing statement, database is locked, database is locked (code 5) Causing statement: DELETE FROM "change_log_record_entry" WHERE "timestamp" < ?;, parameters: 1731832708

Stack trace:

package:sqlite3/src/implementation/exception.dart 75 throwException
package:sqlite3/src/implementation/statement.dart 109 StatementImplementation._execute
package:sqlite3/src/implementation/statement.dart 289 StatementImplementation.executeWith
package:sqlite3/src/statement.dart 79 CommonPreparedStatement.execute
package:drift/src/sqlite3/database.dart 149 Sqlite3Delegate.runWithArgsSync
package:drift/native.dart 392 _NativeDelegate.runUpdate.<fn>
dart:async/future.dart 313 new Future.sync
package:drift/native.dart 391 _NativeDelegate.runUpdate
package:drift/src/runtime/executor/helpers/engines.dart 96 _BaseExecutor.runDelete.<fn>
package:drift/src/runtime/executor/helpers/engines.dart 61 _BaseExecutor._synchronized
package:drift/src/runtime/executor/helpers/engines.dart 93 _BaseExecutor.runDelete
package:drift/src/runtime/api/connection.dart 102 DatabaseConnection.runDelete
package:drift/src/remote/server_impl.dart 158 ServerImplementation._runQuery
package:drift/src/remote/server_impl.dart 119 ServerImplementation._handleRequest.<fn>
package:drift/src/remote/communication.dart 165 DriftCommunication.setRequestHandler.<fn>
===== asynchronous gap ===========================
package:drift/src/remote/communication.dart 113 DriftCommunication.request
package:drift/src/remote/client_impl.dart 101 _BaseExecutor._runRequest
package:drift/src/remote/client_impl.dart 110 _BaseExecutor._intRequest
package:drift/src/remote/client_impl.dart 125 _BaseExecutor.runDelete
package:drift/src/utils/lazy_database.dart 77 LazyDatabase.runDelete
package:drift/src/runtime/api/connection.dart 102 DatabaseConnection.runDelete
package:drift/src/utils/lazy_database.dart 77 LazyDatabase.runDelete
package:drift/src/runtime/query_builder/statements/delete.dart 53 DeleteStatement.go.<fn>
package:drift/src/runtime/api/connection_user.dart 171 DatabaseConnectionUser.doWhenOpened.<fn>
dart:async/future_impl.dart 861 Future._propagateToListeners.handleValueCallback
dart:async/future_impl.dart 890 Future._propagateToListeners
dart:async/future_impl.dart 666 Future._completeWithValue
dart:async/future_impl.dart 736 Future._asyncCompleteWithValue.<fn>
dart:async/schedule_microtask.dart 40 _microtaskLoop
dart:async/schedule_microtask.dart 49 _startMicrotaskLoop

Device parameters:

id: AP4A.241205.013
board: lynx
bootloader: lynx-15.1-12292122
brand: google
device: lynx
display: AP4A.241205.013
fingerprint: google/lynx/lynx:15/AP4A.241205.013/12621605:user/release-keys
hardware: lynx
host: r-456ae1c9fa6a8c5c-nbp0
isPhysicalDevice: true
manufacturer: Google
model: Pixel 7a
product: lynx
tags: release-keys
type: user
versionBaseOs:
versionCodename: REL
versionIncremental: 12621605
versionPreviewSdk: 0
versionRelease: 15
versionSdk: 35
versionSecurityPatch: 2024-12-05

Application parameters:

environment: release
version: 3.0.0
appName: Contactulater
buildNumber: 153
packageName: es.com.skynet.contactulater

Discussed in https://github.com/simolus3/drift/discussions/3343

^{Originally posted by mrclauss November 17, 2024} Hi folks, I do have a sporadic issue resulting in a SQLite "database is locked" error, however I'm not sure how to debug/resolve this. Are there any hints for solving this?

Drift version: "2.19.1+1"

Stacktrace looks like this:

SqliteException(5): while executing statement, database is locked, database is locked (code 5) Causing statement: UPDATE <.snipped.>, parameters: <.snipped.>

Stack trace:

package:sqlite3/src/implementation/exception.dart 75 throwException
package:sqlite3/src/implementation/statement.dart 109 StatementImplementation._execute
package:sqlite3/src/implementation/statement.dart 289 StatementImplementation.executeWith
package:sqlite3/src/statement.dart 79 CommonPreparedStatement.execute
package:drift/src/sqlite3/database.dart 149 Sqlite3Delegate.runWithArgsSync
package:drift/native.dart 340 _NativeDelegate.runUpdate.<fn>
dart:async/future.dart 313 new Future.sync
package:drift/native.dart 339 _NativeDelegate.runUpdate
package:drift/src/runtime/executor/helpers/engines.dart 96 _BaseExecutor.runDelete.<fn>
package:drift/src/runtime/executor/helpers/engines.dart 61 _BaseExecutor._synchronized
package:drift/src/runtime/executor/helpers/engines.dart 93 _BaseExecutor.runDelete
package:drift/src/runtime/api/connection.dart 102 DatabaseConnection.runDelete
package:drift/src/remote/server_impl.dart 153 ServerImplementation._runQuery
package:drift/src/remote/server_impl.dart 118 ServerImplementation._handleRequest.<fn>
package:drift/src/remote/communication.dart 165 DriftCommunication.setRequestHandler.<fn>
===== asynchronous gap ===========================
package:drift/src/remote/communication.dart 113 DriftCommunication.request
package:drift/src/remote/client_impl.dart 97 _BaseExecutor._runRequest
package:drift/src/remote/client_impl.dart 114 _BaseExecutor.runDelete
package:drift/src/utils/lazy_database.dart 91 LazyDatabase.runUpdate
package:drift/src/runtime/api/connection.dart 115 DatabaseConnection.runUpdate
package:drift/src/utils/lazy_database.dart 91 LazyDatabase.runUpdate
package:drift/src/runtime/query_builder/statements/update.dart 36 UpdateStatement._performQuery.<fn>
package:drift/src/runtime/api/connection_user.dart 171 DatabaseConnectionUser.doWhenOpened.<fn>
package:drift/src/runtime/query_builder/statements/update.dart 35 UpdateStatement._performQuery
package:drift/src/runtime/query_builder/statements/update.dart 158 UpdateStatement.replace
package:contactulater/db/userpropdao.dart 66 UserPropDao.update
package:contactulater/propertymanager.dart 781 PropertyManager.refreshDirtyNative.<fn>
package:synchronized/src/basic_lock.dart 36 BasicLock.synchronized

Any help appreciated :)

[simolus3]

Thanks for the follow-up! Unfortunately, these errors are not easy to debug since sqlite3 can't tell you which trace has originally obtained the lock.

Could you share how you're opening the database? If you're using driftDatabase() from package:drift_flutter, there's a new option that can make drift manage the isolates for you, automatically sharing them if a background / foreground service in the same Flutter engine are using the same database.

[mrclauss]

Previously I constructed it using openConnection, now I changed it to driftDatabase() from drift_flutter, but I can't find the option regarding isolates?

Here's the diff:

 class DriftDb extends _$DriftDb {
-  DriftDb() : super(openConnection());
+  DriftDb() : super(_openConnection());

+  static QueryExecutor _openConnection() {
+    // `driftDatabase` from `package:drift_flutter` stores the database in
+    // `getApplicationDocumentsDirectory()`.
+    return driftDatabase(name: 'db', );
+  }

[simolus3]

The option is nested under the native key: https://pub.dev/packages/drift_flutter#sharing-databases-between-isolates

[mrclauss]

Thanks! Oddly that didn't appear in my Android Studio when I wrote the comment... I'll give it a try now 👍

[orestesgaolin]

Is that option available for non flutter_drift but just drift projects?

[simolus3]

Not really, the implementation relies on IsolateNameServer which is Flutter-only. I don't think there's a way to port it to pure Dart (as far as I know, there's no way to establish a communication channel between isolates that don't know each other).

[kaciula]

I'm also getting crash reports about this. The database calls are from the main isolate and it looks that currently it only happens on Windows. Any ideas on how to reproduce?

[orestesgaolin]

One way to reproduce locking issue is to open the database file with some external app. E.g. when running on iOS simulator I can locate my .db file somewhere in /Users/xyz/Library/Developer/CoreSimulator/Devices/A842F0B4-B420-4FF4-AC01-9C30D5531144/data/Containers/Data/Application/A09BC798-7681-40B2-B0F4-17F061BAAE5A/ and open it with SQLite Browser. Then when you open the app the database would be locked. Then you can see how your app behaves when the locking occurs, handle it gracefully or introduce retrying policy that works outside of drift.