🔥 Mini Challenge
Imagine an order-processing system. You need to:
1. Create an order.
2. Add an order item.
3. Reduce inventory.
4. Commit everything if successful.
5. Roll back if a critical operation fails.
Write a transaction structure for this workflow.
Solution:
BEGIN;
INSERT INTO orders (order_id, customer_id, order_amount)
VALUES (1001, 101, 5000);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (1001, 501, 2);
UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE product_id = 501
AND stock_quantity >= 2;
COMMIT;
In a real application, you would also validate that each critical operation succeeded and handle errors according to the database/application's transaction mechanism.
If a critical operation fails:
ROLLBACK;
🎯 Key Takeaway
Remember:
• TRANSACTION → Group related operations into one unit
• COMMIT → Save changes
• ROLLBACK → Undo uncommitted changes
• SAVEPOINT → Create a rollback point
• ACID → Atomicity → Consistency → Isolation → Durability
The most important practical lesson:
«Before making large UPDATE or DELETE changes, first run the corresponding SELECT and verify exactly which rows will be affected.»
Transactions help protect data, but safe SQL also depends on careful query design, validation, permissions, and understanding your database's transaction behavior.
Double Tap ❤️ For More