An order service needs to reserve inventory, record a payment reference and publish an event. Those effects can cross databases and messaging services. Establish which system owns each invariant and which effects can commit together before choosing a transaction mechanism.
Workload types and transaction properties
Online transaction processing (OLTP) handles short record-oriented operations, such as placing an order. Online analytical processing (OLAP) scans and aggregates many records for reporting. Hybrid transactional/analytical processing (HTAP) combines both access patterns within a platform, with implementation-specific isolation of analytical work. These are workload categories; an analytical table can still have transactional commits.
Atomicity, consistency, isolation and durability (ACID) describe guarantees within the transaction scope. The SQL transaction reference defines the four properties with commit and rollback examples. Here the design question is which effects can share that scope.
ACID consistency concerns invariants. Distributed-replica consistency concerns what reads observe across copies. A system may enforce one scope while exposing stale reads in another. Document the guarantee and the failure condition.
Eventual consistency and partitions
Some derived read models update asynchronously after an authoritative transaction. A client can therefore observe old state until propagation catches up. The BASE shorthand (Basically Available, Soft state, Eventual consistency) describes asynchronous, convergent state. Ordering, convergence and durability still need an explicit protocol.
Define acceptable staleness, read-your-writes behavior and how duplicate, reordered or deleted events converge. A wallet balance may require an authoritative read even when a recommendation profile can tolerate a delayed projection.
The consistency, availability and partition-tolerance (CAP) trade-off concerns coordination across replicas during a network partition. Under a partition, a distributed system cannot promise both linearizable operations and availability for all requests. The design still needs to specify normal-operation latency and which operations wait or reject when coordination is unavailable.
Isolation and concurrent writers
PostgreSQL Serializable can reject a transaction when concurrent work cannot be safely ordered. Callers need a whole-transaction retry, including decisions based on reads. See isolation levels and anomalies for the level-by-level behavior.
Multi-version concurrency control (MVCC) lets readers use consistent record versions while writers create newer ones. Conflicting writes can still wait or fail. Long-running transactions can retain old versions and create maintenance pressure.
An optimistic update can require WHERE entity_id = ? AND version = ? and increment the version. Zero affected rows means the expected state no longer matches. Re-read and decide whether the business operation remains valid; blindly replaying stale arguments can violate the invariant.
The SQL transaction chapter provides savepoint, isolation and locking examples.
Table commits and publication scope
A lakehouse writer can prepare data files, validate a candidate table change and commit a new metadata version. Readers following the table protocol see the committed snapshot. Directly listing files bypasses that interpretation and may expose abandoned or superseded files.
A table commit publishes that table’s change. Publishing several marts, an external API and a message together requires a larger protocol supported by the catalog or runtime. For a set of related outputs, a release manifest can identify the snapshots consumers should read together.
Two-phase commit, sagas and transactional outbox
Two-phase commit (2PC) prepares participating resources, then commits them under a coordinator’s decision. Prepared participants may hold resources while awaiting resolution. All participants must support the protocol; an ordinary HTTP request is not automatically enlisted.
A saga coordinates local transactions with compensating actions. Refunding a payment compensates a prior charge; it does not erase the original business event or guarantee that a notification was never observed. Compensation can fail and needs its own durable status.
A transactional outbox commits the business record and an event record in one local transaction, then a relay publishes the event:
Swipe or scroll sideways to read the full diagram.
The local transaction commits order state and an outbox record together. A relay publishes committed events and can retry. Consumers deduplicate stable event IDs.
The relay can publish successfully and crash before recording progress. Duplicate delivery remains possible, so consumers use stable event IDs and idempotent writes. A transactionally published event also needs a meaningful order or source version if later updates can arrive first.
Choosing transaction scope
Use a local transaction when the operation fits one transactional store. Consider supported distributed transactions when a strict multi-resource atomic invariant justifies coordination. Use outbox delivery for reliable propagation from committed state. Use a saga when a business process tolerates intermediate states and compensation.
Fail closed when required authorization or monetary invariants cannot be checked. For analytical publication, retaining the last verified release can be safer than exposing a partially refreshed set; label its freshness so consumers can make an informed decision.