COMMIT, ROLLBACK and SAVEPOINT

COMMIT, ROLLBACK and SAVEPOINT

These transaction control statements decide how much active work is kept. COMMIT accepts the complete transaction, ROLLBACK rejects all its pending work, and a savepoint creates a named position for partial recovery without ending the transaction.

Verified Student-Result Lab

Run the DDL and seed rows before starting transaction experiments. InnoDB is essential for predictable rollback behavior.

DROP TABLE IF EXISTS student_results;

CREATE TABLE student_results (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(60) NOT NULL,
  marks DECIMAL(5,2) NOT NULL,
  CONSTRAINT chk_marks CHECK (marks BETWEEN 0 AND 100)
) ENGINE = InnoDB;

INSERT INTO student_results VALUES
(1, 'Aarav', 80.00),
(2, 'Meera', 85.00),
(3, 'Sana',  90.00);

Reset to these seed values before running each independent example, or run the examples in the displayed order and observe every checkpoint.

COMMIT: Permanently Accept the Unit

START TRANSACTION;

UPDATE student_results
SET marks = marks + 5
WHERE student_id = 1;

SELECT student_id, marks
FROM student_results
WHERE student_id = 1;
-- Current session sees 85.00.

COMMIT;

SELECT student_id, marks
FROM student_results
WHERE student_id = 1;
-- 85.00 is now committed.

COMMIT ends the current transaction, deletes its savepoints and releases its InnoDB locks. After a successful commit, a later ROLLBACK cannot travel back into that completed transaction.

Full ROLLBACK: Reject Every Pending Change

This example temporarily changes two rows, checks the session's provisional view, then cancels both changes.

START TRANSACTION;

UPDATE student_results
SET marks = 40.00
WHERE student_id = 2;

UPDATE student_results
SET marks = 45.00
WHERE student_id = 3;

SELECT student_id, marks
FROM student_results
WHERE student_id IN (2, 3)
ORDER BY student_id;
-- Current session: Meera=40.00, Sana=45.00.

ROLLBACK;

SELECT student_id, marks
FROM student_results
WHERE student_id IN (2, 3)
ORDER BY student_id;

A single statement error does not universally mean that MySQL has rolled back the entire transaction. Application code must detect failure and issue full ROLLBACK unless the complete transaction is already known to have ended.

SAVEPOINT: Correct Only the Later Work

Assume Aarav's and Meera's updates are correct, but Sana was accidentally changed to 50 instead of 92. Savepoints let us preserve the earlier valid work.

START TRANSACTION;

UPDATE student_results SET marks = 85.00
WHERE student_id = 1;
SAVEPOINT after_aarav;

UPDATE student_results SET marks = 95.00
WHERE student_id = 2;
SAVEPOINT after_meera;

UPDATE student_results SET marks = 50.00
WHERE student_id = 3;  -- logical mistake

ROLLBACK TO SAVEPOINT after_meera;
-- Sana returns to 90.00; earlier updates remain pending.

UPDATE student_results SET marks = 92.00
WHERE student_id = 3;

RELEASE SAVEPOINT after_meera;
COMMIT;

ROLLBACK TO undid only the mistaken Sana update. It did not commit Aarav or Meera, and it did not end the transaction. The final COMMIT accepted all three corrected results.

Exact SAVEPOINT Rules in MySQL

• A savepoint exists only inside the current transaction.
• Creating a savepoint with an existing name deletes the old marker and sets the new one.
• ROLLBACK TO the named savepoint keeps that named point but deletes savepoints created after it.
• ROLLBACK TO undoes row modifications after the marker. InnoDB does not necessarily release every row lock stored in memory after that marker.
• RELEASE SAVEPOINT removes only the marker; it does not change table data.
• COMMIT or full ROLLBACK deletes all savepoints for the transaction.
• Using an unknown savepoint name returns error 1305.

START TRANSACTION;
SAVEPOINT review_point;
UPDATE student_results SET marks = 86 WHERE student_id = 1;
SAVEPOINT review_point;
-- The first review_point was replaced by the second.
ROLLBACK TO review_point;
COMMIT;

Use descriptive names such as after_header or before_optional_items. Savepoints are recovery markers, not a substitute for validating every statement.

Autocommit, DDL and Other Traps

With autocommit enabled and no explicit transaction, a successful UPDATE commits immediately. A savepoint therefore requires an active transaction.

SELECT @@autocommit;  -- normally 1

START TRANSACTION;
UPDATE student_results SET marks = 88 WHERE student_id = 1;
SAVEPOINT after_first_update;
-- Continue, roll back to the marker, or finish explicitly.

• Do not leave an interactive transaction open after a savepoint.
• Do not assume ROLLBACK can undo already committed work.
• Do not use nontransactional tables for work that must be recoverable.
• Do not confuse RELEASE SAVEPOINT with COMMIT.
• Do not expose savepoint names directly from untrusted user input.

Which Command Should You Use?

Practice tasks: add a fourth student after a savepoint and roll only that insert back; create two savepoints and prove that rolling to the earlier one deletes the later marker; test a full ROLLBACK; then verify final rows from a second database session.

Review the broader transactions lesson and the ACID properties lesson to connect these commands to reliability.

Official References

• MySQL 8.4: SAVEPOINT, ROLLBACK TO and RELEASE SAVEPOINT
• MySQL 8.4: COMMIT and ROLLBACK Statements
• MySQL 8.4: Statements That Cause an Implicit Commit

Command behavior and savepoint lifecycle were checked against the official MySQL 8.4 manual.