database is locked error with WAL journal mode

[albe-jj]

Note: I activated WAL journal mode on the sql database.

In my flutter application when trying to insert a specific entry into the database I get the error below. I can insert other entries in the database before and after getting this error but every time I try to insert this specific entry it throws the error below. I'm using one single database connection for all insert operations. When restarting the flutter app then I can insert the specific entry to the database without any problem.

In summary it appears that the database (set in WAL mode) is locked only for the insertion of this specific entry and after reconnecting to the database i can insert the entry without any problem.

SqliteException(5): while executing statement, database is locked, database is locked (code 5)
  Causing statement: INSERT INTO "datasets" ("uuid", "alt_uid", "collected", "created", "modified", "name", "scope_id", "creator", "description", "keywords", "search_helper", "ranking", "synchronized") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parameters: 16896f843dfc4f31901cef9ec5f1e989, null, 2024-05-30 16:10:23.818075, 2024-05-30 16:10:24.665727, 2024-05-30 16:10:37.462207, 2D_Sweep_0D_param_test, 14, sphilips, null, [], 16896f84-3dfc-4f31-901c-ef9ec5f1e989 2D_Sweep_0D_param_test, 0, 1
#0   package:sqlite3/src/implementation/exception.dart 75               throwException
#1   package:sqlite3/src/implementation/statement.dart 109              StatementImplementation._execute
#2   package:sqlite3/src/implementation/statement.dart 289              StatementImplementation.executeWith
#3   package:sqlite3/src/statement.dart 72                              CommonPreparedStatement.execute
#4   package:drift/src/sqlite3/database.dart 149                        Sqlite3Delegate.runWithArgsSync
#5   package:drift/native.dart 332                                      _NativeDelegate.runInsert.<fn>
#6   package:drift/native.dart 331                                      _NativeDelegate.runInsert
#7   package:drift/src/runtime/executor/helpers/engines.dart 105        _BaseExecutor.runInsert.<fn>

[simolus3]

Well, what's the specific entry? It's pretty weird that the database is only locked with the one particular row though. Also, are you sure it's related to the entry and not something like "the first 5 inserts work and then this happens"?

[albe-jj]

In the case above the insert that failed was INSERT INTO "datasets" ("uuid", "alt_uid", "collected", "created", "modified", "name", "scope_id", "creator", "description", "keywords", "search_helper", "ranking", "synchronized") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parameters: 16896f843dfc4f31901cef9ec5f1e989, null, 2024-05-30 16:10:23.818075, 2024-05-30 16:10:24.665727, 2024-05-30 16:10:37.462207, 2D_Sweep_0D_param_test, 14, sphilips, null, [], 16896f84-3dfc-4f31-901c-ef9ec5f1e989

But i get this randomly every say 500 inserts, for different datasets entries I try to insert.

I can insert other dataset entries before and after getting "database locked error when inserting dataset X", to be able to insert database X into the database i need to restart the application. If I don't restart the application I can still insert other datasets but every time I try to insert dataset X i get "database locked" error.

The application is running on windows desktop app.

Let me know if there is any useful test I could do to debug this.

[albe-jj]

I managed to systematically reproduce the error with the following code.

void main() async {

  final db = MyDb('test_db.sql');
  final db2 = MyDb('test_db.sql');

  final futures = [];
  for (int i=0; i<100; i++) {
    futures.add(db.into(db.datasets).insert(createRandomDataset(scopeId: 1)));
    futures.add(db2.into(db2.datasets).insert(createRandomDataset(scopeId: 1)));
    print('insert: $i');
  }

  for (var f in futures) {
    await f;
  }

  // await Future.delayed(const Duration(seconds: 10));
  await db.close();
  await db2.close();
}

I set the database WAL mode like this:

  @override
  MigrationStrategy get migration => MigrationStrategy(
        beforeOpen: (details) async {
          await customStatement('PRAGMA journal_mode=WAL'); //TO enable access from multiple processes
        },
      );

I have most recent dependencies

dependencies:
  drift: ^2.18.0
  sqlite3_flutter_libs: ^0.5.0
  path_provider: ^2.0.0
  path: ^1.9.0

dev_dependencies:
  drift_dev: ^2.18.0
  build_runner: ^2.4.9

and running latest dart sdk Dart SDK version: 3.4.1 (stable) and stable flutter: 3.22.1

Let me know if there is something wrong from my side.

[simolus3]

Do you have multiple concurrent writers in your real application as well? WAL only allows concurrent reads and a single writer. Two concurrent writers will still cause lock errors with WAL. If that's the problem you could try setting the busy_timeout pragma which will make sqlite3 sleep and retry the operation shortly afterwards if a lock is active. That can reduce the likelihood of the error, a proper solution is to coordinate access between the two databases to avoid concurrent writers (doable with drift, I can provide more pointers on this if that turns out to be the root cause, but it gets much harder if one of the databases is opened natively and not from Dart).