Transactions

Db.transaction(conn) do |tx| ... end runs the block between the BEGIN and the COMMIT of the database. It gives a Result[R], where R is the type of the block’s ok value.

  • The block gives ok(value): the transaction commits and the call gives ok(value).
  • The block gives error(message), or the commit fails: the transaction rolls back and the call gives that error. If the rollback fails too, its message is added after a semicolon. A transaction the database has ended already (SQLite does when it interrupts a write on a timeout) is not rolled back again.

Use the tx the block receives, not the outer connection, for the statements of the transaction.

import Db from "pkg/db/db"
import Sqlite from "pkg/db/sqlite"

def save(conn: Db.Connection, name: String) -> Result[Int]
  return Db.execute(conn, "insert into users (name) values (?)", [Db.Value.Text(name)])
end

def count(conn: Db.Connection) -> Result[Int]
  rows = try Db.query(conn, "select count(*) as n from users", [])
  case rows[0]["n"]
    Db.Value.Int(n) -> return ok(n)
    _ -> return error("no count")
  end
end

def run() -> Result[Void]
  conn = try Db.open("sqlite::memory:", [Sqlite.driver()])
  try Db.execute(conn, "create table users (name text not null unique)", [])
  saved = try Db.transaction(conn) do |tx|
    save(tx, "Ana")
  end

  puts(saved)
  failed = Db.transaction(conn) do |tx|
    try save(tx, "Bob")
    try save(tx, "Ana")
    ok()
  end

  puts(failed.ok?)
  puts(try count(conn))
  Db.close(conn)
  return ok()
end

def main() -> Void
  case run()
    ok(_) -> puts("done")
    error(message) -> IO.warn(message)
  end
end
Output
1
false
1
done

The second transaction saves “Bob” and then fails on the duplicate “Ana”, so “Bob” is rolled back too.

Rules #

  • Transactions do not nest. On PostgreSQL a second BEGIN is postgres error 25001, and on MySQL it is mysql error: a transaction is already in progress.
  • A runtime error (a halt) inside the block stops the task and leaves the transaction open. Closing the connection rolls it back, which the end of the task does.
  • On PostgreSQL a statement that fails aborts the transaction. A COMMIT after that would roll back without an error, so the package turns it into postgres error 25P02, and transaction gives that error.
  • On MySQL a statement that fails does not abort the transaction, but Db.transaction rolls back because the block returned the error. A DDL statement (create table, drop table) commits the transaction before it runs, as MySQL does.
  • SQLite starts with BEGIN IMMEDIATE, which needs write access to the database. A read-only database cannot open a transaction.
  • A block whose only result is error(...) does not infer R. Give it a type: failed: Result[Void] = error("x"), then return failed.