3-5-1 COMMIT, ROLLBACK, SAVEPOINT Explained

Key Concepts

• COMMIT
• ROLLBACK
• SAVEPOINT

COMMIT

The COMMIT statement is used to permanently save the changes made within a transaction to the database. Once a COMMIT is issued, the changes are made permanent and cannot be undone. The basic syntax is:

        COMMIT;
    

Example: After inserting a new record into the "Customers" table, you would use COMMIT to save the changes:

        INSERT INTO Customers (Name, Email)
        VALUES ('Jane Smith', 'jane.smith@example.com');
        COMMIT;
    

Analogies: Think of COMMIT as sealing a letter and sending it. Once the letter is sent, you cannot change its contents.

ROLLBACK

The ROLLBACK statement is used to undo all the changes made within a transaction since the last COMMIT or ROLLBACK. It restores the database to its previous state. The basic syntax is:

        ROLLBACK;
    

Example: If you realize that an incorrect record was inserted into the "Customers" table, you can use ROLLBACK to undo the changes:

        ROLLBACK;
    

Analogies: Think of ROLLBACK as recalling a sent letter. It allows you to undo the changes and revert to the previous state.

SAVEPOINT

The SAVEPOINT statement is used to create a marker within a transaction that allows you to ROLLBACK to a specific point without undoing all the changes. It provides finer control over transaction management. The basic syntax is:

        SAVEPOINT savepoint_name;
    

Example: After inserting a new record and creating a SAVEPOINT, you can ROLLBACK to that point if needed:

        INSERT INTO Customers (Name, Email)
        VALUES ('Alice Johnson', 'alice.johnson@example.com');
        SAVEPOINT insert_point;
        -- Perform more operations
        ROLLBACK TO SAVEPOINT insert_point;
    

Analogies: Think of SAVEPOINT as bookmarking a page in a book. You can return to that specific page without rereading the entire book.

Conclusion

Understanding COMMIT, ROLLBACK, and SAVEPOINT is crucial for managing transactions in a database. These commands allow you to control the state of your data, ensuring that changes are either saved permanently or undone as needed. By mastering these concepts, you can maintain data integrity and consistency in your database.