== Using transactions

=== Transactions with connections

You can execute transaction using SQL `BEGIN`/`COMMIT`/`ROLLBACK`, if you do so you must use
a `link:../../apidocs/io/vertx/sqlclient/SqlConnection.html[SqlConnection]` and manage it yourself.

Or you can use the transaction API of `link:../../apidocs/io/vertx/sqlclient/SqlConnection.html[SqlConnection]`:

[source,java]
----
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 `link:../../apidocs/io/vertx/sqlclient/Transaction.html#completion--[completion]` future
is failed with a `link:../../apidocs/io/vertx/sqlclient/TransactionRollbackException.html[TransactionRollbackException]`:

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

=== Simplified transaction API

When you use a pool, you can call `link:../../apidocs/io/vertx/sqlclient/Pool.html#withTransaction-java.util.function.Function-io.vertx.core.Handler-[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.

[source,java]
----
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());
  }
});
----