I stuck at this error for a month I cannot find any solution via google. I build a chat app which need to work both online and offline, backend is NodeJS Below code is send any message that still in pending to target devices
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]);
}
}
}
});
});
}
and below is a method to handle event from server:
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();
}
in server I set
setInterval(_worker, 1500, socket);
to load check message and send to end devices
and my database function
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 is working fine but on android I always got this error and my app stuck:
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
Any help would be appropriate
Update
I tried to modify the database function to:
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;
}
but I still faced the same issue
Make sure to await any async method in a transaction:
await batch.commit();
You should use pedantic (or the proper lints) to get a warning when you forget such await.
And yes, don't create a transaction for a single operation and try to group actions in transaction (and batches if you can).
You could also turn on logging to make sure you are not doing too many sqlite queries.