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());
  }
});  }