Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 40 of 49

Transactions: COMMIT and ROLLBACK

Group SQL statements into a transaction so they all succeed or all fail, with BEGIN, COMMIT, ROLLBACK and SAVEPOINT, shown with a SELECT before and after.

14 minChanging dataUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonWrap related changes in a transaction, undo them with ROLLBACK or keep them with COMMIT, and explain why this protects data.

What you will be able to do

After this lesson you can group several changes so they succeed or fail together, undo a mistake before it becomes permanent, and keep part of a larger change. Each example shows the data before, inside and after the transaction.

The problem

Moving 50,000 of budget from the Mobile App project to the Billing Migration project takes two statements: subtract from one, add to the other. If the program crashes between them, 50,000 has vanished. A transaction makes the pair indivisible: either both changes are stored, or neither is.

BEGIN, COMMIT and ROLLBACK

You start a transaction, run statements, and finish with COMMIT (keep everything) or ROLLBACK (undo everything). Outside a transaction most databases are in autocommit mode, where each statement is its own tiny transaction that commits immediately.

Each example below is run as one script, so the whole transaction and its before and after checks appear together. First, a transaction that is committed:

sql
SELECT id, name, budget FROM projects WHERE id IN (2, 5) ORDER BY id;

BEGIN;
UPDATE projects SET budget = budget - 50000 WHERE id = 2;
UPDATE projects SET budget = budget + 50000 WHERE id = 5;
COMMIT;

SELECT id, name, budget FROM projects WHERE id IN (2, 5) ORDER BY id;
SELECT SUM(budget) AS total_budget FROM projects;
result
+----+-------------------+--------+
| id |       name        | budget |
+----+-------------------+--------+
|  2 | Mobile App        | 650000 |
|  5 | Billing Migration | 300000 |
+----+-------------------+--------+
+----+-------------------+--------+
| id |       name        | budget |
+----+-------------------+--------+
|  2 | Mobile App        | 600000 |
|  5 | Billing Migration | 350000 |
+----+-------------------+--------+
+--------------+
| total_budget |
+--------------+
|      1670000 |
+--------------+

The total is unchanged at 1,670,000, which is exactly what a correct transfer should preserve. The committed changes are permanent, and the later examples build on them.

ROLLBACK: undoing a mistake

Now a mistake. Someone runs an UPDATE and forgets the WHERE. Inside a transaction you can look, see the damage, and undo it:

sql
SELECT MIN(salary) AS lowest, MAX(salary) AS highest FROM employees;

BEGIN;
UPDATE employees SET salary = 1;
SELECT MIN(salary) AS lowest, MAX(salary) AS highest FROM employees;
ROLLBACK;

SELECT MIN(salary) AS lowest, MAX(salary) AS highest FROM employees;
result
+--------+---------+
| lowest | highest |
+--------+---------+
|  50000 |  180000 |
+--------+---------+
+--------+---------+
| lowest | highest |
+--------+---------+
|      1 |       1 |
+--------+---------+
+--------+---------+
| lowest | highest |
+--------+---------+
|  50000 |  180000 |
+--------+---------+

The middle SELECT shows every salary at 1, because the transaction sees its own changes. After ROLLBACK, the original range is back. Without the transaction, that mistake would have destroyed the salary data. This is why you should do risky changes inside a transaction and check the result before committing.

Keeping the work together

A transaction can contain any number of statements, and the whole group is judged as one. Here we add a department and its first employee. Both go in, or neither does:

sql
SELECT COUNT(*) AS departments FROM departments;

BEGIN;
INSERT INTO departments (id, name, city) VALUES (7, 'Legal', 'Chennai');
INSERT INTO employees (id, first_name, last_name, email, department_id, job_title, salary, hire_date)
VALUES (21, 'Nikhil', 'Bhat', 'nikhil.bhat@example.com', 7, 'Legal Counsel', 105000, '2026-11-02');
COMMIT;

SELECT d.name AS department, e.first_name
FROM departments d
JOIN employees e ON e.department_id = d.id
WHERE d.id = 7;
result
+-------------+
| departments |
+-------------+
|           6 |
+-------------+
+------------+------------+
| department | first_name |
+------------+------------+
| Legal      | Nikhil     |
+------------+------------+

In a real program you would check each statement for an error and run ROLLBACK if any failed. The database cannot do that decision for you in a plain SQL script.

SAVEPOINT: a partial undo

A savepoint is a named marker inside a transaction. ROLLBACK TO that name undoes only what came after it:

sql
BEGIN;
UPDATE employees SET salary = salary + 1000 WHERE department_id = 5;
SAVEPOINT before_cleanup;
DELETE FROM employees WHERE department_id = 5 AND job_title = 'Support Engineer';
SELECT first_name, salary FROM employees WHERE department_id = 5 ORDER BY id;
ROLLBACK TO before_cleanup;
SELECT first_name, salary FROM employees WHERE department_id = 5 ORDER BY id;
COMMIT;

SELECT first_name, salary FROM employees WHERE department_id = 5 ORDER BY id;
result
+------------+--------+
| first_name | salary |
+------------+--------+
| Pooja      |  86000 |
+------------+--------+
+------------+--------+
| first_name | salary |
+------------+--------+
| Pooja      |  86000 |
| Karan      |  57000 |
| Sana       |  57000 |
+------------+--------+
+------------+--------+
| first_name | salary |
+------------+--------+
| Pooja      |  86000 |
| Karan      |  57000 |
| Sana       |  57000 |
+------------+--------+

The first SELECT shows only Pooja, because the two Support Engineers were deleted inside the transaction. The ROLLBACK TO brought them back, but the 1,000 raise made before the savepoint was kept and committed. All three Support employees show a salary 1,000 higher than the original 85,000, 56,000 and 56,000.

The syntax to start a transaction

The standard way is START TRANSACTION, which MySQL and PostgreSQL accept. Most databases also take a shorter or different form. This block is not run, because SQLite only knows BEGIN:

sql
START TRANSACTION;
UPDATE projects SET budget = budget - 50000 WHERE id = 2;
UPDATE projects SET budget = budget + 50000 WHERE id = 5;
COMMIT;

What the database promises: ACID

  • Atomicity. All or nothing, as shown above.
  • Consistency. A transaction moves the data from one valid state to another; constraints are checked.
  • Isolation. Other users do not see your half-finished work. How strictly, and what happens when two transactions touch the same rows, depends on the isolation level and the database.
  • Durability. After COMMIT, the change survives a crash.

Isolation levels and locking are deeper topics; the MySQL transactions lesson covers them for MySQL.

Differences between databases

  • Starting one. START TRANSACTION works in MySQL and PostgreSQL; BEGIN works in MySQL, PostgreSQL and SQLite; SQL Server uses BEGIN TRANSACTION.
  • Autocommit. MySQL, PostgreSQL and SQL Server commit each statement automatically outside an explicit transaction. Many client tools and drivers have their own setting, so check what yours does.
  • DDL. In MySQL, statements such as CREATE TABLE and ALTER TABLE commit the current transaction implicitly. PostgreSQL, SQLite and SQL Server allow most of them inside a transaction.
  • Savepoint syntax. SAVEPOINT name and ROLLBACK TO name work in MySQL, PostgreSQL and SQLite; SQL Server uses SAVE TRANSACTION name and ROLLBACK TRANSACTION name.

Common mistakes

  • Leaving a transaction open. Locks stay held and other users wait. Always finish with COMMIT or ROLLBACK.
  • Forgetting to COMMIT. Your changes vanish when the connection closes.
  • Thinking ROLLBACK undoes a COMMIT. Once committed, a change is permanent; only a new statement or a backup can reverse it.
  • Doing slow work inside a transaction. Keep transactions short so other users are not blocked.
  • Assuming every statement can be rolled back. In some databases, such as MySQL, schema changes commit implicitly.

Exercise

Transfer 20,000 of budget from project 3 (Spring Campaign) to project 1 (Customer Portal) in a transaction, and show both budgets before and after. Then, in a second transaction, try setting project 3's budget to 0, check it with a SELECT, and roll it back, showing that the budget returns to its committed value.

Show one possible answer
sql
SELECT id, name, budget FROM projects WHERE id IN (1, 3) ORDER BY id;

BEGIN;
UPDATE projects SET budget = budget - 20000 WHERE id = 3;
UPDATE projects SET budget = budget + 20000 WHERE id = 1;
COMMIT;

SELECT id, name, budget FROM projects WHERE id IN (1, 3) ORDER BY id;

BEGIN;
UPDATE projects SET budget = 0 WHERE id = 3;
SELECT id, budget FROM projects WHERE id = 3;
ROLLBACK;

SELECT id, budget FROM projects WHERE id = 3;
result
+----+-----------------+--------+
| id |      name       | budget |
+----+-----------------+--------+
|  1 | Customer Portal | 400000 |
|  3 | Spring Campaign | 120000 |
+----+-----------------+--------+
+----+-----------------+--------+
| id |      name       | budget |
+----+-----------------+--------+
|  1 | Customer Portal | 420000 |
|  3 | Spring Campaign | 100000 |
+----+-----------------+--------+
+----+--------+
| id | budget |
+----+--------+
|  3 |      0 |
+----+--------+
+----+--------+
| id | budget |
+----+--------+
|  3 | 100000 |
+----+--------+

Key points

  • A transaction groups statements into one unit: either every change is kept (COMMIT) or none is (ROLLBACK).
  • Inside a transaction you can see your own changes, but other connections do not see them until you commit.
  • Use a transaction whenever one real-world action needs several statements, such as moving budget from one project to another.
  • A SAVEPOINT marks a point inside a transaction that you can roll back to without abandoning the whole transaction.
  • The ACID properties (atomicity, consistency, isolation, durability) are what a transaction guarantees; the exact syntax for starting one differs between databases.

Check yourself

1. You move budget from project A to project B with two UPDATE statements. The second fails. Why use a transaction?
2. After BEGIN and an UPDATE, you run ROLLBACK. What does SELECT show?
3. What does COMMIT do?
4. What is a SAVEPOINT for?