BEGIN, COMMIT, ROLLBACK
Group statements into one all-or-nothing unit, and undo them before they're permanent.
Syntax
BEGIN;
-- one or more statements
COMMIT; -- make them permanent
-- or:
ROLLBACK; -- undo everything since BEGIN| Parameter | Type | Notes |
|---|---|---|
| BEGIN* | statement | Starts a transaction. Everything after it is staged, not permanent, until
a |
| COMMIT | statement | Makes every staged change permanent and visible to other connections. |
| ROLLBACK | statement | Discards every staged change since |
| SAVEPOINT name | statement | A named point inside a transaction you can roll back to without undoing
the whole thing. |
Why: all-or-nothing
A transfer between two accounts is the classic example: debit one row, credit another. If the process dies after the debit but before the credit, money just vanished. Wrapping both statements in one transaction means either both happen or neither does — there's no in-between state to observe.
ROLLBACK undoes everything since BEGIN
Loading editor…
Both balances are back to 0 — the ROLLBACK discarded both updates as one
unit, exactly as if BEGIN had never run.
COMMIT makes it permanent
Loading editor…
Same two statements, but COMMIT instead of ROLLBACK — the money actually
moved, and net balance across both rows is still zero either way.
SAVEPOINT: rolling back part of a transaction
Loading editor…
Balance is 50, not -9949 — the savepoint rolled back only the risky part.
The first update and the COMMIT still went through. Useful for "try this,
and undo just this part if it turns out wrong" inside a larger transaction
you still want to keep otherwise.
When a statement inside a transaction fails
BEGIN;
INSERT INTO accounts VALUES (3, 'New Co', 0);
INSERT INTO accounts VALUES (3, 'Duplicate id', 0); -- fails: primary key violation
COMMIT;The second INSERT fails, but the transaction doesn't roll back on its own —
most engines leave the session sitting inside an open, now-unusable
transaction until something explicitly issues a ROLLBACK. Postgres is the
strictest about this: any further statement in that session errors with
"current transaction is aborted" until you roll back. Always have error
handling that calls ROLLBACK on failure — don't assume the engine does it
for you.
Gotchas
- Autocommit is the default. Outside an explicit
BEGIN, every statement is its own transaction that commits immediately — including theUPDATE/DELETEexamples on the previous pages.BEGINis what turns several statements into one unit. - DDL transactionality is engine-specific. Postgres and SQLite can roll
back a
CREATE TABLEorALTER TABLElike any other statement. MySQL cannot — DDL causes an implicit commit of whatever came before it, and can't itself be rolled back. SQL Server is transactional for most DDL but not all of it. Don't assume a schema change inside a transaction is undoable everywhere. - Open transactions hold locks. A transaction left open — waiting on
something, or just forgotten — can block other connections from reading or
writing the same rows (or, on Postgres, prevent
VACUUMfrom reclaiming dead rows) for as long as it stays open. Keep transactions short.
- Isolation levels — what a transaction can see of another one running concurrently.
- UPDATE — the "no WHERE" risk a transaction makes recoverable:
BEGIN, run it, check the result,COMMITorROLLBACK. - DELETE — same safety pattern; a transaction is the practical version of "run the SELECT first."
Safety checklist
- wrap multi-step changes in a transaction so a failure partway through can't leave data half-changed
- for a risky UPDATE/DELETE,
BEGIN, run it, inspect the result, thenCOMMITorROLLBACK— don't autocommit blind - always have an explicit
ROLLBACKon error — most engines don't roll back a failed statement for you - keep transactions short; an open one holds locks other connections wait on