Reference · Database

db.transaction

db.transaction(map | fn, fn=) -> any

What it does

Runs a function with all of its database changes kept together: when the function fails, returns an error value or replies early, everything it did in the database is rolled back; otherwise it is committed and the function's value is given. An options map before the function sets isolation ("serializable", "repeatable_read" or "read_committed") and retries (0 to 20), how many times to run the function again when another transaction made the database give up on it (a deadlock, a serialization failure, a busy SQLite file); another option is an error, when it runs and in quillo check. One inside another is a savepoint.

Examples

db.open("app.db")db.exec("create table accounts (id integer primary key, balance integer not null check (balance >= 0))")db.exec("insert into accounts (balance) values (100), (20)")print(db.transaction(() => {    db.exec("update accounts set balance = balance - 30 where id = 1")?    db.exec("update accounts set balance = balance + 30 where id = 2")?    "moved"}))print(db.query("select * from accounts"))let r = db.transaction(() => {    db.exec("update accounts set balance = balance - 40 where id = 1")?    db.exec("update accounts set balance = balance - 500 where id = 2")?})print(r)print(db.query("select * from accounts"))

moved[{ id: 1, balance: 70 }, { id: 2, balance: 50 }]err("db: CHECK constraint failed: balance >= 0")[{ id: 1, balance: 70 }, { id: 2, balance: 50 }]

fn withdraw(account: int, cents: int) with db =    db.transaction({ isolation: "serializable", retries: 5 }, () => {        let balance = db.one("select balance from accounts where id = ?", account).balance        if balance < cents { return err("not enough money") }        db.exec("update accounts set balance = balance - ? where id = ?", cents, account)    })

Edge cases

• The function's value is the transaction's: an error value (made with err, returned, or passed up with ?) rolls back and is given back, so the caller sees why.
• A db.exec that fails in the middle of the function, its error value dropped, rolls the transaction back, and that error is the transaction's value: a transaction is never committed past a failure nothing took. Put ? after it to leave at once, or let _ = db.exec(...) where a failure there does not matter.
• Nested: an inner transaction that fails rolls back its own part and gives its error. Taken (let _ = db.transaction(...), ??, ?), the outer one goes on; dropped, it rolls the outer one back too. Giving the inner one an isolation is a runtime error: options belong to the outermost.
• On SQLite every transaction is serializable whatever is asked; with options it takes the write lock at once (begin immediate).
• On MySQL and MariaDB a statement that commits by itself (truncate, create table, alter, drop, ...) is refused inside it before it runs, so what ran before can still be rolled back: run it before or after the transaction. One that cannot be seen beforehand (a procedure's, execute immediate, a begin not atomic block) ends the transaction with the same error. PostgreSQL and SQLite roll their schema statements back, and take them in a transaction.
• On PostgreSQL the connection is reset as the transaction ends (reset all, discard temp, advisory locks let go, ...), before it goes back to the pool, so a set inside it does not reach the next request.
• A function that may run again (retries) should do nothing that cannot be taken back: no http.* calls inside it; global variables it changes are not rolled back.

db.open("app.db")db.exec("create table a (x integer)")print(db.transaction(() => { db.exec("insert into a values (1)"); err("changed my mind") }))print(db.query("select * from a"))print(db.transaction(() => { db.exec("insert into a values (2)"); db.exec("drop table nope"); "went on" }))print(db.query("select * from a"))print(db.transaction(() => { db.exec("insert into a values (3)"); db.exec("drop table nope")? }))print(db.query("select * from a"))print(db.transaction(() => { db.exec("insert into a values (3)"); db.exec("drop table nope") }))print(db.query("select * from a"))print(db.transaction(() => { db.transaction(() => { db.exec("insert into a values (4)"); err("inner") }); "outer went on" }))print(db.query("select * from a"))print(db.transaction({ isolation: "serializable", retries: 3 }, () => db.one("select count(*) as n from a")))print(db.transaction(() => null))

err("changed my mind")[]err("db: no such table: nope")[]err("db: no such table: nope")[]err("db: no such table: nope")[]err("inner")[]{ n: 0 }null

Errors

A runtime error inside the function stops the request, after the rollback. An option it does not take, an unknown isolation, retries outside 0 to 20, an isolation on a nested transaction, or an argument that is not a function are runtime errors. Out of retries, the transaction gives the database's error value.

db.open("app.db")db.exec("create table a (x integer)")print(db.transaction(() => db.transaction({ isolation: "serializable" }, () => 1)))

line 3: a transaction inside another cannot change its isolation: give it to the outermost one  in a function, called at main.qwe:3

print(db.transaction({ isolaton: "serializable" }, () => 1))

line 1: `db.transaction` has no option `isolaton` (isolation, retries)

print(db.transaction({ retries: 50 }, () => 1))

line 1: `db.transaction`'s retries is a number from 0 to 20, got 50

Chaining

The transaction's value goes on like any other, so a function can be the transaction:

db.open("app.db")db.exec("create table t (id integer primary key, n integer)")fn add(n: int) = db.transaction(() => db.one("insert into t (n) values (?) returning id", n))print([1, 2, 3] | map(add) | map((r) => r.id))print(db.transaction(() => db.query("select n from t")) | map((r) => r.n) | sum)

[1, 2, 3]6

See also

db.exec, db.one, db.atomic, db.error_kind, err

Edit this page on GitHub · every example's output was made by running it (tools/refcheck.mjs) · questions on Discord or r/quillo