sqlite - flutter:警告数据库已锁定 0:00:10.000000。在安卓上

Question

我在这个错误上停留了一个月，我无法通过谷歌找到任何解决方案。我构建了一个需要在线和离线工作的聊天应用程序，后端是 NodeJS下面的代码是将任何仍未决的消息发送到目标设备

  async function _worker(socket) {
    console.log('worker running...')
    await connect.then(async (db) => {

        // When user A sent message to user B but user B is offline so server need to sync this message to user B
        Message.find({ isDeliver: 0 }).then(async (m) => {
            if (m.length > 0) {
                for (let i = 0; i < m.length; i++) {
                    let sender = m[i].sender;
                    let receiver = m[i].receiver;
                    let online = await checkUserOnline(socket, receiver);
                    if (online) {
                        let to_user_socket_id = getSocketIDfromMapForthisUser(`${receiver}`)
                        sendToConnectedSocket(socket, to_user_socket_id, "send_message_to_device", m[i]);
                    }
                }
            }
        });

         // When user A sent a message to user B and user B is offline but when user B is online and message delivered to user B and user A is offline server need to sync this message status to update for user A
         Message.find({ isDeliver: 1 }).then(async (m) => {
            if (m.length > 0) {
                for (let i = 0; i < m.length; i++) {
                    let sender = m[i].sender;
                    let receiver = m[i].receiver;
                    let online = await checkUserOnline(socket, sender);
                    if (online) {
                        let to_user_socket_id = getSocketIDfromMapForthisUser(`${sender}`)
                        sendToConnectedSocket(socket, to_user_socket_id, "send_message_deliver", m[i]);
                    }
                }
            }
        });

        // When user A sent a message to user B and user B is offline but when user B is online and read the message and user A is offline server need to sync this message status to update for user A
        Message.find({ isRead: 1 }).then(async (m) => {
            if (m.length > 0) {
                for (let i = 0; i < m.length; i++) {
                    let sender = m[i].sender;
                    let receiver = m[i].receiver;
                    let online = await checkUserOnline(socket, sender);
                    if (online) {
                        let to_user_socket_id = getSocketIDfromMapForthisUser(`${sender}`)
                        sendToConnectedSocket(socket, to_user_socket_id, "send_message_read", m[i]);
                    }
                }
            }
        });

    });
 }

下面是处理来自服务器的事件的方法:

Socket

setOnServerSendDeliver(Function  onServerSendDeliver) {
    _socket.on('send_message_deliver', (data) {
        onServerSendDeliver(data);
    });
  }

  setOnServerSendRead(Function  onServerSendRead) {
    _socket.on('send_message_read', (data) {
      onServerSendRead(data);
    });
  }

  setOnServerSendToDevice(Function onServerSendToDevice) {
    _socket.on('send_message_to_device', (data) {
      onServerSendToDevice(data);
    });
  }

Method

 onServerSendDeliver(data) async {
    MessageModel message = MessageModel.fromJson(jsonDecode(data));
    await G.dbService.updateDeliver(message);
    G.socketUtils.sendDeliveryDone(message, new UserModel(id: message.sender));
    refreshMessage();
  }

  onServerSendRead(data) async {
    MessageModel message = MessageModel.fromJson(jsonDecode(data));
    await G.dbService.updateRead(message.chatId);
    G.socketUtils.sendReadDone(message, new UserModel(id: message.sender));
    refreshMessage();
  }

  onServerSendToDevice(data) async {
    MessageModel message = MessageModel.fromJson(jsonDecode(data));
    ChatModel chat = new ChatModel();
    chat.id = message.chatId;
    chat.fromId = message.sender;
    chat.toId = message.receiver;
    chat.message = message.content;
    await G.dbService.chatOperation(chat);
    await G.dbService.insertMessage(message);
    await G.dbService.updateDeliver(message);
    G.socketUtils.sendDelivery(message, new UserModel(id: message.sender));
    refreshMessage();
  } 

在我设置的服务器中

setInterval(_worker, 1500, socket);

加载检查消息并发送到终端设备

和我的数据库函数

Future<String> updateRead(String chatId) async {
    Database db = await database;
    try {
      await db.transaction((txn) async {
        return await txn.rawUpdate(
            "UPDATE messages SET isRead = 1, isSend = 1, isDeliver = 1 WHERE chatId = ? AND status = 0",
            [chatId]);
      });

      await db.transaction((txn) async {
        return await txn.rawUpdate(
            "UPDATE chats SET isRead = 1, isSend = 1, isDeliver = 1 WHERE id = ? AND status = 0",
            [chatId]);
      });
    } catch (e) {
      print(e.toString());
    }
    return chatId;
  }

  Future<String> updateDeliver(MessageModel message) async {
    Database db = await database;
    String id;
    try {
      id = message.id;
      await db.transaction((txn) async {
        return await txn.rawUpdate(
            "UPDATE messages SET isDeliver = 1 WHERE id = ? AND status = 0",
            [message.id]);
      });

      await db.transaction((txn) async {
        return await txn.rawUpdate(
            "UPDATE chats SET isDeliver = 1 WHERE id = ? AND status = 0",
            [message.chatId]);
      });
    } catch (e) {
      print(e.toString());
    }
    return id;
  } 

iOS 运行良好，但在 Android 上我总是遇到此错误并且我的应用卡住了:

flutter: Warning database has been locked for 0:00:10.000000. Make sure you always use the transaction object for database operations during a transaction

任何帮助都是合适的

更新

我尝试将数据库函数修改为:

Future<String> updateDeliver(MessageModel message) async {
    Database db = await database;
    String id;
    try {
      id = message.id;
      await db.transaction((txn) async {
        var batch = txn.batch();
        batch.rawUpdate(
            "UPDATE messages SET isDeliver = 1 WHERE id = ? AND status = 0",
            [message.id]);
        batch.rawUpdate(
            "UPDATE chats SET isDeliver = 1 WHERE id = ? AND status = 0",
            [message.chatId]);
        batch.commit();
      });
    } catch (e) {
      print(e.toString());
    }
    return id;
  }

但我还是遇到了同样的问题

Answer 1

确保 await 事务中的任何异步方法:

await batch.commit();

当您忘记这样的 await 时，您应该使用 pedantic(或适当的 lints)来获得警告。

是的，不要为单个操作创建事务并尝试在事务中对操作进行分组(如果可以的话，还可以进行批处理)。

你也可以 turn on logging以确保您没有进行过多的 sqlite 查询。