Using transactions

Transactions with connections

You can execute transaction using SQL BEGIN/COMMIT/ROLLBACK, if you do so you must use a SqlConnection and manage it yourself.

Or you can use the transaction API of SqlConnection:

pool.getConnection(function (res, res_err) {
  if (res_err == null) {

    // Transaction must use a connection
    var conn = res;

    // Begin the transaction
    var tx = conn.begin();

    // Various statements
    conn.query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')").execute(function (ar1, ar1_err) {
      if (ar1_err == null) {
        conn.query("INSERT INTO Users (first_name,last_name) VALUES ('Emad','Alblueshi')").execute(function (ar2, ar2_err) {
          if (ar2_err == null) {
            // Commit the transaction
            tx.commit(function (ar3, ar3_err) {
              if (ar3_err == null) {
                console.log("Transaction succeeded");
              } else {
                console.log("Transaction failed " + ar3_err.getMessage());
              }
              // Return the connection to the pool
              conn.close();
            });
          } else {
            // Return the connection to the pool
            conn.close();
          }
        });
      } else {
        // Return the connection to the pool
        conn.close();
      }
    });
  }
});

When the database server reports the current transaction is failed (e.g the infamous current transaction is aborted, commands ignored until end of transaction block), the transaction is rollbacked and the abortHandler is called:

tx.abortHandler(function (v) {
  console.log("Transaction failed => rollbacked");
});

Simplified transaction API

When you use a pool, you can start a transaction directly on the pool.

It borrows a connection from the pool, begins the transaction and releases the connection to the pool when the transaction ends.

// Acquire a transaction and begin the transaction
pool.begin(function (res, res_err) {
  if (res_err == null) {

    // Get the transaction
    var tx = res;

    // Various statements
    tx.query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')").execute(function (ar1, ar1_err) {
      if (ar1_err == null) {
        tx.query("INSERT INTO Users (first_name,last_name) VALUES ('Emad','Alblueshi')").execute(function (ar2, ar2_err) {
          if (ar2_err == null) {
            // Commit the transaction
            // the connection will automatically return to the pool
            tx.commit(function (ar3, ar3_err) {
              if (ar3_err == null) {
                console.log("Transaction succeeded");
              } else {
                console.log("Transaction failed " + ar3_err.getMessage());
              }
            });
          }
        });
      } else {
        // No need to close connection as transaction will abort and be returned to the pool
      }
    });
  }
});
Note
this code will not close the connection because it will always be released back to the pool when the transaction