== Using transactions

=== Transactions with connections

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

Or you can use the transaction API of `link:../../yardoc/VertxSqlClient/SqlConnection.html[SqlConnection]`:

[source,ruby]
----
pool.get_connection() { |res_err,res|
  if (res_err == nil)

    # Transaction must use a connection
    conn = res

    # Begin the transaction
    tx = conn.begin()

    # Various statements
    conn.query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')").execute() { |ar1_err,ar1|
      if (ar1_err == nil)
        conn.query("INSERT INTO Users (first_name,last_name) VALUES ('Emad','Alblueshi')").execute() { |ar2_err,ar2|
          if (ar2_err == nil)
            # Commit the transaction
            tx.commit() { |ar3_err,ar3|
              if (ar3_err == nil)
                puts "Transaction succeeded"
              else
                puts "Transaction failed #{ar3_err.get_message()}"
              end
              # Return the connection to the pool
              conn.close()
            }
          else
            # Return the connection to the pool
            conn.close()
          end
        }
      else
        # Return the connection to the pool
        conn.close()
      end
    }
  end
}

----

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:../../yardoc/VertxSqlClient/Transaction.html#abort_handler-instance_method[abortHandler]`
is called:

[source,ruby]
----
tx.abort_handler() { |v|
  puts "Transaction failed => rollbacked"
}

----

=== Simplified transaction API

When you use a pool, you can start a transaction directly on the pool.

It borrows a connection from the pool, begins the transaction and releases the connection to the pool when the transaction ends.

[source,ruby]
----

# Acquire a transaction and begin the transaction
pool.begin() { |res_err,res|
  if (res_err == nil)

    # Get the transaction
    tx = res

    # Various statements
    tx.query("INSERT INTO Users (first_name,last_name) VALUES ('Julien','Viet')").execute() { |ar1_err,ar1|
      if (ar1_err == nil)
        tx.query("INSERT INTO Users (first_name,last_name) VALUES ('Emad','Alblueshi')").execute() { |ar2_err,ar2|
          if (ar2_err == nil)
            # Commit the transaction
            # the connection will automatically return to the pool
            tx.commit() { |ar3_err,ar3|
              if (ar3_err == nil)
                puts "Transaction succeeded"
              else
                puts "Transaction failed #{ar3_err.get_message()}"
              end
            }
          end
        }
      else
        # No need to close connection as transaction will abort and be returned to the pool
      end
    }
  end
}

----

NOTE: this code will not close the connection because it will always be released back to the pool when the transaction