10. Transactions, Concurrency and Data Integrity

Introduction and Context

NoSQL databases are often selected for horizontal scalability, flexible schemas, high availability, and efficient handling of large volumes of semi-structured data. These strengths, however, do not eliminate the need for transactions, concurrency control, and data integrity. They change the way these concerns are designed and implemented.

In a relational system, developers commonly rely on multi-row transactions, foreign keys, unique constraints, and a well-defined isolation model. In a distributed NoSQL system, equivalent guarantees may be narrower, configurable, or partly delegated to the application. A document database may provide atomicity for a single document but not automatically for several collections. A key-value store may offer conditional writes but no general-purpose join or transaction mechanism. A wide-column database may prioritize availability and partition tolerance over strong cross-row consistency.

The central engineering question is therefore not whether a NoSQL database supports transactions. It is: which integrity guarantees are required by the business operation, at what scope must they hold, and where should those guarantees be enforced? The answer may involve database transactions, optimistic or pessimistic concurrency, idempotent APIs, version checks, event-driven workflows, compensating actions, or explicit conflict-resolution policies.

This chapter examines these mechanisms as parts of one data-integrity strategy. It also considers distributed transactions, sagas, eventual consistency, operational risks, governance, and emerging techniques for building dependable applications on distributed data platforms.

Learning Objectives

After studying this chapter, the learner should be able to:

• distinguish atomicity, consistency, isolation, and durability in NoSQL environments;
• evaluate the transaction and isolation capabilities of different NoSQL approaches;
• select optimistic or pessimistic concurrency control for a given workload;
• design idempotent operations and explain their role in reliable distributed systems;
• reason about eventual consistency, conflicts, and conflict-resolution policies;
• compare distributed transactions, two-phase commit, sagas, and workflow-based alternatives;
• apply application-level integrity controls, validation, observability, and governance practices;
• analyse the trade-offs between correctness, availability, latency, scalability, and operational complexity.

Why Integrity Is a Distributed Systems Problem

Data integrity means that stored data remains accurate, complete, valid, and consistent with stated business rules. Some integrity rules are local: an order must have a valid status, a quantity must be positive, or a user identifier must be present. Other rules span records or services: an inventory item must not be oversold, a payment must not be captured twice, and a shipment must not be released before payment authorization.

Concurrency makes these rules difficult. Concurrent requests may read the same state and then make incompatible decisions. For example, two customers may attempt to purchase the final unit of a product. If both requests read stock as one and each writes stock as zero, the database may contain a plausible value while the business has accepted two sales. This is a lost-update or overselling problem.

Distribution adds further complications. A network may fail after a payment service has accepted a request but before the client receives the response. A replica may temporarily contain older data. A service may retry a message, producing duplicate effects. Two regions may update the same logical entity while disconnected. A technically valid database state may therefore still violate a business invariant.

A useful distinction is between database consistency and business consistency. Database consistency concerns whether an operation obeys the database's declared rules and transaction model. Business consistency concerns whether the overall application state satisfies domain policies. NoSQL systems can provide the former while requiring application architecture to achieve the latter.

ACID in NoSQL Systems

ACID describes four desirable transaction properties. They are not synonymous with relational databases, although relational systems traditionally made them prominent.

Atomicity means that a transaction is treated as one indivisible unit: either all its intended changes become visible or none do. In a document database, an update to one document may be atomic even when a multi-document transaction is unavailable. Atomicity must therefore be evaluated at a stated boundary.

Consistency means that a committed transaction preserves defined invariants. ACID consistency is not simply the same as strong read consistency. A strongly consistent read may return the latest value while the value itself violates a business rule. Conversely, a system may use eventual replication but still preserve local invariants through conditional writes.

Isolation concerns the visibility of concurrent operations. If one transaction reads data while another is changing it, the isolation level determines what the reader can observe and whether anomalies such as dirty reads, non-repeatable reads, phantom reads, or write skew are possible.

Durability means that committed data survives an acknowledged operation, subject to the system's documented failure model. Replication, write-ahead logging, quorum acknowledgements, storage protection, and backup policies influence practical durability. An acknowledgement from a single memory-resident node should not be treated as equivalent to durable, replicated persistence.

Many NoSQL products provide atomic single-key or single-document operations and conditional updates. Some also support transactions over multiple documents or partitions, but often with limits involving latency, contention, transaction size, or geographic distribution. A sound design documents the exact guarantee rather than assuming that the word “transaction” has the same meaning across products.

Isolation and Concurrency Anomalies

Isolation is best understood through anomalies. A dirty read occurs when a transaction observes uncommitted data. A non-repeatable read occurs when the same item returns different values during one logical operation because another operation committed an update. A phantom read occurs when a repeated query returns a different set of matching records. Lost update occurs when one write overwrites another without incorporating it. Write skew occurs when two transactions read a shared condition and update different records, together violating an invariant.

NoSQL systems may expose isolation through snapshot reads, serializable transactions, linearizable single-item operations, quorum reads and writes, or tunable consistency levels. These terms should be interpreted carefully. A quorum policy may reduce the probability of stale data, but it does not automatically make a multi-record business process serializable. Likewise, linearizability for one key does not guarantee serializability across several keys.

The appropriate isolation level depends on the invariant. A product catalogue description may tolerate stale reads. A stock decrement or account balance update generally requires stronger protection. It is wasteful and potentially expensive to apply the strongest isolation to every read, but dangerous to use weak isolation for decisions that affect money, authorization, inventory, or legal records.

Optimistic and Pessimistic Concurrency Control

Optimistic concurrency control (OCC) assumes that conflicts are relatively infrequent. A client reads a record together with a version, timestamp, or revision token. When writing, it states that the update is valid only if the version remains unchanged. If another writer has modified the record, the conditional write fails and the application retries, merges, or reports a conflict.

A typical pattern is:

1. Read document D with version 7.

2. Calculate the proposed change.

3. Write D only if its version is still 7.

4. On success, store version 8; on failure, reread and resolve the conflict.

OCC is attractive for web applications because it avoids holding locks across network calls and performs well when collisions are uncommon. It also makes lost updates visible rather than silently overwriting them. Its weaknesses include retry storms under contention, complex merge logic, and poor performance when many workers repeatedly compete for the same item.

Pessimistic concurrency control assumes that conflicts are sufficiently likely to justify reserving access. A lock, lease, or transactional write exclusion prevents competing operations from changing the protected state during a critical section. Pessimistic approaches can be useful for scarce resources, financial posting, or highly contended counters. They introduce risks of deadlock, lock expiration, blocking, reduced throughput, and recovery complexity after client failure.

Distributed locks require particular caution. A lock service must define ownership, expiration, renewal, fencing, and behaviour during network partitions. A client that pauses and later resumes may believe it still owns a lock whose lease has expired. Fencing tokens help by attaching a monotonically increasing token to each lock acquisition; the protected resource accepts operations only from the latest valid token. In many cases, a database conditional update is safer and simpler than a broad distributed lock.

The choice is workload-specific. OCC suits collaborative editing, profile updates, and low-contention aggregates. Pessimistic control may suit heavily contended inventory or allocation, provided the lock scope is narrow and failure handling is explicit. Hybrid approaches are common: conditional writes protect the final commitment while short-lived leases regulate expensive computation.

Idempotency and Safe Retry Design

An operation is idempotent when repeating it produces the same intended business result as executing it once. Idempotency is not merely the absence of duplicate network packets; it is a property of the business effect. A request to set an order status to “cancelled” can be idempotent. A request to “add five points” is not idempotent unless it carries a unique operation identifier and the system records whether that identifier has already been applied.

Retries are unavoidable in distributed applications. Timeouts do not reveal whether the server failed before processing, processed successfully, or processed and then lost the response. An idempotency key allows the server to associate repeated requests with one logical operation. The server commonly stores the key, request context, result, and status. A duplicate request can then return the original result rather than performing the effect again.

Idempotency must be scoped and retained for an appropriate period. A key used only in an in-memory process is ineffective after restart. A key stored without request validation may allow the same identifier to be reused for a different operation. The design should specify how long keys remain valid, what happens when two requests with the same key disagree, and how partially completed operations are reconciled.

Common techniques include unique operation records, conditional inserts, deduplication tables, natural business keys, and transactional outbox entries. For message consumers, a durable record of processed event identifiers can prevent repeated application, although storage growth and cleanup must be managed. “Exactly once” is often an end-to-end illusion; reliable systems usually combine at-least-once delivery with idempotent processing.

Eventual Consistency and Conflict Resolution

Eventual consistency means that replicas or services may temporarily disagree, but—if updates cease and communication continues—they are expected to converge. It can improve availability, geographic performance, and partition tolerance. It also changes application design: a read immediately following a write may return an older value, and users may observe intermediate states.

Eventual consistency is not a single guarantee. Systems may provide read-your-writes, monotonic reads, causal consistency, session consistency, or merely eventual convergence. These guarantees describe what a particular client or session can observe. Architects should state the required user experience rather than using “eventual” as a vague justification for stale data.

Conflicts arise when concurrent updates cannot be ordered or when replicas accept independent writes. Resolution may be based on:

• last-write-wins, using synchronized timestamps or logical ordering;
• deterministic field-level merging;
• operation-based or state-based CRDT techniques;
• domain rules, such as preserving the highest approved limit;
• human review for high-value or ambiguous changes.

Last-write-wins is simple but may silently discard a legitimate update and is vulnerable to clock error. Field-level merging can preserve more information but may create combinations that are individually valid yet collectively nonsensical. CRDTs provide mathematically designed convergence for suitable data types, such as sets or counters, but are not a universal solution for workflows involving authorization, payment, or exclusive ownership.

Conflict resolution should be observable. Applications should record conflict frequency, losing versions, resolution decisions, and cases requiring manual intervention. A system that converges silently may still fail its users if important changes disappear without explanation.

Distributed Transactions, Two-Phase Commit, and Sagas

A distributed transaction spans more than one resource, partition, database, or service. The strongest traditional approach is two-phase commit (2PC). In the prepare phase, participants confirm that they can commit. In the commit phase, a coordinator instructs all participants to commit. This can preserve atomicity across participants, but it introduces coordination latency, blocking risks, coordinator failure concerns, and reduced availability during network partitions. It is most appropriate where participants are trusted, infrastructure is controlled, transaction duration is short, and strong atomicity is essential.

Many microservice architectures avoid distributed locking across services because services own separate data and evolve independently. Instead, they use a saga: a sequence of local transactions connected by messages or orchestration. Each step commits locally. If a later step fails, compensating actions attempt to undo or neutralize the earlier effects.

For example, an order saga may reserve inventory, authorize payment, and arrange shipment. If payment fails, inventory reservation is released. If shipment cannot be arranged after payment authorization, the system may cancel the authorization or issue a refund. Compensation is not always a literal reverse operation: a shipped item cannot be “unshipped,” and a notification cannot be unsent. The domain must define a meaningful compensating outcome.

A saga can be coordinated by an orchestrator, which directs steps and tracks state, or implemented through choreography, in which services react to events. Orchestration offers visibility and centralized workflow policy but can create a powerful central component. Choreography reduces central coordination but may produce hidden dependencies and difficult debugging.

A robust saga requires durable state, explicit timeouts, retry policy, idempotent handlers, correlation identifiers, compensation rules, and a clear definition of terminal states. The transactional outbox pattern is especially useful: a service writes its business change and an outgoing event to the same local transaction. A publisher later delivers the event. Consumers remain idempotent because publication can be repeated.

Application-Level Integrity Controls

When database guarantees are limited, integrity must not be left to informal coding conventions. Controls should be designed in layers.

At the schema or validation layer, enforce required fields, types, ranges, enumerated states, and document shape where the database supports such rules. At the write layer, use conditional updates, unique keys, version checks, and atomic operators. At the service layer, enforce domain policies and state transitions, such as allowing an order to move from “paid” to “packed” but not directly from “cancelled” to “shipped.”

At the workflow layer, model long-running operations as explicit state machines. Each state should define permitted transitions, timeout behaviour, retryability, and compensation. At the reconciliation layer, periodically compare related records or projections and repair discrepancies through controlled processes. At the observability layer, log correlation identifiers, versions, decisions, and failure causes without exposing sensitive information.

Data integrity also depends on authorization. A correct transaction executed by an unauthorized actor is still a governance failure. Access should be restricted by role, service identity, tenant, and purpose. Administrative repair tools should require approval, produce audit records, and distinguish correction from deletion.

An Illustrative Case: Preventing Duplicate Payments in a Subscription Platform

Background

Consider a fictional subscription platform serving customers in several regions. Its services manage subscriptions, invoices, payment authorization, and email notifications. The platform uses a document database for subscription data and separate services for payment processing.

Situation and Problem

A customer clicks “Pay” while the browser is slow. The client times out and retries. The first request may have reached the payment provider and succeeded, while the second request is still in progress. The platform must avoid charging twice, maintain an accurate invoice state, and eventually notify the customer.

Analysis

A single database transaction cannot guarantee atomicity across the platform database and an external payment provider. A distributed lock around the customer account would be fragile because a process may fail while holding it. The operation also requires eventual workflow completion because provider responses and webhooks may arrive at different times.

Approach

The payment endpoint requires an idempotency key generated for the logical payment attempt. The service stores a payment-attempt document with a unique key, invoice identifier, amount, currency, and status. A conditional insert ensures that two requests cannot create separate attempts for the same key. If the key already exists, the service returns the stored result or current status.

The local transaction records the attempt and an outbox event. A worker sends the request to the payment provider using the same provider-side idempotency key. Provider webhooks are verified, stored with unique event identifiers, and processed idempotently. A conditional state transition prevents a late “authorized” event from overwriting a final “refunded” or “failed” state without an explicit reconciliation rule.

The workflow is a saga. Authorization is followed by invoice settlement and notification. If settlement fails after authorization, the system initiates a refund or places the payment into a review state. A reconciliation job compares provider records with local payment attempts and raises exceptions for manual investigation.

Outcome

Repeated client requests no longer create repeated payment effects. Temporary failures produce a visible pending state rather than an uncertain duplicate charge. Operations staff can trace the request from client key to local attempt, provider reference, webhook, and final invoice state.

Learning Points

The case illustrates that idempotency, conditional writes, an outbox, webhook deduplication, state-machine rules, and reconciliation work together. No single mechanism solves the entire problem. It also shows why compensation and auditability are essential when an external system participates in a business transaction.

Risks, Limitations, and Common Design Errors

A frequent error is assuming that a successful write means that all replicas, caches, search indexes, and downstream services are updated. Read models may lag and should expose freshness expectations where necessary. Another error is retrying non-idempotent operations without an operation key.

Unbounded retries can overload a failing dependency and create a retry storm. Exponential backoff, jitter, maximum attempts, circuit breaking, and dead-letter handling are necessary safeguards. Conversely, silently abandoning a failed message can leave a business process permanently incomplete.

Long transactions and large distributed locks reduce throughput and increase contention. They should be replaced, where possible, by short local transactions and explicit workflows. Timestamp-based conflict resolution is risky when clocks are not trustworthy. Client-side validation alone is insufficient because different clients may implement different rules and can bypass validation entirely.

Another mistake is treating compensation as guaranteed rollback. A compensation can fail, be delayed, or be impossible. Systems should include pending-compensation states, operational alerts, and reconciliation. Finally, eventual consistency should not be used to excuse undefined business semantics. The organization must decide which temporary inconsistencies are acceptable and which require immediate prevention.

Governance, Ethics, and Professional Responsibility

Integrity controls affect financial accuracy, privacy, safety, and accountability. Audit records should establish who or which service made a change, when it occurred, what version was affected, and why the action was permitted. Logs must be protected because they may contain personal, financial, or security-sensitive data.

Retention and deletion policies must account for legal obligations and operational recovery. Idempotency records, event histories, and audit trails should not be retained indefinitely without purpose, but deleting them too early may permit duplicate processing or undermine investigation. Multi-tenant systems require strict isolation of keys, queries, events, and reconciliation jobs.

Engineers should communicate uncertainty honestly. A user should not be told that a payment failed merely because the response timed out; “pending confirmation” may be more accurate. Governance also requires testing failure scenarios, documenting recovery responsibilities, and ensuring that manual interventions are controlled rather than hidden in production scripts.

Current Developments and Best Practices

Modern cloud databases increasingly provide configurable consistency, multi-document transactions, global tables, change streams, and server-side conditional operations. These features can reduce application complexity but do not remove the need to understand failure modes. Globally distributed transactions remain costly compared with local writes, and latency-sensitive systems often combine strong consistency for critical aggregates with asynchronous projections for queries.

Event-driven architectures now commonly use durable workflow engines, transactional messaging patterns, schema registries, and automated reconciliation. CRDTs and offline-first techniques are expanding collaborative applications, but they require careful selection of mergeable domain models. Observability platforms also make it possible to trace a transaction across services, measure lag, identify conflicting writes, and detect unusual retry behaviour.

Effective practice includes the following:

• define business invariants before selecting a consistency mechanism;
• document transaction scope, isolation assumptions, and failure semantics;
• use atomic database operations for the smallest critical unit;
• apply OCC by default where conflicts are uncommon and detectability matters;
• use pessimistic mechanisms only with explicit lease, fencing, and recovery rules;
• make commands, message handlers, and compensations idempotent;
• use outbox and inbox or deduplication patterns for reliable messaging;
• model long-running work as observable state machines;
• test timeouts, duplicate delivery, stale reads, partitions, failover, and partial completion;
• reconcile independently rather than assuming that successful messaging implies correct final state.

Chapter Summary

NoSQL integrity is an architectural concern involving database guarantees, application logic, messaging, workflow design, and operations. ACID properties must be evaluated at a precise scope because a system may provide atomic single-document writes without providing atomicity across services. Isolation determines which concurrent anomalies are possible, while optimistic and pessimistic concurrency control offer different trade-offs between throughput, contention, blocking, and complexity.

Idempotency makes retries safe, particularly when requests, messages, and webhooks may be duplicated. Eventual consistency improves scalability and availability but requires explicit user expectations and conflict-resolution policies. Distributed transactions may use two-phase commit when strong coordination is justified, but sagas and compensating actions are often more suitable for independent services. Transactional outboxes, conditional writes, state machines, reconciliation, auditability, and observability provide practical application-level integrity controls.

The most reliable design begins with business invariants and failure analysis. It then selects the weakest mechanism that safely preserves those invariants, while making uncertainty, retries, conflicts, and manual recovery visible.

Key Terms

ACID: A set of transaction properties comprising atomicity, consistency, isolation, and durability.

Atomicity: The property that a transaction's changes are committed together or not committed at all.

Business invariant: A rule that must remain true for the application domain.

Conditional write: An update performed only when a stated condition, such as a version, remains valid.

Conflict resolution: The policy used to reconcile concurrent or incompatible updates.

Durability: The survival of committed data according to the system's failure and storage guarantees.

Eventual consistency: A model in which replicas may temporarily differ but are expected to converge.

Idempotency: The property that repeating a logical operation does not create an additional unintended effect.

Isolation: The degree to which concurrent transactions are separated from one another.

Lost update: A concurrency anomaly in which one write silently overwrites another.

Optimistic concurrency control: Conflict detection performed at commit or write time using versions or conditions.

Pessimistic concurrency control: Conflict prevention through locks, leases, or exclusive access.

Saga: A sequence of local transactions coordinated through messages and compensating actions.

Serializable execution: An isolation behaviour equivalent to some serial ordering of transactions.

Transactional outbox: A pattern that stores a business change and its outgoing event in one local transaction.

Two-phase commit: A distributed commit protocol involving preparation followed by coordinated commitment.

Write skew: An anomaly in which separate updates collectively violate an invariant based on shared reads.

Reconciliation: A controlled process of detecting and correcting discrepancies between related systems or records.

Review Questions

Short Answer Questions

1. What does atomicity mean in a single-document NoSQL operation?

2. Define idempotency and explain why it is important for retries.

3. What is a lost-update anomaly?

4. State one difference between optimistic and pessimistic concurrency control.

5. What is the purpose of a transactional outbox?

Long Answer Questions

1. Explain the four ACID properties and discuss how their implementation may differ in NoSQL databases.

2. Compare optimistic and pessimistic concurrency control, including suitable use cases and risks.

3. Explain eventual consistency and analyse at least three conflict-resolution strategies.

4. Describe the structure, benefits, and limitations of a saga in a microservice architecture.

5. Discuss how application-level integrity controls complement database transactions.

Analytical / Application Questions

1. Two customers attempt to purchase the final unit of a product at the same time. Design a concurrency-control strategy and justify your choice of isolation, conditional write, retry, and failure behaviour.

2. A payment request times out after the provider may have approved it. Design an end-to-end workflow that prevents duplicate charging and eventually produces a correct invoice state.

3. A globally replicated profile database receives different edits to the same customer record while a region is disconnected. Propose a conflict policy, identify its risks, and specify what should be logged or reviewed manually.

22. Multiple Choice Questions with Answers

Question 1

A NoSQL system provides atomic updates to one document but no transaction spanning two collections. Which conclusion is most accurate?

A. It provides no transaction guarantees at all

B. It provides atomicity at the document boundary, but cross-collection integrity needs another mechanism

C. It automatically provides serializability across the database

D. It guarantees that all replicas immediately contain the update

Correct Answer: B. It provides atomicity at the document boundary, but cross-collection integrity needs another mechanism

Question 2

Two clients read version 7 of a document. Client X successfully changes it to version 8. What should happen when client Y submits an update conditioned on version 7?

A. The database should overwrite version 8 silently

B. The database should merge the writes without domain rules

C. The conditional write should fail so the application can reread or resolve the conflict

D. The database should delete both versions

Correct Answer: C. The conditional write should fail so the application can reread or resolve the conflict

Question 3

Which operation is naturally idempotent?

A. Add 100 points to an account

B. Append a new shipment event on every request

C. Set an order status to cancelled using the same logical command identifier

D. Charge a credit card without an operation key

Correct Answer: C. Set an order status to cancelled using the same logical command identifier

Question 4

What is a principal limitation of last-write-wins conflict resolution?

A. It requires every update to be manually reviewed

B. It can silently discard a legitimate concurrent update and depends on reliable ordering

C. It prevents replicas from converging

D. It can only be used for numeric counters

Correct Answer: B. It can silently discard a legitimate concurrent update and depends on reliable ordering

Question 5

Which statement best describes a saga?

A. A single lock held across all microservices

B. A sequence of local transactions with coordination and compensating actions

C. A replacement for all database validation

D. A protocol that guarantees rollback of external side effects

Correct Answer: B. A sequence of local transactions with coordination and compensating actions

Question 6

Why is a transactional outbox useful?

A. It ensures a database change and its outgoing event are recorded in the same local transaction

B. It makes every external API call globally atomic

C. It eliminates the need for idempotent consumers

D. It prevents all network partitions

Correct Answer: A. It ensures a database change and its outgoing event are recorded in the same local transaction

Question 7

Which situation most strongly supports optimistic concurrency control?

A. A resource is constantly contested by thousands of workers

B. Conflicts are uncommon and the application can retry or merge failed updates

C. A long-running lock must be held across an unreliable network

D. The business requires all services to commit simultaneously

Correct Answer: B. Conflicts are uncommon and the application can retry or merge failed updates

Question 8

A payment webhook is delivered twice. Which design most directly prevents duplicate invoice settlement?

A. Process every webhook in arrival order

B. Use a durable unique event identifier and an idempotent state transition

C. Ignore all webhooks and trust the browser response

D. Increase the webhook consumer's thread count

Correct Answer: B. Use a durable unique event identifier and an idempotent state transition

Question 9

Which statement about two-phase commit is correct?

A. It removes coordination latency from distributed transactions

B. It can provide coordinated atomic commitment but may block and reduce availability during failures

C. It is identical to eventual consistency

D. It guarantees compensation for irreversible external actions

Correct Answer: B. It can provide coordinated atomic commitment but may block and reduce availability during failures

Question 10

Why should a distributed lock often use fencing tokens?

A. To allow expired lock holders to continue indefinitely

B. To prevent a paused or expired client from performing operations after another client has acquired the lock

C. To make all reads eventually consistent

D. To replace authentication and authorization

Correct Answer: B. To prevent a paused or expired client from performing operations after another client has acquired the lock