Transactions and ACID

A transaction groups multiple statements into a single all-or-nothing unit — either every statement in it succeeds, or none of them take effect at all.

Why transactions matter: a real scenario

Imagine transferring money between two bank accounts — that requires two updates: subtract from one account, add to the other. If the database crashed after the first update but before the second, money would simply vanish. A transaction guarantees that can never happen.

Basic syntax

BEGIN TRANSACTION;

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

COMMIT;

If anything goes wrong partway through, use ROLLBACK instead of COMMIT to undo everything since BEGIN:

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Something looks wrong — undo everything
ROLLBACK;

The ACID properties

Letter	Property	Meaning
A	Atomicity	All statements in a transaction succeed, or none do.
C	Consistency	A transaction always leaves the database in a valid state, respecting all constraints.
I	Isolation	Concurrent transactions don't interfere with each other's intermediate state.
D	Durability	Once committed, changes survive even a crash immediately afterward.

Engine syntax differences

Engine	Start
PostgreSQL / SQL Server	BEGIN; / BEGIN TRANSACTION;
MySQL	START TRANSACTION;
SQLite	BEGIN TRANSACTION;

Autocommit mode

By default, most database clients run every single statement in its own implicit transaction, automatically committed right away ("autocommit"). Explicit BEGIN...COMMIT blocks are how you group multiple statements into one atomic unit instead.

Isolation levels (brief mention)

Databases offer different isolation levels (like READ COMMITTED or SERIALIZABLE) that trade off consistency guarantees against performance when many transactions run concurrently. This is an advanced topic worth knowing exists, but rarely something a beginner needs to configure manually — sensible defaults are used unless you have a specific concurrency problem to solve.

-- A ready-to-query practice database is loaded automatically before every run —
-- no CREATE TABLE needed. Available tables:
--   departments  (id, name, location)
--   employees    (id, first_name, last_name, email, department_id, job_title, salary, hire_date, manager_id)
--   customers    (id, customer_name, city, country, signup_date)
--   products     (id, product_name, category, price, stock_quantity)
--   orders       (id, customer_id, employee_id, order_date, status)
--   order_items  (id, order_id, product_id, quantity, unit_price)

SELECT * FROM employees WHERE department_id = 1;