Commit and Rollback Mechanics

In this lesson, you will learn how to finalize or abandon your data changes to ensure your database remains consistent even when things go wrong.

The Transaction Safety Net

When you modify data using INSERT, UPDATE, or DELETE, those changes are often held in a "pending" state by the database. Think of this like editing a document: you have made changes, but you haven't clicked "Save" yet.

In SQL, the two primary tools to control this lifecycle are COMMIT and ROLLBACK.

• COMMIT: This is your "Save" button. When you execute a COMMIT, you tell the database to permanently etch your pending changes into the permanent storage. Once committed, these changes are visible to other users and cannot be easily undone.
• ROLLBACK: This is your "Undo" button. If you realize you made a mistake during your transaction, or if your application encounters an error (like a lost network connection or a constraint violation), you can issue a ROLLBACK. This wipes away all pending changes since the transaction began, returning your tables to the exact state they were in before you started.

Seeing it in Action

Imagine you are transferring money between two accounts. You need to subtract from one and add to the other. If the system crashes after the subtraction but before the addition, money vanishes! Transactions prevent this.

-- Start of the transaction
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;

-- If both operations succeeded, make them permanent:
COMMIT;

-- If an error occurred (e.g., account #2 doesn't exist):
-- ROLLBACK;

By using these commands, you protect your database from "partial" updates. You ensure that either the entire sequence of commands succeeds, or nothing happens at all, keeping your data clean and reliable.

Key Takeaway: Use COMMIT to finalize successful batches of work and ROLLBACK to discard changes when errors occur, ensuring your database never reflects an incomplete or corrupted state.