ACID Transactions in Distributed Databases: DynamoDB, Cosmos DB, and Spanner Compared

Scenario context

ACID transactions in distributed databases are not equal. DynamoDB provides multi-item atomicity scoped to 25 items using two-phase commit with a coordinator item, but only within a single region. Cosmos DB wraps partition-scoped operations inside a stored procedure using a JavaScript continuation model — cross-partition writes get no atomic guarantee. Google Spanner is the only cloud database that delivers true glo…

This walkthrough focuses on The Write That Silently Disappeared.

Before making a design choice, clarify:

• A payments team at a mid-size fintech company migrated their account ledger service from PostgreSQL to DynamoDB.
• In PostgreSQL, every funds transfer ran inside a BEGIN / COMMIT block: debit the source account, credit the destination account, write a transaction record — three rows, one atomic commit.
• The team ported the logic verbatim to DynamoDB and deployed.

Concepts in play: transaction-isolation-distributed-acid, consistency-replication, event-driven-architecture, caching

Stage 1 — ACID Unpacked for Distributed Systems: What Each Letter Means When There Is No Single Machine

• The ACID acronym was coined for single-node relational databases.
• Transplanted into distributed systems, each letter means something subtly different — and one of them barely means anything at all.
• Atomicity in a distributed context means all writes in a logical transaction either all commit or all abort, even when those writes span multiple nodes, partitions, or shards.
• On a single machine, "all or nothing" is enforced by the storage engine via write-ahead logging — a crash before the WAL is flushed is a rollback, a crash after is a commit.

Observable outcome: On a single machine, "all or nothing" is enforced by the storage engine via write-ahead logging — a crash before the WAL is flushed is a rollback, a crash after is a commit.

Stage 2 — DynamoDB TransactWriteItems: Two-Phase Commit Without Calling It Two-Phase Commit

• Amazon DynamoDB added transactional APIs in late 2018 via TransactWriteItems and TransactGetItems .
• The documentation describes ACID guarantees for up to 25 items across up to 25 tables within a single AWS region.
• What the documentation does not say is that these APIs implement a variant of two-phase commit backed by a coordinator item stored in DynamoDB's own infrastructure.
• How the Coordinator Item Orchestrates 2PC When a client calls TransactWriteItems , DynamoDB's transaction layer writes a transaction record — a special metadata item tracked internally — with status PENDING and the full…

sequenceDiagram
    participant Client
    participant Coordinator
    participant PartitionA as Partition A
    participant PartitionB as Partition B

    Client->>Coordinator: TransactWriteItems with ClientRequestToken
    Coordinator->>Coordinator: Write PENDING transaction record
    Coordinator->>PartitionA: PREPARE - lock item, evaluate condition, tentative write
    Coordinator->>PartitionB: PREPARE - lock item, evaluate condition, tentative write
    PartitionA-->>Coordinator: PREPARED
    PartitionB-->>Coordinator: PREPARED
    Coordinator->>Coordinator: Update transaction record to COMMITTED
    Coordinator->>PartitionA: COMMIT - apply write, release lock
    Coordinator->>PartitionB: COMMIT - apply write, release lock
    PartitionA-->>Coordinator: ACK
    PartitionB-->>Coordinator: ACK
    Coordinator-->>Client: TransactionSucceeded

Observable outcome: How the Coordinator Item Orchestrates 2PC When a client calls TransactWriteItems , DynamoDB's transaction layer writes a transaction record — a special metadata item tracked internally — with status PENDING and the full…

Stage 3 — Cosmos DB Partition-Scoped Transactions: When JavaScript Is the Transaction Boundary

• Azure Cosmos DB supports multi-document transactions, but under a constraint that surprises most developers: all documents in a transaction must share the same logical partition key .
• There is no cross-partition atomic write in Cosmos DB.
• Understanding why requires understanding what the transaction boundary actually is.
• Stored Procedures as the Transaction Unit Cosmos DB's transaction boundary is the stored procedure execution .

flowchart TD
    A[Client sends stored procedure call] --> B{All document operations use same partition key?}
    B -->|No - different partition keys| C[Request rejected at routing layer]
    B -->|Yes| D[Route to owning partition primary replica]
    D --> E[Execute stored procedure JavaScript on primary]
    E --> F{JavaScript throws uncaught exception?}
    F -->|Yes| G[Engine rolls back all ops in this invocation]
    F -->|No| H[Commit - all mutations visible atomically]
    G --> I[Return error to client]
    H --> J[Return success to client]

Observable outcome: Stored Procedures as the Transaction Unit Cosmos DB's transaction boundary is the stored procedure execution .

Stage 4 — Google Spanner and Global ACID: The Engineering Cost of External Consistency

• Google Spanner is the only database in this comparison that delivers true global ACID transactions across any number of rows, tables, and geographic regions simultaneously.
• This is not a marketing claim.
• It is an engineering achievement built on physical infrastructure most databases do not have.
• TrueTime: Bounding the Unknowable Every distributed database that uses timestamps for ordering faces the clock synchronization problem : two nodes' clocks can drift by milliseconds or more.

sequenceDiagram
    participant Client
    participant Leader as Spanner Partition Leader
    participant TrueTime as TrueTime API

    Client->>Leader: Begin read-write transaction
    Client->>Leader: Read rows R1, R2 - shared locks acquired
    Client->>Leader: Write mutations M1, M2
    Leader->>Leader: Acquire exclusive write locks on M1, M2
    Leader->>TrueTime: TT.now() - request current time interval
    TrueTime-->>Leader: Interval [t_earliest, t_latest]
    Note over Leader: Assign commit timestamp s = t_latest
    Leader->>Leader: Commit-wait - sleep until TT.now().t_earliest is greater than s
    Leader->>Leader: Apply writes atomically with timestamp s
    Leader->>Leader: Release all read and write locks
    Leader-->>Client: Commit ACK with timestamp s

Observable outcome: TrueTime: Bounding the Unknowable Every distributed database that uses timestamps for ordering faces the clock synchronization problem : two nodes' clocks can drift by milliseconds or more.

Stage 5 — MongoDB Multi-Document Transactions: Oplog-Based Atomicity With a Sharding Caveat

• MongoDB added multi-document transactions in version 4.0 for replica sets (2018) and extended them to sharded clusters in version 4.2 (2019).
• MongoDB's transaction model builds on its existing replication infrastructure — the oplog (operations log) — and shares conceptual ancestry with Spanner's 2PC, though with higher overhead in the sharded case.
• Replica Set Transactions: The Oplog as the Commit Record On a replica set, all read and write operations in a transaction execute on the primary node.
• Reads use a consistent point-in-time snapshot of the data (MVCC-based), meaning reads inside a transaction never see commits from concurrent transactions — only data as it existed when the transaction began.

Observable outcome: Reads use a consistent point-in-time snapshot of the data (MVCC-based), meaning reads inside a transaction never see commits from concurrent transactions — only data as it existed when the transaction began.

Stage 6 — Deep Dive: How Distributed ACID Is Actually Enforced Under the Hood

• Understanding the mechanics of each database's transaction implementation requires examining three layers: the concurrency control model, the mathematical guarantees it formalizes, and the performance cost those guarant…
• The Internals: Concurrency Control Models Compared Each database uses a different concurrency control strategy to achieve isolation, and that strategy determines both its failure modes under contention and its performan…
• DynamoDB uses optimistic concurrency control (OCC) with condition expressions.
• Rather than locking items during the transaction's lifetime, DynamoDB validates conditions at PREPARE time — atomically checking version numbers, attribute existence, or value predicates at the point of tentative write.

flowchart TD
    DB["Database
(WAL/Log)"]
    Capture["Capture Engine"]
    Transform["Transform"]
    Sink["Sink/Destination"]

    DB -->|"Change Log"| Capture
    Capture -->|"CDC Event"| Transform
    Transform -->|"Normalized"| Sink

Observable outcome: Rather than locking items during the transaction's lifetime, DynamoDB validates conditions at PREPARE time — atomically checking version numbers, attribute existence, or value predicates at the point of tentative write.

End-to-end outcome

After working through the stages, you should be able to explain:

• Two sequential writes are never a transaction.
• The most common distributed transaction bug is issuing two independent write calls and assuming they are atomic because they run in the same Lambda or service method.

Alternatives considered

Isolation Levels Compared: Which Anomalies Each Database Prevents for Transactional Reads

The following table maps each database's transactional API to its effective isolation level and the anomalies it prevents.

Side-by-Side Comparison: Atomicity Scope, Isolation, Latency, and Cost

Side-by-Side Comparison: Atomicity Scope, Isolation, Latency, and Cost

Failure and degraded modes

Trade-offs and Failure Modes Across All Four Databases

• Detection: Performance versus correctness.
• User or operator impact: Performance versus correctness.