Study guides · Intermediate

Choose a transaction boundary and handle concurrent updates

Separate atomicity from isolation, then use an inventory update to reason about rollback, conflicts, retries, and uncertain commit outcomes.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Do not commit an incomplete operation
  2. Work through the last item in stock
  3. Atomicity is not the same as isolation
  4. A missing commit response leaves uncertainty
  5. Trace a conditional decrement across two sessions
  6. Ask Logeia about this articleCheck your understanding

Do not commit an incomplete operation

An order and its inventory decrement often belong together. Recording only the order when inventory modification fails leaves an incomplete business operation. A transaction groups database work that should commit together or be rolled back together. Begin with the roles of BEGIN, COMMIT, and ROLLBACK before treating a transaction as a generic safety label.

A longer boundary is not always better. Waiting for an external service while keeping a transaction open can hold locks or old snapshots and affect other work. Sending an email also does not become reversible merely because application code called it between BEGIN and COMMIT. Identify the resources governed by the database boundary.

Work through the last item in stock

Two buyers can both read stock equal to one and decide to purchase. Wrapping each client operation in a transaction does not, by itself, solve every concurrent-update problem. Reading behavior, update conditions, locks, and the isolation level determine what can happen.

One approach is a conditional decrement that requires stock > 0 and checks whether RETURNING produced an updated row. Only then should the application proceed with the matching order logic. If no row was updated, it must not silently create a confirmed order. Waiting and rechecking behavior still depends on the database and isolation level.

sql
BEGIN;
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 7 AND stock > 0
RETURNING product_id, stock;
-- Create the order only after checking the update result.
COMMIT;

Atomicity is not the same as isolation

Atomicity concerns committing or undoing the grouped changes. Isolation concerns how concurrent transactions observe and interact with one another. Correct behavior in a single-client test does not establish correct behavior under concurrent purchases. Test the race as well as the isolated function.

Even when serializable behavior is requested, a conflicting transaction may fail and require an application retry. Define the complete transaction to restart and the side effects that are safe to repeat. Hiding a conflict error and showing success does not turn the failed transaction into a completed business operation.

A missing commit response leaves uncertainty

If the connection fails after COMMIT was sent, the client may not know whether the transaction committed. Assuming that every connection error means “nothing happened” can create duplicate orders on retry. Use a unique business identifier and a way to query the result.

Durability also rests on configuration and storage assumptions. Writing a log record is not a substitute for checking the documented synchronization and recovery behavior of the deployed system. Transactions provide a strong database boundary, but consistency across external services still needs an explicit design rather than a promise attached to one keyword.

Trace a conditional decrement across two sessions

Consider two PostgreSQL Read Committed sessions targeting stock initially equal to one. Session A conditionally decrements it to zero but has not committed. If B finds that target row and conflicts with A’s update, it waits. When A commits, B rechecks its condition against the updated row. stock > 0 is false, so B returns no updated row and must not confirm this purchase.

If A rolls back, B can proceed against the original row instead. Other isolation levels can produce a serialization failure rather than this outcome. Record isolation, update order, commit positions, and returned row counts when testing. External payment or email effects do not become reversible through database rollback; their handoff and duplicate prevention require a separate design.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 13

Choose a learning path