A database transaction is a logical unit of work: a sequence of operations on objects that ends with exactly one commit or one abort. It lets an application treat concurrency and failure as if they were absent while the database system handles them underneath. section 5.2 section 5.3
A transaction’s guarantees are commonly summarized by ACID:
- Atomicity makes execution observably equivalent to either completing entirely or not occurring. section 5.3
- Consistency transitions the database between states that satisfy application-defined rules. section 5.3
- Isolation prevents concurrent transactions from interfering in a way that produces incorrect results. section 5.3
- Durability makes committed effects permanent. section 5.3
Money transfer
A transfer debits one account and credits another inside BEGIN and COMMIT. Two concurrent transfers therefore produce the same result as some serial order, while a failure between the writes does not leave a partial transfer. section 5.2 section 5.2.1 section 5.2.2
Correctness is expressed through serializability: concurrent transactions are acceptable when their combined effects equal executing one transaction before the other. Consistency rules remain application-dependent; a CHECK constraint can, for example, prohibit negative balances. section 5.3.1 section 5.3
Listing 5.1 Simplified money transfer
app.post('/transfer', async (req, res) => {
const { source, target, amount } = req.body;
const query = ` #1
BEGIN;
UPDATE accounts SET balance = balance - $1 WHERE id = $2;
UPDATE accounts SET balance = balance + $1 WHERE id = $3;
COMMIT;`;
await db.none(query, [amount, source, target]);
res.status(200).end();
});
Inspect the compact SQL-backed transfer handler to see how a transaction hides concurrency and failure handling behind a simple application operation. section 5.2
