CODEWITHSUNDEEP
node.jstransactionsmariadbmariasql
miah
miah

Reputation: 10433

Use transactions with mariadb and node.js

Is there a better way to use transactions with the mariasql library than adding BEGIN to the start of the query, and finalizing with either a commit or a rollback?

Currently, if I want to wrap a series of queries in a transaction I have to do something like this:

const MariaClient = require('mariasql');
let client = new MariaClient();

client.connect({
    host: "127.0.0.1",
    user: "user",
    password: "pass",
    db: "some_db",
    multiStatements: true
});

client.query('BEGIN; INSERT INTO some_table VALUES ("a0","b0"), ("a1","b1"), ("a2","b2");', function (err) {
    if (err) {
        client.query('ROLLBACK;');
    }
    client.query('COMMIT;');
});

This seems clunky and potentially error prone. We are using the generic-pool to manage the mariadb client, so it seems like there could be some unintended consequences handling transactions this way.

Upvotes: 0

Views: 1612

Answers (1)

Rick James
Rick James

Reputation: 142296

Plan A: If autocommit is set to 1, then each statement is its own transaction. No BEGIN/COMMIT needed.

Plan B: Suck it up and use separate calls to the API for each statement here:

BEGIN;
some SQL statement;
some SQL statement;
some SQL statement;
COMMIT;

If the API has special calls for BEGIN and COMMIT, use them instead of performing the corresponding SQL; there may be something important hiding in the call.

In both cases you must check for errors at all steps. Deadlocks can happen when you least expect them.

Upvotes: 1

Related Questions