Rollback¶

By default an ODBC connection is in autocommit mode: every statement is its own transaction, committed immediately. Safe-ish for ad-hoc queries, wrong for anything involving multiple related writes where the whole set must succeed or roll back together.

Connection.begin() turns autocommit off. Until commit() or rollback(), every statement is part of one transaction.

The three methods¶

fun ref begin():    (TxBegun      | TxBeginError)
fun ref commit():   (TxCommitted  | TxCommitError)
fun ref rollback(): (TxRolledBack | TxRollbackError)

Each has a success primitive and an error branch. Matching on the primitive makes intent visible — | TxCommitted => is hard to misread.

After commit() or rollback() the connection returns to autocommit. Call begin() again for the next transaction.

A happy path, and a rollback¶

The sample commits two rows, then starts a second transaction that hits a constraint violation and rolls back cleanly.

use "lib:odbc"
use "odbc"

actor Main
  new create(env: Env) =>
    let dsn_name =
      try env.args(1)?
      else "psqlred"
      end

    match Odbc.connect(Dsn("DSN=" + dsn_name))
    | let conn: Connection =>
      conn.exec("DROP TABLE IF EXISTS tut_tx")
      match \exhaustive\ conn.exec(
        "CREATE TABLE tut_tx "
          + "(id INTEGER PRIMARY KEY, label VARCHAR(32))")
      | let _: (USize | NoRowCount) => None
      | let e: ExecError =>
        env.err.print("create: " + e.string()); conn.close(); return
      end

      // Happy path: begin, exec, commit.
      match \exhaustive\ conn.begin()
      | TxBegun => None
      | let e: TxBeginError =>
        env.err.print("begin: " + e.string()); conn.close(); return
      end

      conn.exec("INSERT INTO tut_tx VALUES (1, 'alpha')")
      conn.exec("INSERT INTO tut_tx VALUES (2, 'bravo')")

      match \exhaustive\ conn.commit()
      | TxCommitted => env.out.print("committed 2 rows")
      | let e: TxCommitError =>
        env.err.print("commit: " + e.string())
      end

      // Rollback path: a constraint violation should leave no trace.
      match \exhaustive\ conn.begin()
      | TxBegun => None
      | let e: TxBeginError =>
        env.err.print("begin2: " + e.string()); conn.close(); return
      end

      conn.exec("INSERT INTO tut_tx VALUES (3, 'charlie')")
      match \exhaustive\ conn.exec(
        "INSERT INTO tut_tx VALUES (1, 'duplicate')")
      | let _: (USize | NoRowCount) => None
      | let e: ExecError =>
        env.err.print("insert failed: " + e.string())
        match \exhaustive\ conn.rollback()
        | TxRolledBack => env.out.print("rolled back cleanly")
        | let r: TxRollbackError =>
          env.err.print("rollback: " + r.string())
        end
      end

      // Confirm: table has only the two committed rows.
      match \exhaustive\ conn.query(
        "SELECT COUNT(*) FROM tut_tx")
      | let cursor: Cursor =>
        match cursor.fetch()
        | let row: Row =>
          try
            match \exhaustive\ row.int(ColIndex(1))?
            | let n: I64 =>
              env.out.print("rows committed: " + n.string())
            | SqlNull => None
            end
          end
        end
        cursor.close()
      | let e: ExecError =>
        env.err.print("count: " + e.string())
      end

      conn.exec("DROP TABLE IF EXISTS tut_tx")
      conn.close()

    | let e: ConnectError =>
      env.err.print("connect: " + e.string())
    end

./build/07-transactions

committed 2 rows
insert failed: ExecError: constraint violation [23505]
rolled back cleanly
rows committed: 2

23505 is Postgres’s “unique constraint violation”. The library classifies that into the ConstraintViolation kind automatically.

Rollback-on-error is the idiom¶

conn.begin()
conn.exec(first_statement)
match conn.exec(second_statement)
| let e: ExecError =>
  conn.rollback()  // server may have already rolled back
  return
end
conn.commit()

Once any statement in a transaction errors, assume the transaction is dead and roll back explicitly. Most drivers put the session into an “aborted” state where every subsequent statement errors until rollback — the library surfaces this rather than silently clearing state.

Close auto-rolls-back¶

Connection.close() fires a rollback before freeing handles. You don’t have to remember rollback in cleanup — but an explicit rollback when you know you’re aborting is still good practice.

What’s next¶

The three error unions carry more detail than “it failed” — especially TxCommitError, which distinguishes “the server rolled it back” from “we don’t know what happened”. Transaction Errors covers that.