Mastering SQL Transactions: The Art of All or Nothing

Imagine you're transferring money from Account A to Account B.

1. Deduct $100 from Account A.
2. CRASH! The power goes out.
3. Add $100 to Account B.

If step 3 never happens, $100 just vanished into thin air. This is a nightmare scenario.

Enter Transactions.

What is a Transaction?

A transaction is a sequence of SQL operations that are treated as a single unit of work. It follows the ACID properties:

• Atomicity: All or nothing. Either everything succeeds, or nothing happens.
• Consistency: The database moves from one valid state to another.
• Isolation: Transactions don't interfere with each other (mostly).
• Durability: Once committed, changes are permanent.

Interactive Demo: The Bank Transfer

Let's simulate a bank transfer. We'll start a transaction, make some changes, and then decide whether to COMMIT (save) or ROLLBACK (undo).

Try it yourself!

1. Run the code above. You'll see the balances change, but then revert because of ROLLBACK.
2. Change ROLLBACK to COMMIT. Run it again. The changes will stick!

Why ROLLBACK is a lifesaver

ROLLBACK isn't just for manual undoing. It's what the database does automatically if an error occurs mid-transaction.

Isolation Levels (Advanced)

When multiple people access the database at once, things get tricky. SQL defines 4 isolation levels to handle "race conditions":

1. Read Uncommitted: Dangerous! You can see dirty data from other unfinished transactions.
2. Read Committed (Default for many DBs): You only see committed data.
3. Repeatable Read: If you read a row twice, it's guaranteed to be the same.
4. Serializable: Strict. Transactions run one after another (slowest but safest).

[!NOTE] In this browser-based playground (SQLite), we are running in a single-threaded environment, so it's hard to demonstrate race conditions. But in a real server (PostgreSQL/MySQL), these settings are critical for high-traffic apps.

Summary

• Use BEGIN TRANSACTION to start.
• Use COMMIT to save changes.
• Use ROLLBACK to undo changes if something goes wrong.
• Transactions ensure your data never ends up in a "half-broken" state.