🚀 Managing Transactions in Oracle SQL (COMMIT & ROLLBACK Explained with Real Example)

While working with Oracle Autonomous Database, I explored how transactions work and why they are critical in real-world applications like banking systems.

In many systems, multiple operations must either complete successfully together or not at all. This is where transactions ensure data consistency and reliability.

🎯 Real-World Scenario

Imagine transferring money between two accounts:

• Deduct amount from Account A

• Add amount to Account B

👉 If one step fails, the entire operation must be reversed.

This is exactly what transactions solve.

💻 Step 1: Creating the Table

CREATE TABLE accounts (
  acc_id NUMBER,
  acc_name VARCHAR2(50),
  balance NUMBER
);

🧪 Step 2: Inserting Initial Data

INSERT INTO accounts VALUES (1, 'Ali', 10000);
INSERT INTO accounts VALUES (2, 'Sara', 8000);

🔄 Step 3: Performing Transaction (Money Transfer)

UPDATE accounts SET balance = balance - 2000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 2000 WHERE acc_id = 2;

🔍 Step 4: Checking Data Before COMMIT

SELECT * FROM accounts;

At this stage, changes are visible but not permanently saved.

❌ Step 5: Rolling Back Changes

ROLLBACK;

👉 This cancels all changes and restores original data.

✅ Step 6: Committing Changes

UPDATE accounts SET balance = balance - 2000 WHERE acc_id = 1;
UPDATE accounts SET balance = balance + 2000 WHERE acc_id = 2;

COMMIT;

👉 Now changes are permanently saved

💡 Key Insights

• Transactions ensure data consistency

• COMMIT saves changes permanently

• ROLLBACK cancels changes

• Essential for financial and enterprise systems

🧠 My Learning Experience

This exercise helped me understand that SQL is not just about queries—it’s about controlling how data behaves in real systems.

Understanding transactions gave me a deeper insight into how real-world applications maintain reliability.

🚀 Conclusion

Transactions are a fundamental concept in Oracle SQL that ensure safe and reliable database operations. They are widely used in banking, e-commerce, and enterprise systems.