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
var conn = res.result()
// Begin the transaction
conn.begin({ ar0 ->
if (ar0.succeeded()) {
var 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()) {
println("Transaction succeeded")
} else {
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 ->
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.
// Acquire a transaction and begin the transaction
pool.withTransaction<Any>({ client ->
client.query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')").execute().flatMap<Any>({ res ->
client.query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')").execute().map("Users inserted")
})
}).onComplete({ ar ->
// The connection was automatically return to the pool
if (ar.succeeded()) {
// Transaction was committed
var message = ar.result()
println("Transaction succeeded: ${message}")
} else {
// Transaction was rolled back
println("Transaction failed ${ar.cause().getMessage()}")
}
})