CODEWITHSUNDEEP
mysqlnode.jstransactionsnode-mysql
Mateusz Bartkowiak
Mateusz Bartkowiak

Reputation: 154

Queries are not executed after transaction is rolled back

I want to execute transactions in node-mysql (by executing queries START TRANSACTION, ROLLBACK, COMMIT). I have this piece of code:

conn.query("START TRANSACTION");
conn.query("INSERT INTO users (id, name, age) VALUES (1, 'Mat', '24')", function(e){
    if (e){
        conn.query("ROLLBACK", callback);
    }
    else{
        conn.query("COMMIT", callback);
    }
});

conn.query("INSERT INTO users (name, age) VALUES ('Mat', '24')", function(e, r){
    console.log(e); // null
    console.log(r); // {fieldCount: 0, affectedRows: 1, insertId: 2, serverStatus: 3, warningCount: 0, message: ''}
});

To test rollback case I have intentionally set id to 1, which causes index duplicate error. The transaction is rolled back, but the problem is that the second insert, which is outside the transaction is not executed. There is no error logged, and - what is strange for me - when I select whole table just after that second insert, there is new row in results. But in another connections it disappears. So it looks like at the end of connection newly created row is deleted. The problem doesn't appear when transaction is commited.

Upvotes: 0

Views: 340

Answers (1)

JohnnyHK
JohnnyHK

Reputation: 312035

Your second INSERT INTO can execute prior to your ROLLBACK in the first INSERT INTO callback because it's executing outside of the callback. When that happens your second insert is also getting rolled back because you're using a single connection. Move the second insert inside the callback of the first to serialize things.

Upvotes: 1

Related Questions