Commit and Rollback Operation in MySQL

• Let's go through a step-by-step example of how to perform a commit and rollback operation in MySQL using the MySQL command-line interface or Workbench.

Commit:

• When you commit a transaction, you're confirming that all the changes made within that transaction should be permanently saved to the database. If the transaction is successful and passes all checks, the changes become part of the database's persistent state.

Here's how you can use the "commit" operation in MySQL Workbench:

• Open a Transaction: Start by initiating a transaction. You can do this by executing the START TRANSACTION; SQL statement. Alternatively, any data modification query you run after connecting to the database will automatically start a transaction.

• Perform Data Operations: Within the transaction, you can execute various SQL statements to insert, update, or delete data as needed.

• Commit the Transaction: Once you're satisfied with the changes and want to make them permanent, you can commit the transaction using the COMMIT; SQL statement. This action will finalize the changes and make them permanent in the database.

Rollback:

• Rollback is the opposite of committing. If you encounter an issue within a transaction or if you decide that you don't want to keep the changes, you can roll back the transaction. Rolling back means that all the changes made within the transaction will be discarded, and the database will be reverted to its state before the transaction started.

Here's how to use "rollback" in MySQL Workbench:

• Open a Transaction: Similar to the commit process, start by initiating a transaction.

• Perform Data Operations: Execute the necessary SQL statements to modify data within the transaction.

• Rollback the Transaction: If you encounter an error or decide not to proceed with the changes, you can roll back the transaction using the ROLLBACK; SQL statement. This action will undo all the changes made within the transaction and restore the database to its state before the transaction started.

Example Scenario:

• Let's imagine a simple scenario where we have a database with a table named "employees" containing employee information. We want to insert a new employee's record into the table as a transaction and demonstrate both commit and rollback operations.

Step 1: Create the Database and Table

• First, let's create a database named "company" and a table named "employees":

    CREATE DATABASE company;

    USE company;

    CREATE TABLE employees (

        id INT PRIMARY KEY,

        name VARCHAR(50),

        department VARCHAR(50)

    );

Step 2: Start a Transaction and Perform Operations

• Now, let's start a transaction, insert a new employee's record, and then decide whether to commit or rollback the transaction.

    -- Start a transaction

    START TRANSACTION;

    -- Insert a new employee's record

    INSERT INTO employees (id, name, department) VALUES (1, 'John Doe', 'Finance');

    -- Display the current state of the table

    SELECT * FROM employees;

• At this point, if you run the `SELECT` query, you'll see the newly inserted employee's record.

Step 3: Commit the Transaction

• Assuming you're satisfied with the changes and want to make them permanent, you can commit the transaction:

    -- Commit the transaction

    COMMIT;

    -- Display the table after committing

    SELECT * FROM employees;

• The inserted employee's record is now permanently stored in the table.

Step 4: Rollback the Transaction

• Now, let's imagine that you made an error or decided not to proceed with the changes. You can use a rollback to undo the changes made within the transaction:

    -- Start a new transaction

    START TRANSACTION;

    -- Insert another employee's record

    INSERT INTO employees (id, name, department) VALUES (2, 'Jane Smith', 'Marketing');

    -- Display the current state of the table

    SELECT * FROM employees;

• At this point, if you run the `SELECT` query, you'll see both the previously inserted employee and the newly inserted employee.

    -- Rollback the transaction to undo changes

    ROLLBACK;

    -- Display the table after rolling back

    SELECT * FROM employees;

• After rolling back the transaction, the changes made within that transaction (inserting "Jane Smith") are discarded, and the table is reverted to its state before the transaction started.

• The key takeaway is that transactions provide a way to ensure data consistency and integrity while allowing you to either permanently save changes or discard them based on the situation.