What is the difference between COMMIT and ROLLBACK?

Feature	COMMIT	ROLLBACK
Purpose	Saves all changes permanently	Undoes all changes since the last COMMIT or SAVEPOINT
Effect	Makes changes visible to other users	Discards uncommitted changes
Undo Possible?	❌ No, changes are permanent	✅ Yes, changes are undone
Locks	Released after commit	Released after rollback
Transaction State	Ends the current transaction	Ends the current transaction
SAVEPOINT Support	Removes all savepoints	Can roll back to a specific SAVEPOINT
Data Integrity	Ensures durability of changes	Ensures recovery from mistakes
Used When	You're sure the transaction is correct	Something went wrong or needs to be canceled

 

COMMIT Example

INSERT INTO employees (id, name) VALUES (101, 'Alice');

COMMIT;

-- The insert is now permanent and visible to others.

 

ROLLBACK Example

INSERT INTO employees (id, name) VALUES (102, 'Bob');

ROLLBACK;

-- The insert is undone, and 'Bob' will not appear in the table.

 

🔸 Using SAVEPOINT with ROLLBACK

SAVEPOINT before_update;

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50;

ROLLBACK TO before_update;

-- Only the salary update is undone; other changes remain.

 

Summary:

• Use COMMIT when you're sure all changes are correct and should be saved permanently.
• Use ROLLBACK when you want to undo changes, either due to errors, validation failure, or user cancellation.

Let me know if you'd like a real-world use case or PL/SQL example involving both!