MySQL COMMIT & ROLLBACK

COMMIT is used to successfully complete the current transaction.

COMMIT;

After COMMIT, the transaction's changes become permanent from the transaction's perspective and cannot be undone using ROLLBACK.

ROLLBACK is used to undo changes made by the current transaction that have not yet been committed.

ROLLBACK;

For a transactional table such as an InnoDB table, changes made since the transaction began can be undone before COMMIT.

The basic syntax is:

COMMIT;

You can also write:

COMMIT WORK;

For normal MySQL usage, COMMIT; is the commonly used form.

The basic syntax is:

ROLLBACK;

It cancels the current transaction's uncommitted changes.

Before using COMMIT or ROLLBACK explicitly, you can start a transaction using:

START TRANSACTION;

Example:

START TRANSACTION;

UPDATE students
SET status = 'Active'
WHERE student_id = 10;

COMMIT;

START TRANSACTION;

UPDATE students
SET city = 'Patna'
WHERE student_id = 1;

COMMIT;

The UPDATE is successfully completed and committed.

START TRANSACTION;

UPDATE students
SET city = 'Delhi'
WHERE student_id = 1;

ROLLBACK;

The UPDATE is undone because the transaction was rolled back before it was committed.

COMMIT can be used after an INSERT:

START TRANSACTION;

INSERT INTO students
(name, email, city)
VALUES
('Amit Kumar', 'amit@example.com', 'Patna');

COMMIT;

The new row is committed as part of the transaction.

START TRANSACTION;

INSERT INTO students
(name, email, city)
VALUES
('Rahul Sharma', 'rahul@example.com', 'Gaya');

ROLLBACK;

For an InnoDB table, the inserted row is removed by the rollback because the INSERT was not committed.

START TRANSACTION;

UPDATE students
SET status = 'Active'
WHERE student_id = 5;

COMMIT;

The UPDATE becomes part of the committed transaction.

START TRANSACTION;

UPDATE students
SET status = 'Inactive'
WHERE student_id = 5;

ROLLBACK;

The UPDATE is undone if the table is transactional and the change has not been committed.

START TRANSACTION;

DELETE FROM students
WHERE student_id = 20;

COMMIT;

The DELETE is committed.

After COMMIT, you cannot use ROLLBACK to restore the deleted row through that transaction.

START TRANSACTION;

DELETE FROM students
WHERE student_id = 20;

ROLLBACK;

For an InnoDB table, the DELETE is undone because the transaction was rolled back before COMMIT.

A transaction can contain multiple SQL statements.

START TRANSACTION;

INSERT INTO students
(name, email)
VALUES
('Amit', 'amit@example.com');

UPDATE students
SET status = 'Active'
WHERE student_id = 2;

DELETE FROM students
WHERE student_id = 5;

COMMIT;

All three operations are part of the transaction.

If multiple changes belong to the same transaction, ROLLBACK can undo them together.

START TRANSACTION;

INSERT INTO students
(name)
VALUES
('Amit');

UPDATE students
SET status = 'Active'
WHERE student_id = 2;

DELETE FROM students
WHERE student_id = 5;

ROLLBACK;

For transactional tables, the uncommitted changes are rolled back.

Once a transaction has been committed, ROLLBACK cannot undo that transaction.

START TRANSACTION;

UPDATE students
SET status = 'Active'
WHERE student_id = 1;

COMMIT;

ROLLBACK;

The ROLLBACK after COMMIT does not undo the already committed UPDATE.

MySQL normally has autocommit enabled.

SELECT @@autocommit;

If the result is 1, autocommit is enabled.

With autocommit enabled, individual transactional statements are normally committed automatically unless they are part of an explicit transaction.

You can disable autocommit for the current session:

SET autocommit = 0;

Now you can control when changes are committed:

UPDATE students
SET city = 'Patna'
WHERE student_id = 1;

COMMIT;

Or cancel them:

ROLLBACK;

Autocommit can be enabled again:

SET autocommit = 1;

When autocommit is enabled, each suitable transactional statement is normally committed automatically when it completes successfully outside an explicit transaction.

SAVEPOINT allows partial rollback inside a transaction.

START TRANSACTION;

UPDATE students
SET status = 'Active'
WHERE student_id = 1;

SAVEPOINT student_step;

UPDATE students
SET status = 'Inactive'
WHERE student_id = 2;

ROLLBACK TO SAVEPOINT student_step;

COMMIT;

The second UPDATE is rolled back, while the first UPDATE can remain part of the committed transaction.

ROLLBACK TO SAVEPOINT does not end the entire transaction.

START TRANSACTION;

UPDATE students
SET city = 'Patna'
WHERE student_id = 1;

SAVEPOINT city_change;

UPDATE students
SET city = 'Delhi'
WHERE student_id = 2;

ROLLBACK TO SAVEPOINT city_change;

UPDATE students
SET city = 'Gaya'
WHERE student_id = 3;

COMMIT;

The transaction continues after the partial rollback.

A savepoint can be removed using:

RELEASE SAVEPOINT city_change;

After a savepoint is released, you cannot roll back to that savepoint.

Suppose ₹1,000 is transferred from account 1 to account 2.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

COMMIT;

COMMIT completes both related operations as one transaction.

If a problem occurs before COMMIT, the application can roll back the transaction.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

ROLLBACK;

For InnoDB, both uncommitted balance changes are undone.

An order system may insert an order and update inventory within the same transaction.

START TRANSACTION;

INSERT INTO orders
(customer_id, total_amount)
VALUES
(101, 2500);

UPDATE products
SET stock = stock - 1
WHERE product_id = 10;

COMMIT;

The application can commit both operations after verifying that they completed successfully.

If an error occurs during an order transaction, the application may roll back the uncommitted changes.

START TRANSACTION;

INSERT INTO orders
(customer_id, total_amount)
VALUES
(101, 2500);

UPDATE products
SET stock = stock - 1
WHERE product_id = 10;

ROLLBACK;

For InnoDB tables, the transaction's uncommitted changes can be undone.

• Calling COMMIT before checking whether all operations succeeded.
• Forgetting to ROLLBACK after an application error.
• Assuming ROLLBACK can undo already committed changes.
• Ignoring autocommit settings.
• Using non-transactional storage engines and expecting normal rollback behavior.
• Keeping transactions open longer than necessary.
• Not handling database errors correctly in application code.

Successful operation:

START TRANSACTION;

UPDATE students
SET fee_paid = fee_paid + 1000
WHERE student_id = 1;

COMMIT;

Cancel operation:

START TRANSACTION;

UPDATE students
SET fee_paid = fee_paid + 1000
WHERE student_id = 1;

ROLLBACK;

In the first example, the change is committed. In the second example, the uncommitted change is cancelled for a transactional table.

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(100),
    balance DECIMAL(12,2)
) ENGINE=InnoDB;

INSERT INTO accounts
(account_id, account_name, balance)
VALUES
(1, 'Amit', 10000.00),
(2, 'Rahul', 5000.00);

-- Start transaction
START TRANSACTION;

-- Deduct from Amit
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

-- Add to Rahul
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

-- Complete transaction
COMMIT;

-- Check balances
SELECT *
FROM accounts;

If an error occurs before COMMIT, use:

ROLLBACK;

For example:

START TRANSACTION;

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

-- Cancel both uncommitted changes
ROLLBACK;

This example demonstrates the practical difference between COMMIT and ROLLBACK.