## 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(res -> {
      if (res.succeeded()) {

        // Transaction must use a connection
        SqlConnection conn = res.result();

        // Begin the transaction
        conn.begin(ar0 -> {
          if (ar0.succeeded()) {
            Transaction tx = ar0.result();
            // Various statements
            conn
              .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
              .execute(ar1 -> {
                if (ar1.succeeded()) {
                  conn
                    .query("INSERT INTO Users (first_name,last_name) VALUES ('Emad','Alblueshi')")
                    .execute(ar2 -> {
                      if (ar2.succeeded()) {
                        // Commit the transaction
                        tx.commit(ar3 -> {
                          if (ar3.succeeded()) {
                            System.out.println("Transaction succeeded");
                          } else {
                            System.out.println("Transaction failed " + ar3.cause().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();
                }
              });
          } 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 `completion` future is failed with a `TransactionRollbackException`:

    tx.completion().onFailure(err -> {
      System.out.println("Transaction failed => rollbacked");
    });

### Simplified transaction API

When you use a pool, you can call `withTransaction` to pass it a function executed within a transaction.

It borrows a connection from the pool, begins the transaction and calls the function with a client executing all operations in the scope of this transaction.

The function must return a future of an arbitrary result:

- when the future succeeds the client will commit the transaction

- when the future fails the client will rollback the transaction

After the transaction completes, the connection is returned to the pool and the overall result is provided.

    pool.withTransaction(client -> client
      .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
      .execute()
      .flatMap(res -> client
        .query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')")
        .execute()
        // Map to a message result
        .map("Users inserted"))
    ).onComplete(ar -> {
      // The connection was automatically return to the pool
      if (ar.succeeded()) {
        // Transaction was committed
        String message = ar.result();
        System.out.println("Transaction succeeded: " + message);
      } else {
        // Transaction was rolled back
        System.out.println("Transaction failed " + ar.cause().getMessage());
      }
    });