Transactions & Concurrency in SQL · SQL & Databases

A transaction groups several changes into one all-or-nothing unit. It's how you keep data correct when steps can fail midway.

A transaction bundles multiple statements so they succeed or fail together. The classic example is a bank transfer: debit one account and credit another — both must happen, or neither. You control transactions with BEGIN, COMMIT (make permanent) and ROLLBACK (undo).

BEGIN, COMMIT, ROLLBACK

Wrap related changes in BEGIN ... COMMIT. If anything goes wrong before COMMIT, ROLLBACK undoes the whole block. This delivers the ACID guarantees — atomicity, consistency, isolation, durability — in practice.

An atomic money transfer

BEGIN;
  UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
  UPDATE accounts SET balance = balance + 1000 WHERE id = 2;
  -- if both succeed:
COMMIT;
  -- if something failed instead:
-- ROLLBACK;

Savepoint: partial rollback

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  SAVEPOINT after_debit;
  UPDATE accounts SET balance = balance + 100 WHERE id = 99;  -- bad id
  ROLLBACK TO after_debit;   -- undo just the second step
COMMIT;

⚡ The edge

Wrap any multi-step change that must be all-or-nothing in a transaction. COMMIT makes it durable; ROLLBACK undoes everything since BEGIN (or back to a SAVEPOINT).
Isolation levels trade safety for concurrency: Read Uncommitted < Read Committed < Repeatable Read < Serializable. Higher levels prevent more anomalies (dirty/non-repeatable/phantom reads) but allow less parallelism.

Worked example

Why must a bank transfer run inside a transaction?

A transfer is two updates: debit one account, credit another.
If the system crashes after the debit but before the credit, money would vanish.
A transaction makes both updates atomic — they commit together or roll back together — so the books always balance.

Answer: So the debit and credit are atomic: both commit together or neither does, preventing lost money.

Worked example

What's the effect of raising the isolation level to Serializable?

Serializable is the strictest level; transactions behave as if run one after another.
It prevents dirty, non-repeatable and phantom reads entirely.
The cost is reduced concurrency — more locking/aborts — so throughput drops.

Answer: It eliminates all read anomalies by serialising transactions, at the cost of lower concurrency.

⚠ Watch out

Forgetting to COMMIT leaves changes invisible to others (and possibly locked); forgetting to handle errors can leave a transaction open.
Long transactions hold locks, blocking others — keep them short.
Many tools run in autocommit mode (each statement commits immediately) — know whether you're in a transaction.