sqlite - 颤振：警告数据库已被锁定为 0:00:10.000000。在安卓上
问题描述
我在这个错误上停留了一个月，我无法通过谷歌找到任何解决方案。我构建了一个需要在线和离线工作的聊天应用程序，后端是 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]);
}
}
}
});
});
}
以下是处理来自服务器的事件的方法：
插座
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);
});
}
方法
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 上我总是遇到这个错误，我的应用程序卡住了：
颤振：警告数据库已被锁定为 0:00:10.000000。确保在事务期间始终使用事务对象进行数据库操作
任何帮助都是合适的
更新
我试图将数据库函数修改为：
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;
}
但我仍然面临同样的问题
解决方案
确保await事务中的任何异步方法：
await batch.commit();
当您忘记此类await.
是的，不要为单个操作创建事务并尝试对事务中的操作进行分组（如果可以的话，也可以进行批处理）。
您还可以打开日志记录以确保您没有执行过多的 sqlite 查询。