SQL Transactions: COMMIT, ROLLBACK, SAVEPOINT

Reassigning a Beacon Robotics project from one employee to another takes two statements: delete the old project_assignments row, insert the new one. If the connection drops between them, the project now has no one assigned at all, half a change applied is worse than no change. A transaction is how to make both statements succeed or fail together.

Learning Objectives

• Define a transaction, and explain what "all-or-nothing" actually means for a group of statements.
• Use COMMIT and ROLLBACK, and the real BEGIN/START TRANSACTION syntax split across all six databases.
• Use SAVEPOINT to undo part of a transaction without discarding all of it.

Starting a Transaction, by Database

COMMIT and ROLLBACK

COMMIT makes every statement since the transaction began permanent, visible to every other connection. ROLLBACK, used instead of COMMIT, undoes every statement since the transaction began, as if none of them had ever run. Once COMMIT has actually succeeded, ROLLBACK can no longer undo it, commit is the point of no return.

START TRANSACTION;

DELETE FROM project_assignments
WHERE employee_id = 12 AND project_id = 4;

INSERT INTO project_assignments (employee_id, project_id)
VALUES (18, 4);

COMMIT;

BEGIN TRANSACTION;

DELETE FROM project_assignments
WHERE employee_id = 12 AND project_id = 4;

INSERT INTO project_assignments (employee_id, project_id)
VALUES (18, 4);

COMMIT TRANSACTION;

SAVEPOINT: Undoing Only Part of a Transaction

A SAVEPOINT marks a point inside a still-open transaction that a later ROLLBACK can return to, without discarding everything back to the very start. Useful when one step of a larger transaction might fail in a way that's fine to retry or skip, without losing the steps already done before it.

A MySQL-Specific Gotcha: Storage Engine

Transactions on MySQL and MariaDB only work on the InnoDB storage engine, the default since MySQL 5.5. The older MyISAM engine has no transaction support at all, START TRANSACTION and ROLLBACK are silently accepted but do nothing on a MyISAM table, every statement commits immediately regardless. This is specific to MySQL/MariaDB's pluggable storage engines, none of the other five databases have an engine choice that can silently disable transactions this way.

Try It Yourself

Write a transaction on PostgreSQL that deletes employee id 25's row from project_assignments, then inserts a replacement assignment for employee id 31 on the same project, committing only if both succeed.

BEGIN;

DELETE FROM project_assignments
WHERE employee_id = 25 AND project_id = 7;

INSERT INTO project_assignments (employee_id, project_id)
VALUES (31, 7);

COMMIT;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Keep a transaction to the statements that genuinely need to succeed or fail together, not everything a request happens to do.
• Always pair BEGIN/START TRANSACTION with exactly one COMMIT or ROLLBACK, in every code path, including error handling.
• Use SAVEPOINT when one step of a larger transaction might reasonably need to be undone without discarding the whole thing.

Interview Questions

Summary

A transaction groups multiple statements so they succeed or fail together, COMMIT makes them permanent, ROLLBACK undoes them, SAVEPOINT marks a point partway through for a partial rollback. Starting one is genuine, real syntax that differs by database, down to Oracle not needing a start statement at all.

What's Next?

The next lesson covers ACID, the four properties a transaction is actually supposed to guarantee, atomicity, consistency, isolation, and durability, and what each one really means underneath.