SQL Transactions – BEGIN, COMMIT, ROLLBACK (with Real Table Example)

When you want your database changes to be safe, reversible, and controlled, you use a transaction.

---

Imagine This Table: accounts

account_id	name	balance
1	Alice	500
2	Bob	300

Now let’s say we want to transfer $100 from Alice to Bob.

To do that, we need two updates:

1. Subtract 100 from Alice
2. Add 100 to Bob

But what if something fails after step 1?
We can’t leave Alice with less money if Bob didn’t get his share. So we wrap the operations in a transaction.

---

Step-by-Step with BEGIN, COMMIT, ROLLBACK

Step 1: Start the transaction

BEGIN;

This tells SQL: “Hold off on saving anything — I’m doing a set of operations as one unit.”

Step 2: Subtract from Alice

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

Temporary state:

account_id	name	balance
1	Alice	400 ← TEMP
2	Bob	300

Step 3: Add to Bob

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

Now the temporary state looks like this:

account_id	name	balance
1	Alice	400
2	Bob	400 ← TEMP

Step 4: COMMIT the changes

COMMIT;

Now both updates are saved permanently.
Your database officially says:

• Alice has 400
• Bob has 400

---

But What If Something Fails?

Let’s say Bob’s update fails (maybe the account doesn’t exist). Without a transaction, Alice loses $100!

But with a transaction, we can ROLLBACK.

ROLLBACK in Action

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Oops! The next line fails
UPDATE accounts SET balance = balance + 100 WHERE account_id = 99;

ROLLBACK;

Final Result (Nothing Changed):

account_id	name	balance
1	Alice	500
2	Bob	300

All changes were cancelled. Nobody lost any money.

---

Recap

Keyword	What It Does	Use It When…
BEGIN	Starts a transaction	You want to group several commands together
COMMIT	Saves all changes made during the transaction	You’re ready to make it permanent
ROLLBACK	Undoes all changes since BEGIN	Something went wrong or needs to be canceled

---

Real-Life Scenarios for Transactions

• Bank transfers
• Stock updates in e-commerce
• Inventory adjustments in warehouses
• Multi-step forms where all data must be saved together

---

Final Thoughts

Use BEGIN, COMMIT, and ROLLBACK to wrap your SQL operations in a safe container. Either everything works together, or nothing changes — that’s how you protect your data from weird bugs and partial updates.