Database Transactions

Overview

A transaction groups several SQL statements so they either all succeed or all fail together. This matters when one logical action touches multiple rows, like moving money between two accounts. If something fails partway through, the database can roll back and leave your data as if nothing happened.

Syntax / Usage

Wrap statements between BEGIN and COMMIT. If an error occurs, use ROLLBACK to undo everything since BEGIN.

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Examples

Rolling back when a check fails keeps the data consistent:

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Application detects insufficient funds
ROLLBACK;  -- no change is saved

Both inserts commit together, so you never get a half-finished order:

BEGIN;
INSERT INTO orders (id, user_id, total) VALUES (10, 1, 59.99);
INSERT INTO order_items (order_id, product_id, qty) VALUES (10, 5, 2);
COMMIT;

Common Mistakes

• Forgetting to COMMIT, leaving the transaction open and locking rows
• Treating multiple related updates as independent statements with no transaction
• Doing slow work (network calls, user input) while a transaction is open, holding locks too long
• Assuming a rolled-back transaction still reserved auto-generated ids (sequences usually keep advancing)
• Catching an error but not issuing ROLLBACK, leaving the connection in a failed state

See Also

databases-acid databases-relational-basics databases-indexes