Atomic Update Patterns

Atomic update patterns make state transitions indivisible so concurrent workers do not overwrite one another's changes. They rely on database-side updates, transactions, constraints, or locks chosen to protect the specific invariant.

An atomic update pattern expresses a state transition so that competing operations cannot observe and overwrite an incomplete intermediate result. The key idea is to let the component that owns the state—often a relational database—apply the change as one coordinated operation.

A common failure is reading a value, changing it in Python, then saving it. If two requests read the same value before either writes, one result can erase the other's update. Prefer a conditional or relative database update, such as `UPDATE accounts SET balance = balance - ? WHERE id = ? AND balance >= ?`, and check the affected-row count to determine whether the condition held.

Transactions group multiple statements into a unit that commits together or rolls back. They do not automatically prevent every race: choose suitable isolation, locking, constraints, or conditional writes for the invariant. For example, a unique constraint can arbitrate creation of a row that must exist only once.

For multi-step business actions, keep the transaction short and include all database changes needed to preserve the invariant. External effects such as sending email or charging a remote service are not made atomic by a database transaction; use patterns such as an outbox when those effects must reliably follow a committed change.

Key points

• Prefer database-side relative or conditional writes over application-side read-modify-write.
• Use transactions when a state transition spans multiple database operations.
• Use constraints and affected-row counts to enforce and detect outcomes.
• Keep remote side effects outside the assumption of database atomicity.

Interview questions

What is a lost update, and how would you prevent one?

A lost update occurs when two workers read the same old value, independently compute a new value, and then write it. The later write can overwrite the earlier one, so one change disappears. I avoid this by expressing the change in the database, using a conditional update, or locking the row inside a transaction. I also check the update result and enforce important invariants with constraints where possible.

How would you safely reserve one unit of inventory under concurrent requests?

I would encode the precondition in the write itself, for example `UPDATE inventory SET quantity = quantity - 1 WHERE sku = ? AND quantity > 0`. Then I inspect the affected-row count: one row means the reservation succeeded, while zero means the item was unavailable or the row did not match. This avoids relying on a stale quantity read by the application. A constraint can provide additional protection for invariants that the schema can express.

How do you choose between a conditional update, locking, and stronger transaction isolation?

First identify the invariant and the scope of state it covers. If a single conditional statement can preserve it, that is usually simpler than a multi-step protocol; otherwise, use a transaction with appropriate isolation or explicit row locking, and verify behavior on the actual database. Unique and check constraints are valuable because they protect data regardless of which application path writes it. I would also consider retries for serialization or deadlock failures, while ensuring the operation is safe to retry.

Why does a database transaction not make an email or API call atomic with a database update?

A transaction can atomically commit or roll back participating database changes, but it cannot generally include a remote service in the same commit protocol. If the database commits and the process crashes before sending the message, the external action may be missing; if it sends first and the transaction rolls back, the action may be incorrect. An outbox stores the intent in the same database transaction, and a separate worker delivers it. Delivery is commonly at least once, so consumers should be idempotent or deduplicate.