SqliteException(5): while executing statement, database is locked, database is locked (code 5)

[SungHyun22]

Hi simolus3, first of all thank you for good library!

Bug Explain

I'm currently opening drift's sqlite file in IOS WidgetKit with the code below.

let directory = FileManager.default.containerURL(forSecurityApplicationGroupIdentifier: "group.com.example.project")
let dbDirectory = directory!.appendingPathComponent("example_db.sqlite").path()
var db: OpaquePointer? 

if sqlite3_open_v2(dbDirectory, &db, 0x00000001, nil) == SQLITE_OK {
     return db
} else {
     return nil
}

My guess is that this open db is causing the SqliteException(5). But the weird thing is that I opened sqlite in read mode. As I understand it, sqlite3 can be accessed by multiple processes concurrently. Is that not available in Drift? Or is there something I'm misunderstanding?

Logs

Fatal Exception: FlutterError SqliteException(5): while executing statement, 
database is locked, database is locked (code 5) Causing statement: INSERT INTO 
"routine_histories_d_b_entity" ("routine_id", "created", "complete_list") 
VALUES (?, ?, ?), parameters: 1, 2023-12-10T19:55:00.328656,
0
exception.dart - 75번째 줄
(null).throwException + 75
1
statement.dart - 113번째 줄
StatementImplementation._execute + 113
2
statement.dart - 305번째 줄
StatementImplementation.executeWith + 305
3
statement.dart - 55번째 줄
CommonPreparedStatement.execute + 55
4
database.dart - 142번째 줄
Sqlite3Delegate.runWithArgsSync + 142
5
native.dart - 307번째 줄
_NativeDelegate.runInsert.<fn> + 307
6
dart:async
new Future.sync
7
native.dart - 306번째 줄
_NativeDelegate.runInsert + 306
8
engines.dart - 105번째 줄
_BaseExecutor.runInsert.<fn> + 105
9
engines.dart - 61번째 줄
_BaseExecutor._synchronized + 61
10
engines.dart - 102번째 줄
_BaseExecutor.runInsert + 102
11
lazy_database.dart - 78번째 줄
LazyDatabase.runInsert + 78
12
insert.dart - 74번째 줄
InsertStatement.insert.<fn> + 74
13
connection_user.dart - 162번째 줄
DatabaseConnectionUser.doWhenOpened.<fn> + 162
14
insert.dart - 73번째 줄
InsertStatement.insert + 73
15
routine_history_db.dart - 31번째 줄
RoutineHistoryDB.createRoutineHistory + 31

Environment

flutter doctor

Doctor summary (to see all details, run flutter doctor -v):
[✓] Flutter (Channel stable, 3.13.6, on macOS 14.0 23A344 darwin-arm64, locale ko-KR)
[✓] Android toolchain - develop for Android devices (Android SDK version 34.0.0)
[✓] Xcode - develop for iOS and macOS (Xcode 15.0)
[✓] Chrome - develop for the web
[✓] Android Studio (version 2022.3)
[✓] IntelliJ IDEA Ultimate Edition (version 2023.1.3)
[✓] VS Code (version 1.85.0)
[✓] Connected device (2 available)
[✓] Network resources

• No issues found!

Drift Version

sqlite3_flutter_libs: ^0.5.16
sqflite: ^2.3.0
drift: ^2.11.1

Reproduce Step

Reproducing in a new project. Give me a moment. The reproduction steps I'm thinking of are as follows

1. open the DB on IOS.
2. open DB in Drift.
3. perform a migration or insert operation.
4. check the logs.

End

If you need additional information, please ask! Thanks for reading this long post.

[simolus3]

sqlite3 can be accessed by multiple processes concurrently

By default, sqlite3 uses a locking scheme that will not allow concurrent reads and writes. If one connection is read-only, enabling write-ahead logging with pragma journal_mode = wal; (you can use a customStatement() in MigrationStrategy.beforeOpen for that) might fix this.

[JerContact]

I'm getting a similar issue using an isolate trying to connect to the same database, @simolus3 could you give an example of how you would write this out in the customStatement()? Is there some way a read can wait for the other task to end?

This is my code below.

LazyDatabase _openConnection() {
  return LazyDatabase(() async {
    final dbDirectory = await getDatabaseDirectory();
    final file = p.join(dbDirectory, 'slt_TEST.sqlite');
    // Adding c++ file to see if file exists
    initMetadataNativeDBConnection(
        databasePath: file,
        metadataTableName: DatabaseConstants.audioMetadataMainTable,
        metadataFtsTableName: DatabaseConstants.audioMetadataFtsTable);
    return NativeDatabase.createInBackground(
      File(file),
      setup: (database) {
        database.execute('PRAGMA journal_mode=WAL;');
        database.execute('PRAGMA foreign_keys=ON;');
        database.execute('PRAGMA recursive_triggers = ON;');
      },
    );
  });
}

/// This creates a new isolate server for drift
///
/// returns a DriftIsolate
Future<DriftIsolate> _createDriftIsolate() async {
  final token = RootIsolateToken.instance;
  return await DriftIsolate.spawn(() {
    // This function runs in a new isolate, so we must first initialize the
    // messenger to use platform channels.
    BackgroundIsolateBinaryMessenger.ensureInitialized(token!);

    // The callback to DriftIsolate.spawn() must return the database connection
    // to use.
    return LazyDatabase(() async {
      final dbDirectory = await getDatabaseDirectory();
      final file = p.join(dbDirectory, 'slt_TEST.sqlite');
      return NativeDatabase(
        File(file),
        // setup: (database) {
        //   database.execute('PRAGMA journal_mode=WAL;');
        //   database.execute('PRAGMA foreign_keys=ON;');
        //   database.execute('PRAGMA recursive_triggers = ON;');
        // },
      );
    });
  });
}

/// Starts up an isolate and connects it to the database
///
/// Uses [singleClientMode] for optimization purposes, knowing this will only be used once and
/// closed right after
///
/// Returns a DriftAudioMetadataDatabase connection
Future<DriftAudioMetadataDatabase> createIsolateDatabaseConnection() async {
  final driftIsolate = await _createDriftIsolate();
  return DriftAudioMetadataDatabase(
      await driftIsolate.connect(singleClientMode: true));
}

Trying to figure out how i can do this concurrent in an isolate without locking erros

[JerContact]

To add, i think i'm getting the locked when my app first starts which is trying to connect at the same time most likely

[simolus3]

createIsolateDatabaseConnection() and _openConnection connections are doing very similar things, are you using both of them?

If you want to share the database isolate between the clients, you can open the drift isolate first, you can then send it as a parameter to the other isolate you're spawning. Or does one of the locked exception come from C++?

[JerContact]

i think my issue was not opening the first database before the isolate went to town, I staggered the initialization and seems to be working now!

[SungHyun22]

@simolus3

Before we get started, I always appreciate a good library.

I used wal mode but database lock error was not solved,,,,
below is what i add to my code

      beforeOpen: (details) async {
        await customStatement('PRAGMA foreign_keys = ON');
        await customStatement('PRAGMA journal_mode = wal');
      },

Update 1

I found why this error occured. most of error occured in onUpgrade. and onUpgrade called before beforeOpen. So PRAGMA journal_mode = wal doesn't called on beforeOpen. Can I use wal mode in beforeOpen?

Update 2

Also, after enabling WAL mode, I got the below error along with it. Do you have any idea about this issue and if so, could you share it?

while executing, database is locked, database is locked (code 5)
  Causing statement: PRAGMA journal_mode = wal, parameters:

[simolus3]

Can I use wal mode in beforeOpen?

You could try doing this in the setup parameter that all constructors of NativeDatabase have, since that one is running before drift is doing anything else with the database. Something like setup: (db) => db.execute('PRAGMA journal_mode = wal') should do it.

Do you have any idea about this issue

I haven't seen this one before, but I wonder if it might be happening because the switch between journal modes temporarily needs to lock the whole database?

In addition, you could try setting the busy timeou pragma in setup which may make conflicts less likely.

[SungHyun22]

Thank you so much simolus3

We solved the problem