Transaction — SQL Technical Interview Questions & Answers

Transaction

Transaction

  • A transaction is a group of statements that all succeed or all fail. Fee payment: subtract wallet, insert receipt. START TRANSACTION; UPDATE …; INSERT …; COMMIT; If the insert fails, ROLLBACK; — wallet should not stay subtracted.
  • ACID: Atomic (all or nothing), Consistent (rules kept), Isolated (other sessions don’t see half-work), Durable (after COMMIT, it survives a crash). InnoDB supports this. MyISAM does not.
  • autocommit is ON by default — each statement is its own transaction. START TRANSACTION (or BEGIN) turns a batch into one unit. COMMIT ends it. ROLLBACK undoes since START.
  • Trap: no transaction around two related writes. Trap: COMMIT after an error you did not notice.
  • On the example next to this theory: Transaction: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.
START TRANSACTION
     UPDATE wallet
     INSERT receipt
        │
        ├── COMMIT   → both kept
        └── ROLLBACK → both undone

Technical Interview Tip: START TRANSACTION; two writes; COMMIT; or ROLLBACK;

Code Examples for Transaction

Transaction — sample query

-- Transaction
CREATE TABLE demo (
  id INTEGER PRIMARY KEY,
  label TEXT NOT NULL,
  qty INTEGER DEFAULT 0
);
INSERT INTO demo (id, label, qty) VALUES
  (1, 'alpha', 2),
  (2, 'beta', 5);
SELECT label, qty FROM demo WHERE qty >= 2 ORDER BY qty DESC;

Transaction: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.

How to Explain Transaction to an Interviewer

What is COMMIT?

Save the transaction forever

What is ROLLBACK?

Undo since START TRANSACTION

Why InnoDB?

Transactions / ACID