Read related data in one transaction ​

Begin a transaction when several SQL steps belong to one unit of work. Statements outside an explicit transaction autocommit, so independently submitted reads and writes are not automatically one operation.

BEGIN;
SELECT id, amount FROM balances WHERE id = 1;
UPDATE balances SET amount = amount - 5 WHERE id = 1;
SELECT id, amount FROM balances WHERE id = 1;
COMMIT;

This example assumes the application's balances table already exists. Handle any statement error before deciding to commit; use ROLLBACK when the intended operation cannot finish. Constraints remain important even when an application first checks data with a SELECT.

Connection lifetime matters. A native connection retains its transaction, and closing it rolls back uncommitted changes. Every HTTP request has a separate session, so send the complete transaction in one request. An unfinished HTTP transaction rolls back before the response is sent.

Do not infer cross-process coordination from ordinary SQL transactions. Several connections in one process can share a database file; separate processes must use independent files or connect to one server. Query-history reads take fresh history snapshots and have their own documented visibility behavior.

See transactions, deployment modes and query-log visibility.