A transfer is one transaction¶
The shop pays its suppliers from a ledger: an accounts table and a
transfers table in the one PostgreSQL database of
chapter 16. A transfer of 100 from one account
to another changes two rows and adds one, and the three changes hold
together or not at all. target cannot say that: it rewrites a table whole,
and three writes are three commits. This page is the statements that change
rows in place, and the scope that makes several of them one transaction
(HQL-0051).
Every program here is checked, not run: the book's tests connect to nothing. The two records are declared once:
type Account { id : Int, owner : String, balance : Decimal }
type Transfer { id : Int, from_id : Int, to_id : Int, amount : Decimal }
The configuration names each table's record, element = "Account", beside
its connection:
[endpoint.accounts]
kind = "postgres"
dsn_env = "SHOP_DSN"
table = "accounts"
order = ["id"]
element = "Account"
[endpoint.transfers]
kind = "postgres"
dsn_env = "SHOP_DSN"
table = "transfers"
order = ["id"]
element = "Transfer"
Changing rows in place¶
configured("accounts") is the table itself as a value, where
source("accounts") is its rows. Three statements change what a table
holds. update selects rows with the filter of every pipeline and set
gives the fields that change; its value is the number of rows changed:
accounts = configured("accounts")
update accounts
| filter(a => a.id == 1)
| set(a => {balance: a.balance - 100.00})
Int
The lambdas are not run row by row. The statement runs on the database as
one UPDATE … SET … WHERE …, so a lambda holds only what translates: a
field of the row, a literal, a value bound outside the lambda, comparisons,
and, or, not, arithmetic, contains of a bound collection, and
is_none or is_some of a field. Anything else is a check error that says
to compute it in HQL and bind it. insert adds a row or a collection of
them, and delete removes the rows it selects:
transfers = configured("transfers")
added = insert transfers Transfer(id = 1, from_id = 1, to_id = 2, amount = 100.00)
gone = delete transfers | filter(t => t.amount == 0.00)
[added, gone]
List<Int>
A filter that matches no row is no failure: the statement changes nothing
and its value is 0. A program that needs the row to exist reads the
count. A statement outside a transaction is its own transaction, committed
when it ends, as target of a table is. The checker refuses a statement
over an endpoint that cannot take it: a Kafka topic is not Updatable, a
CSV file is not Deletable. A sqlite or postgres table is all three, and a
spark table too, where the table's format allows it.
Two updates and an insert, or none¶
A transaction scope runs its block as one unit over its participants, a set of endpoints in one database, and commits when the block ends:
accounts = configured("accounts")
transfers = configured("transfers")
ledger = {accounts, transfers}
with transaction(isolation = Serializable) tx on ledger {
sender = source(accounts) | filter(a => a.id == 1) | one
tx.rollback("insufficient balance") given sender.balance < 100.00
debited = update accounts | filter(a => a.id == 1) | set(a => {balance: a.balance - 100.00})
credited = update accounts | filter(a => a.id == 2) | set(a => {balance: a.balance + 100.00})
tx.rollback("no such account") given debited != 1 or credited != 1
insert transfers Transfer(id = 1, from_id = 1, to_id = 2, amount = 100.00)
}
Result<Int, TransactionFailure>
Inside the block every read and write of a participant runs on the scope's
own connection: a source after an update sees the update, and a sink
commits no row of its own. one gives the one element of a sequence of
exactly one, and fails otherwise. Each update is bound so that its count
is read: a credit to an account that does not exist changes no row, and
without the check the scope would commit the debit alone.
isolation = Serializable is what keeps the balance check true when two
transfers run at once. Under it, two scopes that both read a balance of
150 and both debit 100 cannot both commit: the database refuses one of
them as a conflict. PostgreSQL's default level, ReadCommitted, does not
refuse it: the second debit waits for the first, then subtracts from the
committed 50, and the balance ends at -50. SQLite has no other level.
The handle tx ends the attempt early:
tx.rollback(reason) undoes it and gives the reason back,
tx.commit(value) commits before the end, and tx.retry() runs the block
again. Each is written as a statement of the block or as an arm of a
given … otherwise or a match that is one, and tx goes nowhere else:
not into a lambda, not into a function.
The scope's value is a Result: ok with the block's final value, here the
rows the insert added, and err with a TransactionFailure saying why
the scope did not commit. A rollback by the program's own rule, a
constraint the database enforced and a conflict that outlasted the attempts
are all data, matched like any other:
accounts = configured("accounts")
transfers = configured("transfers")
outcome =
with transaction(attempts = 3, isolation = Serializable) tx on {accounts, transfers} {
sender = source(accounts) | filter(a => a.id == 1) | one
tx.rollback("insufficient balance") given sender.balance < 100.00
debited = update accounts | filter(a => a.id == 1) | set(a => {balance: a.balance - 100.00})
credited = update accounts | filter(a => a.id == 2) | set(a => {balance: a.balance + 100.00})
tx.rollback("no such account") given debited != 1 or credited != 1
insert transfers Transfer(id = 1, from_id = 1, to_id = 2, amount = 100.00)
}
match outcome {
ok(n) => "transferred",
err(f) => f.kind + ": " + f.reason,
}
String
A scope is observed when it is bound, as outcome is here, or when it is
the program's last statement, whose value is the program's result. A scope
written as a statement before the last one has a value nothing reads, and
the checker warns, kind effect: this transaction's `Result` is neither
bound, nor the program's result, nor matched: its failure would be observed
by nothing. The program still runs; a rollback in that scope goes
unnoticed.
attempts = 3 runs the block again, on a fresh transaction, when the
database refuses an attempt because of a concurrent one: in PostgreSQL a
serialization failure under isolation = Serializable or a deadlock, in
SQLite a database file another transaction is writing. The attempt is
retried with no delay, whether a statement or the commit was refused, and
after the last one the scope is err with kind = "conflict". A retried
attempt reads the balance again, so a transfer that lost the race to
another one meets the balance that one left, and rolls back by its own
rule where it no longer holds. HQL's own tests run that race over SQLite:
two of these transfers of 100 start together from a balance of 150, both
pass the balance check, one commits, and the other rolls back on the
balance after a retry or ends conflict, with no half of it left. A
constraint violation is never retried, since the same rows would be
refused again.
A retried attempt undoes what it wrote to a participant and nothing else. A
write to an endpoint outside the set, a target or sink to a file, a
channel or a topic, or a log call, runs on every attempt and is rolled back
by none. Where attempts is written as a number above 1, the checker warns
of each such write, kind effect, naming the endpoint:
`sink` to `audit` runs on every attempt of the scope and is not rolled
back. A write wanted once per outcome is written after the scope, from
its value; a log line per attempt is often what is wanted, and the warning
says what it is.
Any other failure inside the block, a division by zero, a lost connection, rolls the attempt back before it is reported, so a program that stops leaves no half-made transfer.
The ledger case
of hql-demo runs this transfer against PostgreSQL 17: twice it commits,
and the third time it rolls back on the sender's balance.
What the kind promises¶
The statements read the same over every table; what a scope guarantees is the kind's.
| Kind | One scope holds | insert, update, delete |
|---|---|---|
postgres |
every table of one database, at ReadCommitted unless the scope names RepeatableRead or Serializable |
all three, as one statement each |
sqlite |
every table of one database file, at Serializable, the one level it offers |
all three; a Decimal computed on the server is a float |
spark, a Delta table |
one table, and at most one statement that changes it: a Delta table commits one statement of itself | all three where the table's format allows it |
A set of two kinds, or two databases, is refused where the program is checked: no scope spans them, and nothing runs weaker than it was asked to. Two Delta tables that must change together are two commits, and the scope has no form for it; coordinating them is a later record.
What is next¶
Chapter 20 turns from the transactions to what they
leave behind, and its merge page is the analytical
counterpart of update: a table kept up to date by key, whole.