Topic: PostgreSQL

Transaction Boundaries for Orders, Inventory, and ACID

An order can touch inventory, orders, and payment records. A transaction keeps database changes all-or-nothing; it cannot unsend an email or replace constraints and concurrency design.

“Decrease inventory, create an order, record a payment” sounds like three small jobs. Run them separately, and they can leave an awkward half-state: inventory changed but no order exists, or a payment record exists before a later SQL statement fails.

A transaction groups database changes that describe one fact into one outcome: they all happen, or none of them do. It cannot invent rules an application never defined.

PostgreSQL’s transaction tutorial uses a transfer to illustrate this problem: several updates must succeed together, and concurrent transactions should not see a state where money has left one account but not reached the other.

First, decide which data describes one fact

Suppose a product has one unit left. A successful checkout needs at least two database actions: reserve the inventory and create the order. A payment authorization record or a pending delivery notice can also be part of the same fact: this order was accepted.

BEGIN;

UPDATE inventory
SET quantity = quantity - 1
WHERE sku = $1
  AND quantity > 0
RETURNING sku;

-- Continue only when one row returns; otherwise ROLLBACK.
INSERT INTO orders (customer_id, sku)
VALUES ($2, $1);

COMMIT;

This SQL has two separate responsibilities. The UPDATE puts “inventory remains” into the write condition, avoiding a gap between reading availability and decrementing it. BEGIN through COMMIT then binds the reservation and order creation together. If the INSERT fails, the transaction cannot commit and the application should finish with ROLLBACK, leaving no earlier decrement behind.

Without an explicit BEGIN, PostgreSQL still executes every statement in a transaction and commits each successful statement. That is convenient for one UPDATE, but it does not merge the following INSERT into the same unit. Use an explicit transaction block when several statements must advance or retreat together.

The four responsibilities in ACID

ACID describes a transaction from four related angles. Each property has a separate job.

• Atomicity: a group of changes all happens or none does. A charge recorded without an order is first a broken atomic boundary.
• Consistency: a committed state still obeys declared data rules, such as CHECK (quantity >= 0), foreign keys, or unique constraints. A database cannot infer every product rule; a rule not encoded in a constraint or transaction flow still needs explicit application logic.
• Isolation: concurrent transactions should not see each other’s half-finished states. That does not make every concurrency problem disappear; it prevents an intermediate step from being treated as completed fact.
• Durability: after the database confirms COMMIT, a change should survive a crash. This applies to committed database data, not to whether a third-party service can undo its work.

CHECK, UNIQUE, NOT NULL, and foreign keys are concrete consistency tools. Transactions solve a different problem: several individually valid changes must not leave only some of their effects behind.

Isolation prevents half-states; it does not erase concurrency

When two people try to buy the last unit at once, a risky flow is often a SELECT asking whether stock remains, followed later by a separate UPDATE. Between those statements, the answer can change.

UPDATE ... WHERE quantity > 0 RETURNING puts the availability test and decrement in one database operation. One returned row means this reservation succeeded; no row means no order should be created. For a simple, predetermined row, that is more reliable than application code that first reads and then writes.

PostgreSQL’s Transaction Isolation documentation distinguishes this kind of simple update from complex rules that span several rows. The latter can need a higher isolation level, a locking strategy, or a full retry after a serialization failure. Seeing BEGIN is not proof that every race condition is solved.

Keep data rules in constraints, then group writes for one fact in a transaction. Design read-then-write races with database conditions, locks, or isolation when they appear.

ROLLBACK cannot unsend an email

A transaction controls only changes in that database transaction. If it calls a payment API, sends an email, or uploads a file before COMMIT, a later ROLLBACK cannot rewind the external service.

That is why “write the order, then send an email” often needs two boundaries. First, write the order and a trackable pending record in a short transaction:

BEGIN;

WITH created_order AS (
  INSERT INTO orders (customer_id, sku)
  VALUES ($1, $2)
  RETURNING id
)
INSERT INTO outbox (kind, order_id)
SELECT 'order-confirmation', id
FROM created_order;

COMMIT;

A worker can then read outbox, send the email, and record its result. The row records the system’s intent to send; it does not guarantee exactly one email. Failures still need observation and retries. Idempotency, retries, and failure handling for external side effects are the next layer of design, and they should not be hidden inside a transaction held open for a network call.

What I learned

• I can identify which database changes describe one fact before choosing a transaction boundary, rather than mechanically adding BEGIN around every query.
• Atomicity prevents partial multi-step writes. Consistency requires that committed data still obey declared rules; neither replaces the other.
• One conditional UPDATE can close a read-then-write race for simple inventory. Multi-row or global rules still need deliberate concurrency control and retries.
• ROLLBACK only cancels database changes. Emails, payments, and uploads need observable, retryable flows of their own.

Conclusion

Transactions are valuable because they let a database give a clear answer to “did this fact happen?” Define the shared fact first, use constraints to guard data rules, use transactions to contain multi-step writes, then choose locks or isolation for the concurrency cases that remain.

External references

• PostgreSQL: Transactions
• PostgreSQL: Transaction Isolation
• PostgreSQL: Data Consistency Checks at the Application Level
• Reddit: Beginner discussion of ACID and isolation

Further learning

• CS50 SQL Week 5: Optimizing is Harvard CS50’s official video lesson covering transactions, ACID, race conditions, and locks.