Transactions — Atomic, Safe Data Changes

Transactions

A transaction is a group of SQL statements that execute as a single unit. Either all succeed (commit) or all fail and the database returns to its previous state (rollback). Transactions enforce the ACID properties: Atomicity, Consistency, Isolation, Durability.

-- Transfer a student from one course to another atomically
BEGIN;

  DELETE FROM enrolments
  WHERE student_id = 1 AND course_id = 2;

  INSERT INTO enrolments (id, student_id, course_id, enrolled_at)
  VALUES (99, 1, 3, CURRENT_DATE);

COMMIT;

-- If something goes wrong, undo everything
BEGIN;

  UPDATE enrolments SET score = 95 WHERE id = 10;
  -- Oops, wrong row — let's undo
ROLLBACK;

ACID Properties

• Atomicity — all statements in the transaction succeed or none do
• Consistency — the database moves from one valid state to another
• Isolation — concurrent transactions do not interfere with each other
• Durability — committed changes survive system crashes