COMMIT & ROLLBACK

n databases, transactions are sequences of operations performed as a single unit. Managing transactions ensures that data remains consistent, even when multiple operations are performed. Two key commands for controlling transactions are COMMIT and ROLLBACK.

COMMIT

COMMIT is used to save all changes made during the current transaction permanently to the database. Once a COMMIT is executed, the changes cannot be undone.

Example Usage:
If you insert a new record into a table and execute COMMIT, the new record is permanently saved.

Syntax:
COMMIT;

Key Points:

• Makes all changes permanent.
• Ends the current transaction.
• Cannot be undone.

ROLLBACK

ROLLBACK is used to undo changes made during the current transaction. It restores the database to the state it was in before the transaction started.

Example Usage:
If you accidentally update multiple records incorrectly, executing ROLLBACK will revert all those updates.

Syntax:
ROLLBACK;

Key Points:

• Undoes changes in the current transaction.
• Useful for correcting mistakes.
• Does not affect previous committed transactions.

Transaction Example

1. Start a transaction.
2. Insert a new record.
3. Update some existing records.
4. Decide whether to save changes or discard them:
   • If everything is correct, execute COMMIT.
   • If there is an error, execute ROLLBACK.

Best Practices

• Always use transactions for critical operations.
• COMMIT only after verifying that all operations are correct.
• Use ROLLBACK to safely handle errors or incorrect updates.