SQL Transactions in Practice

The rest of this module covered COMMIT, ROLLBACK, ACID, and isolation levels one piece at a time. This lesson puts them together into one realistic transaction, and the practical habits around using one well.

Learning Objectives

• Write a realistic multi-statement transaction, in the real syntax of multiple databases.
• Apply the rollback-on-error pattern an application genuinely uses around a transaction.
• Explain why a long-running open transaction has a real cost in production, beyond just the locks it holds.

A Full Reassignment Transaction

Reassigning project id 4 from employee id 12 to employee id 18, the exact scenario this module opened with, written out in full, in each database's real wrapper syntax.

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;

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;

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

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

COMMIT;

The Rollback-on-Error Pattern

An application wraps a transaction the same way regardless of database: run each statement, and the moment any single one fails, issue ROLLBACK instead of continuing, undoing everything that already ran in this transaction. Only once every statement has succeeded does the application issue COMMIT. This is why the pattern needs the transaction in the first place, without it, the statements that already ran before the failure would stay applied, exactly the half-finished state atomicity exists to prevent.

SAVEPOINT for a Partial Rollback

If only one step of a larger transaction is allowed to fail without discarding the rest, a SAVEPOINT marks the point to roll back to instead of the very start, covered in the first lesson of this module. Useful for an optional step, like logging the reassignment to an audit table, that shouldn't block the reassignment itself if it fails.

The Real Cost of a Long-Running Transaction

An open transaction isn't free to leave sitting idle. It holds locks that can block other sessions, how much depends on the isolation level. On PostgreSQL and Oracle specifically, both MVCC-based, an old row version can't be cleaned up as long as any transaction that started before it's still open, might still need to read that version. A transaction left open across a slow network round trip, waiting on user input, is a common real cause of production slowdowns and table bloat, not a theoretical concern.

Try It Yourself

Write the rollback-on-error version of this module's reassignment transaction on MySQL: if the INSERT fails after the DELETE already succeeded, issue ROLLBACK instead of COMMIT.

START TRANSACTION;

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

-- If the next statement fails, the application issues ROLLBACK; here instead of
-- continuing, undoing the DELETE above so the assignment is never left missing.
INSERT INTO project_assignments (employee_id, project_id)
VALUES (18, 4);

COMMIT;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Open a transaction right before the statements that need it, and close it (COMMIT or ROLLBACK) right after, don't hold it open across anything slow.
• Give every code path through a transaction exactly one ending, COMMIT or ROLLBACK, including the error path.
• Use SAVEPOINT for a genuinely optional step inside a larger transaction, not as a substitute for keeping the transaction itself short.

Interview Questions

Summary

A real transaction wraps exactly the statements that need to succeed or fail together, follows the rollback-on-error pattern on every code path, uses SAVEPOINT only for a genuinely optional inner step, and stays open for as short a time as possible, since an idle open transaction has a real cost in locks and, on MVCC-based databases, in cleanup blocked behind it.

That completes Module 14, Transactions and ACID: COMMIT/ROLLBACK/SAVEPOINT, the four ACID properties, isolation levels and their real per-database defaults, and the practical patterns for using a transaction well.

What's Next?

Module 15, Advanced Aggregate Functions, is next: the database-specific and statistical aggregates beyond COUNT/SUM/AVG, string aggregation, array/JSON aggregation, variance, percentiles, and more.