Commit related inserts atomically ​

Use a transaction when several changes must become one unit of commit or rollback. Outside an explicit transaction, statements autocommit. Choose the request interface before designing the operation because HTTP and native connections retain different session state.

BEGIN;
INSERT INTO accounts(id, balance) VALUES (1, 100);
INSERT INTO audit_log(account_id, action) VALUES (1, 'created');
COMMIT;

Keep the transaction together ​

A native connection retains an open transaction until it commits, rolls back or closes. Closing the connection rolls back uncommitted changes. Separate invocations of the command-line client are separate connections and cannot continue each other's transaction.

Each HTTP request creates its own session. Put BEGIN, every related SQL statement and COMMIT in the same request. An unfinished transaction is rolled back before the response. Inline INSERT ... FORMAT consumes the rest of its request as data, so do not append a COMMIT after that input. For an HTTP workflow needing several formatted loads, use staging data and a later SQL transaction for the final changes.

Test error handling as well as success. When a client loses a write response, reconcile using application identifiers before retrying; transport failure alone does not prove rollback.

See transactions, INSERT input and HTTP sessions.