## 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