## 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.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 `abortHandler` is called:

    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.

    # 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