MySQL COMMIT & ROLLBACK

In the previous lesson we learned that a transaction groups SQL statements together and holds their changes in a temporary state. Once those statements have run, you face a decision: should the changes be made permanent, or should everything be thrown away? That decision is made using two statements — COMMIT and ROLLBACK.

---

COMMIT vs ROLLBACK at a Glance

---

COMMIT — Save Changes Permanently

COMMIT is the instruction that tells MySQL: "Everything went well — write all the changes to the database for good."

After a COMMIT, those changes become visible to all other users and sessions connected to the database.

Syntax

START TRANSACTION;

-- your SQL statements

COMMIT;

Example

The following example registers a new student and enrols them in a course in one safe transaction:

START TRANSACTION;

-- Add the new student
INSERT INTO students (name, email) VALUES ('Sara Ahmed', 'sara@example.com');

-- Enrol the student in course ID 3
INSERT INTO enrolments (student_id, course_id) VALUES (LAST_INSERT_ID(), 3);

-- Both inserts succeeded — commit permanently
COMMIT;

The LAST_INSERT_ID() function captures the auto-generated ID from the first INSERT and uses it in the second — a very common transaction pattern.

---

ROLLBACK — Undo All Changes

ROLLBACK is the safety net. It tells MySQL: "Something went wrong — cancel everything and put the database back to how it was."

ROLLBACK undoes every INSERT, UPDATE, and DELETE that happened after the most recent START TRANSACTION (or after the most recent SAVEPOINT, which we will cover shortly).

Syntax

START TRANSACTION;

-- your SQL statements

ROLLBACK;

Example

Consider an order placement where we deduct stock and create an order record. If the stock check fails, we roll everything back:

START TRANSACTION;

-- Reduce stock for product ID 7
UPDATE products SET stock = stock - 1 WHERE product_id = 7;

-- Check that stock did not go negative
-- (in a real app this logic would be handled in application code or a stored procedure)

-- For this demo, assume stock went negative — cancel everything
ROLLBACK;

After ROLLBACK, the product's stock value is unchanged — as if the UPDATE never ran.

---

Using SAVEPOINT

Sometimes you do not want to undo an entire transaction — just a specific portion of it. SAVEPOINT lets you plant a named marker inside a transaction. You can then roll back to that marker without losing the work done before it.

SAVEPOINT Syntax

SAVEPOINT savepoint_name;         -- Create a savepoint
ROLLBACK TO SAVEPOINT savepoint_name;  -- Roll back to that point
RELEASE SAVEPOINT savepoint_name;      -- Remove a savepoint (optional)

How SAVEPOINT Works

Here is a timeline showing how savepoints divide a transaction:

SAVEPOINT Example

START TRANSACTION;

-- Step 1: Insert a new order
INSERT INTO orders (customer_id, total) VALUES (12, 250.00);

-- Save a checkpoint after the order is created
SAVEPOINT after_order;

-- Step 2: Try to insert order items
INSERT INTO order_items (order_id, product_id, qty) VALUES (LAST_INSERT_ID(), 99, 2);

-- Suppose product_id 99 does not exist — we undo only the items insertion
ROLLBACK TO SAVEPOINT after_order;

-- The order row still exists. We can try again with correct data or simply commit the order alone.
COMMIT;

---

Statements That Cannot Be Rolled Back

Not every MySQL statement can be undone by a ROLLBACK. DDL statements — those that create or modify the structure of the database — cause an implicit commit and cannot be rolled back. These include:

Statement	Can it be rolled back?
INSERT, UPDATE, DELETE	✔ Yes
CREATE TABLE, DROP TABLE	✘ No — causes implicit commit
ALTER TABLE, TRUNCATE TABLE	✘ No — causes implicit commit
CREATE DATABASE, DROP DATABASE	✘ No — causes implicit commit

---

Complete Transaction Example

Here is a full, realistic example that combines START TRANSACTION, SAVEPOINT, ROLLBACK TO SAVEPOINT, and COMMIT:

START TRANSACTION;

-- Deduct payment from customer wallet
UPDATE wallets SET balance = balance - 100 WHERE customer_id = 5;

SAVEPOINT deducted;

-- Try to log the payment record
INSERT INTO payment_log (customer_id, amount, status) VALUES (5, 100, 'pending');

-- Suppose the log table does not exist — MySQL raises an error
-- We roll back only the failed log insertion
ROLLBACK TO SAVEPOINT deducted;

-- The wallet deduction is still in place
-- Update the status in a different way or handle the error

-- Commit the wallet deduction permanently
COMMIT;

---

Key Points to Remember

• COMMIT saves all transaction changes permanently to the database.
• ROLLBACK undoes all changes made since START TRANSACTION.
• SAVEPOINT name creates a named checkpoint inside a transaction.
• ROLLBACK TO SAVEPOINT name undoes changes only back to that checkpoint — earlier work is preserved.
• DDL statements like CREATE TABLE or DROP TABLE trigger an implicit commit and cannot be rolled back.
• Rolling back to a savepoint does not close the transaction — you still need a final COMMIT or ROLLBACK.