Transactions — ACID, commit, and rollback

"In a bank transfer, the debit succeeded but the credit failed." The worst outcome here isn't that processing stops — it's money vanishing, with data left in that state.

A transaction is a mechanism that makes multiple operations all-succeed-or-all-not-happen. It's a tool for leaving no in-between state.

What you'll learn in this chapter

• The three basic transaction operations
• What each of the four ACID properties guarantees
• What rollback undoes
• Whether commits can be undone
• When SQL errors are detected

Prerequisites

If you have written SQL like INSERT or UPDATE, you're ready.

What this chapter does not cover

Topic	See
Introductory ACID explanation and exercises	Database Design Guide — CRUD and ACID
Framework-level implementation and the layer to hold the transaction	Laravel × DDD Guide — Transaction management
Retry decisions, count, and interval	Retries and idempotency
Concurrency control and retries in practice	Laravel API Development Guide — Transactions and concurrency
How atomicity breaks after distributing across machines	Data distribution
Writing queries that produce one answer from multiple tables	Joins and aggregation

Three basic operations

Only three operations are used for transactions.

BEGIN;                                              -- Start

UPDATE accounts SET balance = balance - 10000 WHERE id = 1;
UPDATE accounts SET balance = balance + 10000 WHERE id = 2;

COMMIT;                                             -- Confirm
-- or ROLLBACK;                                     -- Undo

Operation	Meaning
Begin (BEGIN / START TRANSACTION)	Treat from here as a single unit
Commit (COMMIT)	Confirm the changes up to here
Rollback (ROLLBACK)	Undo the changes since begin

That's the basic set. There's also savepoints for marking places and partially reverting, but the three operations are the base.

ACID

The four properties transactions guarantee. Called ACID after the initials.

Property	Guarantees	If broken
Atomicity	All succeed or all not happen	Only the debit lands and money vanishes
Consistency	Moves from a constraint-satisfying state to another	Orders under nonexistent users appear
Isolation	Not affected by concurrent transactions (how much is actually prevented depends on the isolation level)	Half-done values are read from outside
Durability	Committed results survive failures	Completion notice sent, then wiped by a restart

Atomicity — "all or nothing"

The transfer example at the start. If one of the two UPDATEs fails, the other is also un-done. The state "only one succeeded" doesn't exist.

Atomicity is also a mechanism for automating cleanup on failure. Without it, every detected failure needs code that manually records "what changed up to here" and reverses it.

Durability — committed stays

Once COMMIT returns, the change survives failures. Databases achieve this by writing the change to a durable log on disk before returning completion.

Because of this guarantee, "commits can't be undone" follows as a constraint. If they could, the "confirmed" promise would mean nothing.

What rollback undoes

This is the most misunderstood point.

Rollback undoes only changes made by that transaction. Operations by other users or other sessions at the same time are untouched.

Session A's rollback doesn't touch what session B committed. It is not "undo every operation by every user from the transaction's start until now".

If it were, one rollback would wipe everyone's work — a system many people can use concurrently wouldn't work. Transactions are actually the mechanism for isolating each session's work from the others.

Commits can't be undone

A committed transaction cannot be rolled back later by specifying its ID.

The reason is durability. Commit is the declaration "this result is confirmed", and other transactions start acting on that result. Enabling later undo would cascade back through processes that read the confirmed value and made subsequent decisions — untenable.

If you want to reverse post-commit, run a canceling operation as a new transaction.

Intent	Method
Undo the last update	Run a new transaction that sets the values back
Accounting cancellation	Add a reversing entry (don't delete the original)
Restore to a point in time	Restore from backup, or PITR

In domains where history matters — accounting, inventory — pick a design that never deletes or overwrites, and instead appends cancellation records. The fact "we canceled" is itself required as a record.

When syntax errors are detected

There are stages when SQL errors are found.

Stage	Detected
At statement issue	Syntax errors, nonexistent tables or columns, type mismatches
At statement execution	Unique constraint violation, foreign key violation, NOT NULL violation
At commit	Deferred constraint (DEFERRABLE) violations

Syntax errors normally return the moment the statement is issued. The state "syntax error only shows up at commit" doesn't occur.

But some constraint checks can be deferred to commit. PostgreSQL lets unique, primary key, foreign key, and exclusion constraints be declared DEFERRABLE INITIALLY DEFERRED, in which case they're checked together at commit. NOT NULL and CHECK cannot be deferred and are always checked at statement execution.

-- Declare the constraint as DEFERRABLE up front (without this, deferral doesn't apply)
ALTER TABLE a ADD CONSTRAINT a_b_fk FOREIGN KEY (b_id) REFERENCES b(id)
  DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE b ADD CONSTRAINT b_a_fk FOREIGN KEY (a_id) REFERENCES a(id)
  DEFERRABLE INITIALLY IMMEDIATE;

-- Insert into two mutually referencing tables in order
BEGIN;
SET CONSTRAINTS a_b_fk, b_a_fk DEFERRED;
INSERT INTO a (id, b_id) VALUES (1, 1);  -- b doesn't exist yet
INSERT INTO b (id, a_id) VALUES (1, 1);
COMMIT;                                   -- Verify both foreign keys here

SET CONSTRAINTS only affects constraints declared as DEFERRABLE. Constraints without the declaration are checked at statement execution even with this directive.

So "even if issuing SQL succeeded, commit can still produce an error" holds. But those aren't syntax errors — they're deferred constraint violations. Pinning this down makes reading error messages a lot easier.

Where to use them

Transactions aren't a hammer. Kept open for long, they hold locks and make others wait.

Do	Don't
Bundle related updates into one	Call external APIs inside a transaction
Keep it short	Keep it open while waiting for user input
Rollback reliably on failure	Update a huge number of rows in one transaction

Don't call external APIs inside a transaction is an important principle. External slowness holds the lock the whole time, and external calls can't be rolled back anyway. "Sent the email then rolled back" — the email doesn't come back. How long locks are held is covered by Locks and isolation levels.

Common misconceptions

"Transactions can stay open a long time without issue." — Locks are held; others wait. Keep them short as a rule.

"Syntax errors never appear at commit." — Syntax errors appear at issue, but deferred constraint violations appear at commit. "Issue succeeded but commit could still error" does hold.

"Rollback undoes every operation by every user from the transaction's start." — It undoes only that transaction's changes. Other sessions' committed changes aren't affected.

"If you know the transaction ID, you can roll back after commit." — You can't. Commit is the confirmation, in conflict with durability. Execute a canceling operation as a new transaction.

"Autocommit means transactions aren't used." — Each statement is wrapped in an implicit transaction. You just didn't wrap it explicitly; a single statement still executes atomically.

Exercises

Q1. Ops asks "at 15:00 yesterday I accidentally ran a bulk update. Please roll back that transaction." What do you answer?

Answer and explanation

Answer: If committed, it can't be rolled back. Execute a canceling operation as a new transaction, or restore from backup.

Frame the explanation:

1. Commit is the confirmation. With durability guaranteed, "unmaking it" isn't possible afterward. If it were, subsequent processes that read the result would need to be rolled back too
2. What can be done — if you know the previous values, run an update to restore them as a new transaction. If you don't, consider a backup restore or point-in-time recovery (PITR)
3. On record-keeping — in accounting or inventory, where history matters, keep a design that doesn't overwrite but appends cancellation entries, so this ask is served directly

Note that an uncommitted transaction can still be rolled back with ROLLBACK. Even then, only that transaction's changes are undone; other users' concurrent commits aren't touched.

Q2. Someone says "with transactions, all SQL errors are visible the moment they're issued". Is this valid?

Answer and explanation

Answer: Not valid. Some errors first appear at commit.

Syntax errors and references to nonexistent tables do return the moment the statement is issued. But constraint checks such as foreign keys can be deferred to commit.

BEGIN;
SET CONSTRAINTS a_b_fk, b_a_fk DEFERRED;  -- Applies to constraints declared DEFERRABLE
INSERT INTO a (id, b_id) VALUES (1, 1);   -- b doesn't exist yet, but this passes
INSERT INTO b (id, a_id) VALUES (1, 1);
COMMIT;                                    -- Both foreign keys verified here

When inserting into mutually referring tables in order, an interim state may temporarily violate the constraint. Deferred constraints (DEFERRABLE INITIALLY DEFERRED) allow that and verify at commit.

So "issuing succeeded but commit can still error" holds. But that error is a deferred constraint violation, not a syntax error. From the timing of the error, you can narrow the kind of cause, so this distinction is worth remembering.

Q3. After confirming an order, you call an external payment API. Should this call be inside the transaction?

Answer and explanation

Answer: It should not.

Two reasons.

1. External calls can't be rolled back. Rolling back a transaction doesn't retract the request sent to the payment API. You end up with no order in the database but payment settled anyway
2. Long-held locks. If the external API is slow, row locks are held that whole time. Other processes touching the same rows wait

The realistic design closes the transaction, then calls externally, then reflects the result in a separate transaction.

[Tx1] Save the order as "pending payment" → COMMIT
      Call the payment API
[Tx2] Update to "confirmed" or "failed" based on result → COMMIT

Even if things crash midway, the "pending payment" order remains, so reconciliation can recover it later. Making the intermediate state explicit is the idiom for reconciling with external systems.

Q4. Session A is updating stock from 10 to 5 (uncommitted); session B updates the product name of a different item and commits. Then A rolls back. What happens to the name?

Answer and explanation

Answer: The name stays as B updated it.

A's rollback only undoes A's own stock update. Stock returns to 10, but B's committed name isn't touched.

Because of this isolation, many users can work the same tables at once. If rollback rewound "every operation up to now", one person's failure would wipe everyone's work.

If A and B tried to update the same row, the story changes and locking makes one wait — even when the columns differ. That's covered in the next chapter.

Summary

• Transaction basics are three operations: begin, commit, rollback
• ACID is atomicity, consistency, isolation, durability
• Rollback undoes only your transaction's changes and doesn't touch other users' operations
• You cannot roll back after commit. To reverse, run a canceling operation as a new transaction
• Syntax errors show at issue, but deferred constraint violations show at commit
• Keep transactions short. Don't call external APIs inside them, as a rule

Related references

• Database Design Guide — CRUD and ACID — Introductory ACID explanation and exercises
• Laravel × DDD Guide — Transaction management — Which layer to place the transaction on
• Retries and idempotency — Failure classification and retry design
• Laravel API Development Guide — Transactions and concurrency — Concurrency control and retries in practice

What to read next

• Locks and isolation levels — What happens when multiple transactions run at once