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