SQL Transactions — COMMIT, ROLLBACK, SAVEPOINT in SQL

A new student and an initial result should be saved together. Keeping only one part would leave an inconsistent school record.

A transaction groups operations into one unit whose changes can be committed or rolled back according to the database engine’s transactional rules.

Start a transaction, perform the related changes, check their outcomes and commit only when the unit is valid. A rollback discards its uncommitted transactional changes. A savepoint identifies a position for partial rollback within that transaction; it is not a durable copy of the database.

Related concepts: Foreign Keys and Handle SQL Errors and Triggers.

A transaction is a group of SQL statements that must be executed together. If one part fails, the entire group can be undone. Think of it like a checklist: either you complete all tasks or you undo everything.

• BEGIN / START TRANSACTION – begins a new transaction
• COMMIT – saves all changes permanently
• ROLLBACK – undoes all changes since the transaction started
• SAVEPOINT – sets a bookmark within the transaction
• ROLLBACK TO SAVEPOINT – undoes changes back to that bookmark

SQL · portable concepts; preserved examples identify their engine

CREATE TABLE students (
  roll_no INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE results (
  roll_no INT,
  subject VARCHAR(30),
  marks INT
);

The following seed rows complete the input for the worked examples. They are also reproduced with each example that uses them.

INSERT INTO students VALUES (1,'Aarav Sharma'),(2,'Diya Iyer'),(3,'Sneha Patil');
INSERT INTO results VALUES (1,'Maths',85),(1,'Science',88),(2,'Maths',92),(2,'Science',90),(3,'Maths',78),(3,'Science',84);

The comments “Invalid marks” express the school’s 0–100 rule. The preserved sample schema has no CHECK enforcing that rule, so 105 does not automatically trigger a database error. The explicit ROLLBACK below is the application’s deliberate response; COMMIT saves valid changes to the seeded rows. Add an appropriate constraint when the database itself must reject an out-of-range mark.

SQL · portable concepts; preserved examples identify their engine

START TRANSACTION;

UPDATE results
SET marks = 90
WHERE roll_no = 1 AND subject = 'Maths';

UPDATE results
SET marks = 105
WHERE roll_no = 2 AND subject = 'Science'; -- Invalid marks

ROLLBACK;

All updates are discarded. Nothing is saved.

If all changes are valid:

START TRANSACTION;

UPDATE results
SET marks = 90
WHERE roll_no = 1 AND subject = 'Maths';

UPDATE results
SET marks = 88
WHERE roll_no = 2 AND subject = 'Science';

COMMIT;

Both updates saved permanently.

Start from the reproduced values 78 for Maths and 84 for Science. The first update changes Maths to 85. Rolling back to the savepoint cancels the Science change to 110, so the final marks are 85 and 84. The school considers 110 invalid even though this unrestrained fixture can store it.

SQL · portable concepts; preserved examples identify their engine

START TRANSACTION;

UPDATE results SET marks = 85 WHERE roll_no = 3 AND subject = 'Maths';
SAVEPOINT before_second_update;

UPDATE results SET marks = 110 WHERE roll_no = 3 AND subject = 'Science'; -- Invalid

ROLLBACK TO before_second_update;

COMMIT;

First update saved. Second update cancelled.

SQL · portable concepts; preserved examples identify their engine

START TRANSACTION;

INSERT INTO students VALUES (6, 'Ravi Kumar');

INSERT INTO results VALUES (6, 'Maths', 95);
INSERT INTO results VALUES (6, 'Science', NULL); -- Missed entry

ROLLBACK;

Why rollback? Because incomplete data was inserted. You’ll want to retry later with full information.

• Use TRANSACTION when working with multiple related queries
• Use ROLLBACK when you detect errors during execution
• Use SAVEPOINT to break logic into safe checkpoints
• Always COMMIT only when everything is verified

• Bulk updating results after exams
• Inserting students with multi-subject entries
• Undoing mistakes in attendance uploads

Atomicity and isolation address different concerns. Concurrent sessions and the configured isolation level affect what can be observed. MySQL DDL commonly causes implicit commits, so a rollback demonstration with data changes must not imply that every schema change can be undone.

Change a mark inside a transaction, roll back, and read the original value.

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

This practice uses a separate, fresh fixture named practice_students. It does not continue the changing table state of the earlier examples.

SQL · create only these practice tables in a disposable database:

CREATE TABLE practice_students (roll_no INTEGER PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), marks INTEGER, city VARCHAR(30));
INSERT INTO practice_students VALUES
(1, 'Aarav', '10A', 85, 'Pune'),
(2, 'Diya', '9B', 70, 'Chennai'),
(3, 'Sneha', '10A', 85, 'Pune');

SQL · this practice variation runs with SQLite 3; the preserved examples retain their stated dialect

BEGIN; UPDATE practice_students SET marks = 99 WHERE roll_no = 2; ROLLBACK;
SELECT name, marks FROM practice_students WHERE roll_no = 2;

The fixture uses a fresh SQLite transaction for this executable variation. The uncommitted update is discarded, so the subsequent SELECT reads 70. MySQL uses START TRANSACTION for the equivalent workflow.

A transaction groups operations into one unit whose changes can be committed or rolled back according to the database engine’s transactional rules.

The fixture uses a fresh SQLite transaction for this executable variation. The uncommitted update is discarded, so the subsequent SELECT reads 70. MySQL uses START TRANSACTION for the equivalent workflow.

Continue your understanding with Foreign Keys and Handle SQL Errors and Triggers.

Source for the engine-specific behavior: MySQL 8.4 reference documentation.