I'm really struggling to achieve something I actually consider to be pretty trivial and it is driving me crazy.
I just want the following MySQL database transaction to be mutually excluded. So a second call would have to wait until the first call is finished.
I already tried async-mutex framework from npm, wrapping all in promises, the SELECT ... FOR UPDATE clause from MySQL but none of these lead to the expected result.
The current behaviour is the following: when 2 HTTP requests come in simultaneously and call the following model function, they both enter the canEntryBeInserted function obviously leading to wrong results. I would like to have
1 2 1 2
but I get
1 1 2 2.
exports.addEntry = function (entry_id, type) {
return new Promise(function (resolve, reject) {
console.log("1")
beginTransactionP().then(function () {
canEntryBeInserted(entry_id, type).then(function (canBeInserted) {
if (canBeInserted) {
addEntrySQL(entry_id, type).then(function(){
finishTransactionP().then(function() {
console.log("2")
resolve();
})
})
} else {
finishTransactionP().then(function() {
console.log("2")
resolve();
})
}
})
})
})
}
addEntrySQL = function(entry_id, type){
return new Promise(function (resolve, reject) {
sql.query("INSERT INTO entry (entry_id, typ, time) VALUES (?, ?, NOW());", [entry_id, type], function (err, rows, fields) {
if (err) {
console.log("error: ", err);
reject(err);
}
else {
console.log("Inserted successfully.")
resolve();
}
});
})
}
canEntryBeInserted = function (entry_id, type) {
return new Promise(function (resolve, reject) {
sql.query("SELECT * FROM entry WHERE entry_id = ? AND typ = 1 UNION SELECT * FROM entry WHERE entry_id = ? ORDER BY entry_id DESC LIMIT 1;", [entry_id, entry_id], function (err0, rows0) {
console.log(JSON.stringify(rows0), type)
if (err0) {
console.log("error: ", err0);
reject(err0)
}
else if ((rows0.length == 1 && (rows0[0].typ == type || rows0[0].typ == 1)) || rows0.length == 2) {
console.log("Error: Not a valid operation.");
resolve(false)
}
else {
resolve(true)
}
})
})
}
The following code demonstrates how an asynchronous function can acquire a lock, which prevents concurrent execution until the lock is released again.
var locked;
function lock(req, res, next) {
if (locked)
locked.on("release", next);
else {
locked = new EventEmitter();
next();
}
}
function unlock() {
if (locked) {
locked.emit("release");
locked = undefined;
}
}
app.use(lock, function(req, res) {
console.log("1");
setTimeout(function() {
console.log("2");
res.end();
unlock();
}, 1000);
});
This maintains the lock on the Javascript level in the global variable lock. You may need something more fine-granular.
A database level lock with select for update is also possible, but if that fails the request would have to try again until it succeeds, because the database does not emit a release event like the code above.