My lord, serious lock problem when try accessing data while updating it.

[OpenJarvisAI]

Hello, got a serious problem, I have no idea how to resolve it.

situation:

I got an chat app, when pushing to conversation page, I need query out the messages in a room, and also updating it's status to displayed

but when unread messages are many, it got stuck UI.

Here is what I do now:

// set msg display in DAO

Future<void> setMessageDisplayed(String id) async {
    DbMessage? msg = await getMessageById(id);
    if (msg != null) {
      var other = msg.copyWith(status: "displayed");
      (update(messages)..where((t) => t.id.equals(id))).write(other);
    }
  }

// and this function will be called in `build` function in UI, and in build it will query data and render on UI.

 Widget _buildMainChat(List<DbMessageWithDbContactOrRoomMember> rawData) {
    if ((rawData.isEmpty) && chatPageController.messages.isEmpty) {
      chatPageController.messages = [];
    } else {
      setMsgDisplayed(rawData);
      if (rawData.isNotEmpty &&
          rawData.first.message.roomId != widget.contactChat!.roomId) {
        // drop it
        kLog(
            "looks like wrong room data?? ${rawData.first.message.toJson()} ${rawData.first.contact}");
        rawData = rawData
            .where((element) =>
                element.message.roomId == widget.contactChat!.roomId)
            .toList();
      }
      if (rawData.isNotEmpty &&
          chatPageController.messages.isNotEmpty &&
          rawData.last.message.id == chatPageController.messages.last.id &&
          rawData.length == chatPageController.messages.length) {
        // last message same, last page?
        kLog("is last page? delta last same as now");
        // isLastPgae = true;
        var delta = convertToUiMsg(rawData);
        chatPageController.messages = delta;
      } else {
        var delta = convertToUiMsg(rawData);
        kLog("---&&& ${delta.length} $lastPageMsgId");
        chatPageController.messages = delta;
      }
    }

    return Container(
      height: MediaQuery.of(context).size.height,
....

}

Now, when unread messge more than 5, it will stuck UI, any guidance to follow?

[simolus3]

First, you don't need to load a message to update one of its columns. The method can be written as

Future<void> setMessageDisplayed(String id) async {
  await (update(messages)..where((t) => t.id.equals(id)))
      .write(MessagesCompanion(status: Value('displayed')));
}

Can you add a print in setMessageDisplayed to verify that it's not being called more often than you intended? For 5 messages, even with the select the method should not freeze the UI.

[OpenJarvisAI]

@simolus3 it was just called 5 times same as messages length.

Does the simuetlously query and write db caused a lock problem caused the time spent more than expected?

[OpenJarvisAI]

It was still a little laggy when removed the get mssgage in first place.

When unread mesage have more than 5, a very obvious laggy got on UI

[simolus3]

Does the simuetlously query and write db caused a lock problem caused the time spent more than expected?

It really shouldn't. Can you enable logStatements: true on the NativeDatabase constructor? Do you see an unexpected amount of statements being executed when there is an UI lag? If not, one of those statements must be really slow (but these statements don't look like they should be slow...)

Are you using NativeDatabase.createInBackground as shown in the last snippet of this section? Or are you using a regular NativeDatabase? That runs SQL in the same thread as the UI which can cause lags, but typically not with these tiny queries.

[OpenJarvisAI]

@simolus3 thanks for the reply first.

Here is my database.dart code:

Future<DriftIsolate> _createDriftIsolate() async {
  // this method is called from the main isolate. Since we can't use
  // getApplicationDocumentsDirectory on a background isolate, we calculate
  // the database path in the foreground isolate and then inform the
  // background isolate about the path.
  final dir = await getApplicationDocumentsDirectory();
  final path = p.join(dir.path, 'db.sqlite');
  final receivePort = ReceivePort();

  await Isolate.spawn(
    _startBackground,
    _IsolateStartRequest(receivePort.sendPort, path),
  );

  // _startBackground will send the DriftIsolate to this ReceivePort
  return await receivePort.first as DriftIsolate;
}

void _startBackground(_IsolateStartRequest request) {
  // this is the entry point from the background isolate! Let's create
  // the database from the path we received
  final executor = NativeDatabase(File(request.targetPath), setup: (database) {
    database.execute('PRAGMA journal_mode=WAL;');
  });
  // we're using DriftIsolate.inCurrent here as this method already runs on a
  // background isolate. If we used DriftIsolate.spawn, a third isolate would be
  // started which is not what we want!
  final driftIsolate = DriftIsolate.inCurrent(
    () => DatabaseConnection(executor),
  );
  // inform the starting isolate about this, so that it can call .connect()
  request.sendDriftIsolate.send(driftIsolate);
}

// used to bundle the SendPort and the target path, since isolate entry point
// functions can only take one parameter.
class _IsolateStartRequest {
  final SendPort sendDriftIsolate;
  final String targetPath;

  _IsolateStartRequest(this.sendDriftIsolate, this.targetPath);
}

LazyDatabase _openConnection() {
  // the LazyDatabase util lets us find the right location for the file async.
  return LazyDatabase(() async {

    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'chat2.sqlite'));
    if (!await file.exists()) {
      dbFolder.create();
    }
    return NativeDatabase(file);
  });
}

@DriftDatabase(tables: [
  Contacts,
  Users,
  Messages,
  RoomMember
], daos: [
  UserDao,
  MessageDao,
  RoomMemberDao
])
class MyDatabase extends _$MyDatabase {
  static MyDatabase? _instance;

  static MyDatabase? instance() {
    _instance ??= MyDatabase._();
    return _instance;
  }

// we tell the database where to store the data with this constructor
  MyDatabase._() : super(_openConnection());

  // you should bump this number whenever you change or add a table definition. Migrations
  // are covered later in this readme.
  @override
  int get schemaVersion => 8;

  @override
  MigrationStrategy get migration {
    return MigrationStrategy(
      onCreate: (Migrator m) async {
        await m.createAll();
      },

    );
  }
}

I forget where the code comes from, but seems using NativeDatabase, however, it runs a isolate.

Can u see anything wrong with my code usage?

Furthermore, there could be another possible reason that, the code have a query listen to messages in build function from UI, if I write it and build it, will caused laggy?

[simolus3]

You have a lot of isolate code there, but the relevant _openConnection method is not using it. Using isolates has become a lot simpler in recent drift versions though, so you can just use:

LazyDatabase _openConnection() {
  // the LazyDatabase util lets us find the right location for the file async.
  return LazyDatabase(() async {
    final dbFolder = await getApplicationDocumentsDirectory();
    final file = File(p.join(dbFolder.path, 'chat2.sqlite'));
    if (!await file.exists()) {
      dbFolder.create();
    }
    return NativeDatabase.createInBackground(file, setup: (rawDb) {
      rawDb.execute('PRAGMA journal_mode=WAL;');
    });
  });
}

You can delete _createDriftIsolate, _startBackground and _IsolateStartRequest which you don't seem to be using.

Furthermore, there could be another possible reason that, the code have a query listen to messages in build function from UI, if I write it and build it, will caused laggy?

If a result from the database causes a rebuild, which then causes another query and so on, then yes - that could be a problem. But since you didn't see many queries from logStatements, I think you're not running into that issue.