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.

Diagram

START TRANSACTION
     UPDATE wallet
     INSERT receipt
        │
        ├── COMMIT   → both kept
        └── ROLLBACK → both undone

Exam tip

START TRANSACTION; two writes; COMMIT; or ROLLBACK;

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.

Short notes

DefTransaction = all statements commit or all rollback.
RuleInnoDB. START TRANSACTION … COMMIT / ROLLBACK.
TrapMyISAM. Two writes without a transaction.

Transaction

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.

Diagram

START TRANSACTION
     UPDATE wallet
     INSERT receipt
        │
        ├── COMMIT   → both kept
        └── ROLLBACK → both undone

Exam tip

START TRANSACTION; two writes; COMMIT; or ROLLBACK;

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.

Short notes

DefTransaction = all statements commit or all rollback.
RuleInnoDB. START TRANSACTION … COMMIT / ROLLBACK.
TrapMyISAM. Two writes without a transaction.

Transaction

Transaction — sample query

Short notes

Questions

Transaction

Transaction — sample query

Short notes

Questions