NodeJS concurrential access to MySQL database

Question

i'm facing an issue with my MySQL database and my RESTful API in NodeJS

I have a request that does this :

function plusOneToValue(id, callback) {
    MySQLDatabase.pool.getConnection(function(err, connection) {
        if (err) return callback(err)
        connection.query("SELECT id, value, property FROM values WHERE id = ?", 
            [id], function(err, entries) {
            if (err) {
                connection.release();
                return callback(err);
            }
            if (entries.length == 0) {
                connection.release();
                return callback("some error");
            }
            var entry = entries[0]
            if (entry.property = true)
                connection.query("UPDATE values SET value = ? WHERE id = ?", 
                    [entry.value + 1, entry.id], function(err, users) {
                    connection.release();
                    if (err) return callback(err);
                    return callback();
                });
            else {
                connection.release();
                return callback();
            }
        });
    });
}

My problem is that when i call this function 3 times at the same time, it has this behavior :

SELECT
SELECT
SELECT
UPDATE
UPDATE
UPDATE

When it should have :

SELECT
UPDATE
SELECT
UPDATE
SELECT
UPDATE

I've tried to fixed it with semaphores, but the thing is i don't want it to block at every call ! Only if there's a concurrential access to the row that has the id passed in parameter.

Does anyone know how i can achieve this ?

I'm sorry but i'm not in node.js .. normally if there are db command there are also command for transaction .. — ScaisEdge, Oct 30 '16 at 21:58
Nope, it's not transactions you need this time, but locking or isolation level setting. — Shadow, Oct 30 '16 at 22:34
But, in this particular example you can do this in a single update without select, which would eliminate the need for explicit locking. — Shadow, Oct 30 '16 at 22:36
@Shadow well the code is not that easy, it involves more parameters and more checking. this is a very simplified version — Devz, Oct 30 '16 at 22:38
Gave you a link to an exact duplicate question. Do not be mislead by the title. This is handled on mysql level, not at pdo or node.js. Read all answers in the duplicate topic, not just mine. — Shadow, Oct 30 '16 at 22:41
@Shadow ok so if i got everything, i have to set my transaction isolation to serializable so that mysql "locks" my transaction ? is that right ? — Devz, Oct 30 '16 at 22:54
No. Mysql does not lock transactions. Depending on the table engine used, mysql can lock a table, range of records, or a single record. Serializable isolation level does what you need in this particular case, but you should consider using a different level combined with explicit locking read. — Shadow, Oct 30 '16 at 23:00
The fundamental issue here is that you are doing this wrong. Let the database perform an atomic update and you have no issue. Use this: `UPDATE values SET value = value + ? WHERE id = ?` passing the *change* as the argument, not the new value. The database will never get this wrong, no matter how much concurrency exists, even if you don't explicitly use transactions. **Do not do math in your code and then splat the new value into the database.** — Michael - sqlbot, Oct 31 '16 at 00:17
Otherwise, inside a transaction, `SELECT ... FOR UPDATE` and other transations asking for the same thing will wait until you commit or roll back... that the locking read @Shadow is referring to. — Michael - sqlbot, Oct 31 '16 at 00:23
@Michael-sqlbot you are duplicating the conversation I had with the OP earlier and the OP has responded that the above code is simplified. — Shadow, Oct 31 '16 at 00:25

NodeJS concurrential access to MySQL database

0 Answers0