== Using transactions

=== Transactions with connections

You can execute transaction using SQL `BEGIN`/`COMMIT`/`ROLLBACK`, if you do so you must use
a `link:../../jsdoc/module-vertx-sql-client-js_sql_connection-SqlConnection.html[SqlConnection]` and manage it yourself.

Or you can use the transaction API of `link:../../jsdoc/module-vertx-sql-client-js_sql_connection-SqlConnection.html[SqlConnection]`:

[source,js]
----
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 `link:../../jsdoc/module-vertx-sql-client-js_transaction-Transaction.html#abortHandler[abortHandler]`
is called:

[source,js]
----
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.

[source,js]
----

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