Transaction Basics

SQL Transactions: Your Data’s Safety Net 🎪

Imagine you’re at a circus, and a tightrope walker is crossing high above the crowd. There’s a safety net below—if anything goes wrong, the performer lands safely and can try again.

SQL Transactions are your database’s safety net. They protect your data when things go wrong, making sure your database stays clean and reliable.

What Are Transactions?

Think of a transaction like moving money between two piggy banks.

Your friend wants to borrow $10 from you:

1. Take $10 out of YOUR piggy bank
2. Put $10 INTO your friend’s piggy bank

Here’s the problem: What if you take $10 out, but then the lights go off before you can give it to your friend?

• Your piggy bank has $10 less ❌
• Your friend’s piggy bank has nothing new ❌
• The $10 is… lost? 😱

A transaction makes sure BOTH steps happen together, or NEITHER happens at all.

-- A simple transaction
BEGIN;
  UPDATE piggy_bank SET money = money - 10
    WHERE owner = 'You';
  UPDATE piggy_bank SET money = money + 10
    WHERE owner = 'Friend';
COMMIT;

If anything fails, everything gets undone automatically!

BEGIN, COMMIT, ROLLBACK

These are the three magic words of transactions:

BEGIN – Start Your Safety Net

BEGIN;
-- Now you're protected!

This says: “Hey database, I’m about to do some important stuff. Watch carefully!”

COMMIT – Save Everything

COMMIT;
-- All changes are now permanent!

This says: “Everything went well! Save all my changes forever.”

ROLLBACK – Undo Everything

ROLLBACK;
-- Oops! Pretend nothing happened.

This says: “Something went wrong! Erase all my changes since BEGIN.”

graph TD
    A["BEGIN"] --> B["Do Some Work"]
    B --> C{Everything OK?}
    C -->|Yes| D["COMMIT ✅"]
    C -->|No| E["ROLLBACK ❌"]
    D --> F["Changes Saved"]
    E --> G["Changes Erased"]

Real Example:

BEGIN;
  INSERT INTO orders (item, price)
    VALUES ('Toy Car', 15);
  -- Oops! Customer canceled!
ROLLBACK;
-- The order was never saved

SAVEPOINT and Partial Rollback

Sometimes you don’t want to undo EVERYTHING—just the last few steps.

Imagine building a LEGO tower:

• You build 5 floors ✅
• You add a roof, but it looks ugly ❌
• You want to remove the roof, but keep the 5 floors!

That’s what SAVEPOINT does!

BEGIN;
  INSERT INTO tower (floor) VALUES (1);
  INSERT INTO tower (floor) VALUES (2);
  INSERT INTO tower (floor) VALUES (3);

  SAVEPOINT before_roof;  -- Mark this spot!

  INSERT INTO tower (part) VALUES ('ugly roof');

  ROLLBACK TO before_roof;  -- Go back!
  -- The roof is gone, but floors 1-3 remain

  INSERT INTO tower (part) VALUES ('pretty roof');
COMMIT;

graph TD
    A["BEGIN"] --> B["Floor 1"]
    B --> C["Floor 2"]
    C --> D["Floor 3"]
    D --> E["SAVEPOINT 📍"]
    E --> F["Ugly Roof"]
    F --> G["ROLLBACK TO SAVEPOINT"]
    G --> H["Pretty Roof"]
    H --> I["COMMIT ✅"]

Key Points:

• Create multiple savepoints with different names
• Roll back to any savepoint you want
• Everything AFTER that savepoint gets erased

ACID: The Four Superpowers of Transactions

ACID isn’t the sour stuff—it’s an acronym for four promises every transaction makes:

ACID – Atomicity

“All or Nothing”

Think of an atom—the smallest thing that can’t be split. A transaction is atomic: it either happens completely or not at all.

Story Time: 🍕

You order a pizza online:

1. Payment is taken from your card
2. Kitchen makes the pizza
3. Driver delivers it

What if step 1 succeeds, but the kitchen catches fire? Without atomicity:

• You paid money ❌
• No pizza was made ❌
• You’re hungry AND broke! 😭

With atomicity:

• If ANY step fails, EVERYTHING undoes
• Your money comes back
• It’s like the order never happened

BEGIN;
  UPDATE wallet SET balance = balance - 20;
  INSERT INTO orders (item) VALUES ('Pizza');
  -- Kitchen fire! Transaction fails!
ROLLBACK;
-- Your $20 is still safe!

ACID – Consistency

“Rules Can’t Be Broken”

Your database has rules. Consistency means transactions can’t break them.

Example Rules:

• Bank balance can’t go negative
• Every order needs a customer
• Age must be a positive number

-- Rule: balance >= 0

BEGIN;
  UPDATE bank SET balance = balance - 1000
    WHERE account = 'Alice';
  -- Alice only has $50!
  -- This BREAKS the rule!
COMMIT;
-- ERROR! Transaction fails
-- Alice still has $50

graph TD
    A["Start: Valid State ✅"] --> B["Transaction Begins"]
    B --> C["Make Changes"]
    C --> D{Rules OK?}
    D -->|Yes| E["End: Valid State ✅"]
    D -->|No| F["ROLLBACK"]
    F --> A

Consistency guarantees: Your database goes from one valid state to another valid state. Never invalid!

ACID – Isolation

“No Peeking at Unfinished Work”

When two people use the database at the same time, they shouldn’t see each other’s half-done work.

Story Time: 🎨

Imagine you’re painting a picture:

• Half the painting shows a cat
• The other half isn’t done yet

Should your friend see the half-cat? No! They should wait until you’re done.

Without Isolation:

Alice: BEGIN; UPDATE seats SET taken=1 WHERE row=5;
Bob:   SELECT * FROM seats; -- Sees row 5 as taken
Alice: ROLLBACK; -- Actually, nevermind!
Bob:   -- Thought row 5 was taken, but it's not!

With Isolation:

Bob can’t see Alice’s changes until she says COMMIT!

graph LR
    A["Alice's Transaction] --- B[Alice's Changes"]
    C["Bob's Transaction] --- D[Bob's View"]
    B -.->|"Hidden until COMMIT"| D

ACID – Durability

“Changes Stick Forever”

Once you say COMMIT, your changes survive ANYTHING:

• Power outage ⚡
• Computer crash 💥
• Server fire 🔥
• Earthquake 🌍

The database writes to permanent storage, not just memory.

Like carving into stone, not writing on a whiteboard!

BEGIN;
  INSERT INTO messages (text)
    VALUES ('Happy Birthday!');
COMMIT;
-- Even if server explodes NOW,
-- this message is saved forever!

graph TD
    A["COMMIT"] --> B["Write to Disk 💾"]
    B --> C["Confirm to User ✅"]
    C --> D["Safe Forever 🔒"]
    E["Crash? Restart?"] --> D

Error Handling Concepts

Errors happen. Good code catches them and responds properly!

The TRY-CATCH Pattern

Different databases use different syntax, but the idea is the same:

-- PostgreSQL style
BEGIN;
  -- Try dangerous stuff
  INSERT INTO users (id, name) VALUES (1, 'Alice');
  INSERT INTO users (id, name) VALUES (1, 'Bob');
  -- ERROR! ID 1 already exists!
EXCEPTION
  WHEN unique_violation THEN
    ROLLBACK;
    -- Handle the error gracefully
END;

Common Error Types

Best Practice: Always Have a Plan B

BEGIN;
  SAVEPOINT attempt1;

  -- Try inserting
  INSERT INTO products (name, price)
    VALUES ('Widget', 9.99);

EXCEPTION WHEN OTHERS THEN
  ROLLBACK TO attempt1;
  -- Log the error
  INSERT INTO error_log (msg, time)
    VALUES ('Insert failed', NOW());
END;
COMMIT;

graph TD
    A["Try Operation"] --> B{Success?}
    B -->|Yes| C["Continue ✅"]
    B -->|No| D["Catch Error"]
    D --> E["Log Problem"]
    E --> F["Rollback Changes"]
    F --> G["Try Alternative"]

Quick Summary

You Did It! 🎉

Now you understand how SQL keeps your data safe:

1. Transactions wrap your changes in a safety net
2. BEGIN/COMMIT/ROLLBACK control when to save or undo
3. SAVEPOINT lets you partially undo
4. ACID guarantees your data stays reliable
5. Error handling catches problems gracefully

Your data is no longer walking the tightrope without a net. It’s protected, reliable, and ready for anything! 🎪✨