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 givesok(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
endOutput
1
false
1
doneThe 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
BEGINispostgres error 25001, and on MySQL it ismysql 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
COMMITafter that would roll back without an error, so the package turns it intopostgres error 25P02, andtransactiongives that error. - On MySQL a statement that fails does not abort the transaction, but
Db.transactionrolls 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 inferR. Give it a type:failed: Result[Void] = error("x"), then returnfailed.