29️⃣ COMMIT vs SAVEPOINT vs ROLLBACK
Remember:
• COMMIT → Save the transaction
• ROLLBACK → Undo uncommitted transaction changes
• SAVEPOINT → Mark a point inside a transaction
• ROLLBACK TO SAVEPOINT → Undo changes after that point
Example:
BEGIN;
UPDATE customers
SET status = 'Active'
WHERE customer_id = 101;
SAVEPOINT s1;
UPDATE customers
SET status = 'Inactive'
WHERE customer_id = 102;
ROLLBACK TO SAVEPOINT s1;
COMMIT;
The first update can remain while the second update is rolled back, subject to the database's transaction semantics.
30️⃣ Common Mistakes
❌ Mistake 1: Forgetting COMMIT — You may make changes but not persist them as intended.
❌ Mistake 2: Assuming ROLLBACK always works — If the changes have already been committed, a normal rollback cannot undo them.
❌ Mistake 3: Running UPDATE without checking the WHERE condition — Dangerous:
UPDATE customers SET status = 'Inactive';
Safer workflow:
SELECT * FROM customers WHERE ...;
UPDATE customers SET status = 'Inactive' WHERE ...;
❌ Mistake 4: Assuming transaction behavior is identical everywhere — Database systems differ in areas such as:
- Autocommit
- DDL transactions
- Isolation
- Locking
- Savepoints
- Error handling
🎯 Interview Questions
•
Q1. What is a transaction? A transaction is a logical unit of one or more database operations that are handled together.
•
Q2. What does COMMIT do? It commits the transaction's changes.
•
Q3. What does ROLLBACK do? It reverses uncommitted changes in the transaction.
•
Q4. What is SAVEPOINT? A savepoint marks a point inside a transaction to which you can potentially roll back without undoing the entire transaction.
•
Q5. What does ACID stand for?
A → Atomicity,
C → Consistency,
I → Isolation,
D → Durability
•
Q6. What is atomicity? It treats the transaction as a logical unit so that its operations are committed together or rolled back as appropriate.
•
Q7. What is isolation? It controls how concurrent transactions interact and what changes they can see.
•
Q8. What is durability? Committed changes are intended to survive failures according to the database's durability mechanisms.
•
Q9. What is autocommit? A mode where individual statements may be committed automatically.
•
Q10. What is a dirty read? Reading data changed by another transaction before that transaction commits.
🧠 Practice Questions
Practice 1 Write a transaction that updates a customer's status and commits it.
BEGIN;
UPDATE customers SET status = 'Active' WHERE customer_id = 101;
COMMIT;
Practice 2 Update a customer's status but roll back the change.
BEGIN;
UPDATE customers SET status = 'Inactive' WHERE customer_id = 101;
ROLLBACK;
Practice 3 Create a savepoint after the first update.
BEGIN;
UPDATE customers SET status = 'Active' WHERE customer_id = 101;
SAVEPOINT customer_update;
UPDATE customers SET status = 'Inactive' WHERE customer_id = 102;
ROLLBACK TO SAVEPOINT customer_update;
COMMIT;
Practice 4 Before executing this UPDATE:
UPDATE orders SET status = 'Cancelled' WHERE order_date < DATE '2025-01-01';
write a SELECT that lets you inspect the affected records.
SELECT * FROM orders WHERE order_date < DATE '2025-01-01';
Practice 5 Explain what happens here:
BEGIN;
UPDATE accounts SET balance = balance - 5000 WHERE account_id = 101;
UPDATE accounts SET balance = balance + 5000 WHERE account_id = 202;
ROLLBACK;