TopolearnACID is what lets you reason about money transfers, stock counts and any multi-step change safely.
A transaction groups statements so they succeed or fail together. The classic example is a transfer: debit one account and credit another. If the system crashes between the two, you must not lose or create money.
ACID names the guarantees:
- Atomicity: all statements commit, or none do. On error or
ROLLBACK, every change is undone. - Consistency: a transaction takes the database from one valid state to another — every constraint holds at commit. (This is the database's part; your business logic must also be correct.)
- Isolation: concurrent transactions don't see each other's half-done work. How strictly this holds depends on the isolation level.
- Durability: once
COMMITreturns, the change survives a crash, because it was written to a durable log first.
Keep transactions short. A transaction that stays open while waiting for a user or an HTTP call holds locks and old row versions, which blocks other work and bloats storage.
Every statement runs in a transaction even if you don't write BEGIN: in autocommit mode each statement is its own transaction. Explicit transactions are for when several statements must stand or fall together.
Example
CREATE TABLE account (id int PRIMARY KEY, balance numeric NOT NULL CHECK (balance >= 0));
INSERT INTO account VALUES (1, 100), (2, 50);
BEGIN;
UPDATE account SET balance = balance + 80 WHERE id = 2;
UPDATE account SET balance = balance - 80 WHERE id = 1;
COMMIT; -- both changes become visible and durable together
BEGIN;
UPDATE account SET balance = balance + 80 WHERE id = 2;
UPDATE account SET balance = balance - 80 WHERE id = 1;
-- ERROR: violates check constraint (balance would be -60)
ROLLBACK; -- the credit to account 2 is undone too
SELECT * FROM account ORDER BY id; -- 1 | 20 2 | 130Remember
- A transaction is all-or-nothing
- ACID = atomicity, consistency, isolation, durability
- Keep transactions short; never wait on users or the network inside one
Watch out for
- Doing related writes in separate autocommitted statements and hoping nothing fails in between.