SQL Transactions (ACID, Commit, Rollback, Savepoint)

🔹 What is a Transaction?

A transaction is a sequence of one or more SQL operations executed as a single unit of work. Either all succeed (commit) or all fail (rollback), ensuring data integrity.

🔹 Properties of Transactions (ACID)

• Atomicity — All steps succeed or none.
• Consistency — Database moves from one valid state to another.
• Isolation — Transactions don’t interfere with each other.
• Durability — Committed changes survive crashes.

1️⃣ Starting a Transaction

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
-- Transfer happens only if both updates succeed

2️⃣ Rolling Back a Transaction

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
-- Oops! Something went wrong
ROLLBACK;

3️⃣ Savepoints (Partial Rollbacks)

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT step1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
ROLLBACK TO step1;
COMMIT;

✅ Why Transactions Matter

• Essential for banking, e‑commerce, and financial apps.
• Prevents half-completed operations.
• Protects against system crashes or errors.