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