Transactions and ACID

A bank transfer has two steps: take money from one account and add it to another. What if the server switches off after the first step? The money simply vanishes. A transaction prevents that.

All or nothing

BEGIN;
UPDATE accounts SET balance = balance - 500000 WHERE id = 1;
UPDATE accounts SET balance = balance + 500000 WHERE id = 2;
COMMIT;

• BEGIN — the transaction starts;
• COMMIT — keep everything;
• ROLLBACK — undo everything, as if nothing had happened.

Other users do not see the changes until COMMIT. If an error occurs or the connection drops, the database cancels the transaction itself.

A safety belt for yourself

A transaction is a seat belt before a risky command:

BEGIN;
DELETE FROM dori WHERE yaroqlilik_muddati < '2026-01-01';
SELECT count(*) FROM dori;      -- check the result
ROLLBACK;                       -- or COMMIT if everything is right

Try it in free play: load the ready-made database, delete every row after BEGIN, look with SELECT, then ROLLBACK — the rows are back.

ACID

A good database guarantees four properties for transactions:

Problems with working in parallel

What can happen when many people work at once:

• dirty read — seeing someone else’s changes that are not saved yet;
• non-repeatable read — reading a row twice in one transaction and getting different values;
• phantoms — new rows appear when you count again.

How well a database guards against these is called the isolation level.

PostgreSQL works at READ COMMITTED by default: each command sees only saved data. If needed: BEGIN ISOLATION LEVEL SERIALIZABLE;

Autocommit

Without BEGIN, every command is a transaction of its own and is saved immediately (autocommit). That is why a DELETE run without BEGIN cannot be undone with ROLLBACK.

SAVEPOINT — a checkpoint

BEGIN;
INSERT INTO mijoz (ism) VALUES ('Aziz');
SAVEPOINT birinchi;
INSERT INTO mijoz (ism) VALUES ('Xato');
ROLLBACK TO SAVEPOINT birinchi;   -- only the second insert is undone
COMMIT;

Remember

• BEGIN … COMMIT keeps everything; ROLLBACK undoes everything.
• ACID — atomicity, consistency, isolation, durability.
• Write BEGIN before a risky change.