MySQL COMMIT and ROLLBACK

COMMIT and ROLLBACK are the two primary transaction control commands in MySQL. COMMIT permanently saves all changes made during a transaction. ROLLBACK undoes all changes and restores the database to its state before the transaction began.

COMMIT

When COMMIT is issued, all changes made since the START TRANSACTION statement are written permanently to the database. After a COMMIT, the changes cannot be undone with ROLLBACK.

Syntax

START TRANSACTION;
-- SQL statements
COMMIT;

ROLLBACK

When ROLLBACK is issued, all changes made since the START TRANSACTION statement are discarded. The database returns to the state it was in before the transaction started.

Syntax

START TRANSACTION;
-- SQL statements
ROLLBACK;

Sample Table

CREATE TABLE inventory (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100),
    quantity INT
);

INSERT INTO inventory VALUES (1, 'Laptop', 50);
INSERT INTO inventory VALUES (2, 'Mouse', 200);
INSERT INTO inventory VALUES (3, 'Keyboard', 150);

Example: COMMIT — Permanent Change

START TRANSACTION;

UPDATE inventory SET quantity = quantity - 5 WHERE item_id = 1;
UPDATE inventory SET quantity = quantity - 10 WHERE item_id = 2;

COMMIT;

After COMMIT:

item_id | item_name | quantity
--------+-----------+---------
1       | Laptop    | 45
2       | Mouse     | 190
3       | Keyboard  | 150

Changes are now permanent. Even if the server restarts, the data remains updated.

Example: ROLLBACK — Discard Changes

START TRANSACTION;

UPDATE inventory SET quantity = 0 WHERE item_id = 3;
DELETE FROM inventory WHERE item_id = 2;

-- Decide to undo everything
ROLLBACK;

After ROLLBACK:

item_id | item_name | quantity
--------+-----------+---------
1       | Laptop    | 45
2       | Mouse     | 190
3       | Keyboard  | 150

The table is exactly as it was before the transaction. The deletion and update never took effect.

ROLLBACK After a Mistaken DELETE

START TRANSACTION;

DELETE FROM inventory;  -- Accidentally deletes all rows

-- Immediately issue a rollback
ROLLBACK;

All rows are restored. This is why wrapping dangerous operations in a transaction is a safe practice.

Verifying Before COMMIT

A common pattern is to start a transaction, check the results, and only commit if everything looks correct.

START TRANSACTION;

UPDATE inventory SET quantity = quantity + 100 WHERE item_id = 1;

-- Verify the change
SELECT * FROM inventory WHERE item_id = 1;

-- If correct, commit; otherwise, rollback
COMMIT;
-- or
-- ROLLBACK;

COMMIT vs ROLLBACK

Implicit COMMIT

Certain statements in MySQL automatically issue an implicit COMMIT, ending any open transaction. These include DDL statements like CREATE TABLE, DROP TABLE, ALTER TABLE, and others. Running such a statement inside a transaction commits all pending changes before executing.

Key Points

• COMMIT permanently saves all changes made in the current transaction.
• ROLLBACK undoes all changes made since the transaction began.
• After COMMIT, changes cannot be rolled back.
• DDL statements like CREATE and DROP trigger an implicit COMMIT.
• Always wrap critical multi-step operations in a transaction to protect data integrity.