SQL Transactions - COMMIT, ROLLBACK, SAVEPOINT in SQL

Introduction

Imagine you're updating multiple student records and something fails halfway. You don’t want a partial update — you want an all-or-nothing execution. This is where SQL Transactions shine. With COMMIT, ROLLBACK, and SAVEPOINT, you can manage your data safely and precisely, like a real-world exam correction process.

What is a Transaction?

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.

Transaction Commands

• 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

Sample Tables – students and results

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

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

1. Using COMMIT and ROLLBACK

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.

2. Using SAVEPOINT and ROLLBACK TO SAVEPOINT

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.

3. Practical Example – Adding Student with Results

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.

Best Practices

• 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

Real-World School Use Cases

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

Summary

SQL transactions ensure your data operations are safe, consistent, and reversible. With COMMIT, ROLLBACK, and SAVEPOINT, you gain full control over what gets saved and what doesn't. Whether it's student marks or records, transactions make your database robust and mistake-proof.

What’s Next?

Coming up next: SQL Optimization Techniques — how to write faster, more efficient queries for large datasets.