Transactions

Introduction

A transaction is a group of SQL statements that either all succeed or all fail — atomically. Banks would collapse without them.

Understanding the topic

Core concepts to understand:

• BEGIN; ...; COMMIT; — successful group.
• BEGIN; ...; ROLLBACK; — undo group.
• Default in many DBs: each statement is its own transaction.
• Use transactions whenever multiple writes must be consistent.
• Long transactions are an anti-pattern — keep them short.

Syntax reference

Visual workflow / architecture:

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;            -- both updates atomic, durable

if anything fails between → ROLLBACK; both undone.

Real-world use

Bank transfers, e-commerce checkouts, multi-row updates — every meaningful write path is wrapped in a transaction.

Best practices

• Wrap multi-row writes in transactions.
• Keep transactions short — release locks fast.
• Don't make external API calls inside a transaction.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Why do we need transactions?
• Q2. ROLLBACK — what does it undo?
• Q3. Auto-commit mode.
• Q4. Cost of long transactions.
• Q5. External calls inside transactions — why bad?