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:
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;+----+-------------------+--------+
| 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:
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;+--------+---------+
| 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:
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;+-------------+
| 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:
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;+------------+--------+
| 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:
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 TRANSACTIONworks in MySQL and PostgreSQL;BEGINworks in MySQL, PostgreSQL and SQLite; SQL Server usesBEGIN 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 TABLEandALTER TABLEcommit the current transaction implicitly. PostgreSQL, SQLite and SQL Server allow most of them inside a transaction. - Savepoint syntax.
SAVEPOINT nameandROLLBACK TO namework in MySQL, PostgreSQL and SQLite; SQL Server usesSAVE TRANSACTION nameandROLLBACK TRANSACTION name.
Common mistakes
- Leaving a transaction open. Locks stay held and other users wait. Always finish with
COMMITorROLLBACK. - 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
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;+----+-----------------+--------+
| 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.