How to debug "database is locked" errors?

[mrclauss]

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]

Does this happen frequently? Are you opening the same drift isolate on multiple isolates? Or do you have one connection in drift and one outside of drift (including native code with your app possibly accessing the database)?

This should not happen when using a database with drift alone because drift has an internal async lock for the database. But if you're opening the same database file multiple times, there might be conflicts. Unfortunately I'm not a way to debug these issues (or printing where the lock was obtained). So the first step is determining where the database is opened and where it might be used concurrently.

[mrclauss]

Hi Simon, thanks for the ideas. I only see that sporadically. So what might happen is that the application code is running in the background due to a firebase trigger when the user opens the UI. On Android flutter this results in two isolates, which might be the cause for opening the database multiple times in different isolates. I'll try to check that and make those isolates mutually exclusive... Thanks, Martin Am 18. November 2024 00:12:00 MEZ schrieb Simon Binder ***@***.***>:

Does this happen frequently? Are you opening the same drift isolate on multiple isolates? Or do you have one connection in drift and one outside of drift (including native code with your app possibly accessing the database)? This should not happen when using a database with drift alone because drift has an internal async lock for the database. But if you're opening the same database file multiple times, there might be conflicts. Unfortunately I'm not a way to debug these issues (or printing where the lock was obtained). So the first step is determining where the database is opened and where it might be used concurrently. -- Reply to this email directly or view it on GitHub: #3343 (comment) You are receiving this because you authored the thread. Message ID: ***@***.***>

-- Martin Clauss Meine Contactulater Card - All meine Kontaktinfo immer aktuell und vollständig: https://contactulater.app/me/mc

[mrclauss]

After synchronising the background isolate triggered by firebase and the foreground isolate (UI) I did not observe this any more...
Thanks for your advice and hints that lead me to the right direction.