Persistence, Transactions and Data Consistency

Introduction

A RESTful API is useful only when it can reliably store, retrieve, and modify state. Persistence connects an HTTP interface to durable data stores, while transactions and consistency mechanisms determine whether that state remains correct when requests arrive concurrently, fail midway, or interact with changing schemas. In a simple prototype, an endpoint may directly execute a database query. In a production system, however, persistence requires deliberate architectural choices involving repositories, object–relational mapping, transaction boundaries, concurrency control, data modeling, and operational monitoring.

This chapter examines how RESTful APIs interact with relational and NoSQL databases. It explains repositories and ORMs, the ACID transaction model, isolation levels, optimistic locking, consistency models, schema evolution, and the N+1 query problem. The emphasis is not on a particular programming language or framework, but on transferable design principles.

Learning Objectives

By the end of this chapter, you should be able to:

1. Explain how repositories and ORMs mediate between API code and persistence stores.
2. Compare relational and NoSQL persistence strategies for RESTful applications.
3. Describe transaction atomicity, consistency, isolation, and durability.
4. Select suitable transaction boundaries and isolation levels.
5. Analyze race conditions and implement optimistic locking.
6. Distinguish strong, eventual, causal, and read-your-writes consistency.
7. Design backward-compatible schema migrations.
8. Identify and mitigate the N+1 query problem.
9. Evaluate persistence designs using correctness, performance, scalability, and governance criteria.

1. Persistence as an Architectural Boundary

Persistence is the process of storing application state so that it survives process termination. A REST API normally sits between clients and one or more persistence systems:

Client → HTTP controller → application service → repository/data-access layer → database
                                      ↓
                              transaction boundary

The controller should translate HTTP requests and responses; it should not normally contain database-specific logic. The application service coordinates a use case, such as placing an order or transferring funds. A repository expresses persistence operations in domain terms, while an adapter or ORM translates those operations into SQL, document queries, or key-value commands.

This separation has several benefits:

• Testability: application logic can be tested with in-memory or fake repositories.
• Maintainability: database-specific code is concentrated in a small boundary.
• Security: authorization and validation can be enforced before persistence operations.
• Replaceability: a relational store can sometimes be replaced or supplemented without rewriting controllers.
• Observability: query timing, transaction failures, and retry behavior can be measured at a defined layer.

The abstraction must not be so general that it hides important database behavior. For example, a repository that pretends every database supports joins, transactions, and serializable isolation may create false assumptions. Good abstractions hide accidental complexity while exposing capabilities that affect correctness.

Repositories and Data Mappers

A repository represents a collection of domain objects or aggregates and offers operations such as findById, save, and delete. A data mapper translates between domain objects and persistence records. The domain model should not be forced to know whether data is stored in rows, documents, or key-value entries.

A repository should have clear semantics. For example, save(order) must specify whether it inserts a new order, updates an existing order, or performs an upsert. It should also define what happens when the record has been concurrently modified. Ambiguous repository behavior leads to accidental overwrites and inconsistent APIs.

Object–Relational Mapping

An object–relational mapper (ORM) maps classes or objects to relational tables. It commonly provides identity tracking, relationship mappings, query construction, validation hooks, and unit-of-work behavior. ORMs improve productivity, but they do not eliminate the need to understand SQL or database design.

Common ORM risks include:

• inefficient queries generated from apparently simple object navigation;
• implicit lazy loading during serialization;
• excessive columns selected by default;
• incorrect mappings for nullable values, time zones, or monetary amounts;
• long-lived persistence contexts that retain stale objects;
• accidental writes caused by automatic dirty checking.

A developer should inspect generated SQL, examine query plans, define indexes intentionally, and understand when a raw query is safer or more efficient than an ORM expression.

2. Relational and NoSQL Persistence

Relational databases organize data into tables with defined columns and relationships. Primary keys identify records, foreign keys express references, and constraints enforce rules such as uniqueness or non-nullability. Relational databases are particularly effective when data has structured relationships and operations require multi-record transactions, joins, and sophisticated querying.

NoSQL is a broad category rather than one single model. Important types include:

NoSQL systems often encourage denormalization and data models designed around access patterns. A document may contain the information needed for one API response, reducing joins and network round trips. The cost may be duplicated data, more complicated updates, and weaker cross-document transaction guarantees.

The choice should follow the workload rather than fashion. Important questions include:

1. Are relationships central to the use case?
2. Must several records change atomically?
3. What are the read and write patterns?
4. Is horizontal partitioning required at very high scale?
5. What consistency guarantees do clients need?
6. How will backups, migrations, auditing, and reporting be performed?

Polyglot persistence uses more than one storage technology in a system. It can be appropriate—for example, a relational database for orders and a search index for product discovery—but it introduces synchronization, failure, and operational complexity. Each additional store creates another boundary at which data can become temporarily inconsistent.

3. Transactions and the ACID Model

A transaction is a logical unit of work whose operations are treated according to defined all-or-nothing and visibility rules. The classic ACID properties are:

• Atomicity: either all intended changes occur or none occur.
• Consistency: each committed transaction preserves declared data invariants.
• Isolation: concurrent transactions do not produce outcomes that violate the selected isolation semantics.
• Durability: committed changes survive failures according to the database’s durability guarantees.

Consistency in ACID is not the same as distributed consistency models. ACID consistency means that application and database constraints are preserved; it does not automatically mean every client everywhere immediately observes the same value.

Transaction Boundaries

A transaction should normally surround one coherent business operation. For example, placing an order may create an order record, create order lines, reserve inventory, and record a payment authorization. If these changes must succeed or fail together, they belong in one transaction—provided the participating resources support the required atomicity.

A transaction should not normally remain open while the server waits for another HTTP request, a user decision, or a slow external service. Long transactions hold locks or snapshots, increase contention, and make failures harder to recover from.

A practical service structure is:

1. Validate syntax and basic business input.
2. Begin a transaction.
3. Read the required state.
4. Check business invariants.
5. Apply changes.
6. Commit.
7. Publish external effects using a reliable mechanism.

External effects such as email, shipping requests, or message publication cannot generally be rolled back by a local database transaction. The transactional outbox pattern addresses this by writing an event to an outbox table in the same transaction as the business change. A separate publisher sends unsent outbox records and marks them delivered. Consumers must be idempotent because delivery may be repeated.

Distributed Transactions and Sagas

When one business operation spans independent databases or services, a single local transaction cannot provide atomicity across all participants. Two-phase commit can coordinate some systems, but it increases blocking, latency, and operational complexity. A saga instead divides the operation into local transactions and defines compensating actions for failures.

For example, a travel booking saga might reserve a flight, reserve a hotel, and then confirm both. If hotel reservation fails, the flight reservation is cancelled. Compensation is not always a perfect reversal: an external side effect may be irreversible, and compensation may itself fail. Therefore, sagas require explicit state machines, retries, timeouts, reconciliation, and operational visibility.

4. Concurrency and Isolation

Concurrency occurs when overlapping requests read or modify related data. Typical anomalies include:

• Lost update: one write overwrites another write based on stale data.
• Dirty read: a transaction reads data written by another transaction that later rolls back.
• Non-repeatable read: the same query returns different committed values within one transaction.
• Phantom read: repeating a predicate query returns a different set of rows because another transaction inserted or removed matching rows.

Isolation levels provide different trade-offs between correctness and concurrency. Names and exact behavior vary somewhat by database implementation, so the database documentation must be consulted.

Isolation is not a substitute for correct business logic. A transaction that reads a value, makes a decision, and later writes must still protect the relevant invariant. For example, checking that available seats are greater than zero and then decrementing the count can fail under concurrency if two transactions make the check simultaneously.

Pessimistic and Optimistic Concurrency Control

Pessimistic control assumes conflicts are likely and prevents them through locks. A row may be locked for update while a transaction changes it. This can protect invariants but may cause blocking and deadlocks. Applications must keep transactions short and implement safe retry handling for deadlock or serialization failures.

Optimistic control assumes conflicts are uncommon. Each record has a version number or modification token. The update succeeds only if the version still matches the value read earlier.

Suppose a record initially has version 7. Client A and client B both read version 7. Client A sends an update, and the database executes an update equivalent to:

UPDATE account
SET display_name = ?, version = version + 1
WHERE id = ? AND version = 7;

The update affects one row and changes the version to 8. Client B sends an update requiring version 7; it affects zero rows. The API should report a conflict, commonly with HTTP status 409 Conflict, rather than silently overwriting A’s work.

A conditional update can be represented conceptually as:

Here, current_version is the stored version at update time and supplied_version is the version observed by the client. The rule is applicable when a monotonically changing version token is stored with the record and the update condition is evaluated atomically by the database.

The same idea can use an HTTP entity tag:

GET /profiles/42
ETag: "v7"

PUT /profiles/42
If-Match: "v7"

If the representation has changed, the precondition fails. 412 Precondition Failed is appropriate when an explicit conditional request fails; 409 Conflict is often used for a domain-level conflict. The API contract should be consistent and documented.

5. Consistency Models in Distributed APIs

In a single database transaction, clients may still observe different results when data is replicated or asynchronously processed. Important models include:

• Strong consistency: a successful read reflects the latest committed write according to the system’s defined ordering.
• Eventual consistency: if updates stop, replicas converge after some period, but a read may temporarily be stale.
• Read-your-writes consistency: a client sees its own successful writes in later reads.
• Monotonic reads: once a client has observed a value, later reads do not move backward to an older value.
• Causal consistency: causally related operations are observed in an order that respects their cause-and-effect relationship.

Consistency should be selected per use case. A bank balance or inventory reservation usually requires stronger guarantees than a social-media reaction count or a search index. A system can also provide a strongly consistent source of truth while exposing eventually consistent read models for performance.

Users need clear API semantics. If a POST returns success but an immediate GET is routed to a lagging replica, the client may incorrectly conclude that creation failed. Solutions include session affinity, routing reads to a primary for a bounded period, returning the created representation directly, or using a consistency token understood by the read service.

Retries create another concern. A client that times out after sending a request cannot know whether the server committed it. Non-idempotent operations should use an idempotency key. The server stores the key and the resulting outcome, ensuring that repeated attempts do not create duplicate orders or charges.

6. Schema Design and Evolution

A schema includes tables, columns, constraints, indexes, document structures, and assumptions embedded in application code. Schema evolution is unavoidable as requirements change. The challenge is to change storage without breaking running application instances, old clients, background jobs, or analytical consumers.

A safe relational migration commonly follows the expand–migrate–contract pattern:

1. Expand: add the new nullable column, table, or index without removing the old structure.
2. Migrate: deploy application code that can read both forms and write the new form; backfill existing records in controlled batches.
3. Contract: once all readers and writers use the new form, remove the old column or compatibility code.

For a rename, directly renaming a column can break an older application instance during a rolling deployment. Adding a new column, dual-writing temporarily, backfilling, switching reads, and then removing the old column is safer. Dual writes must be monitored because partial failures can leave the two representations different.

Migration practices include:

• versioning migrations in source control;
• testing migrations against realistic data volumes;
• measuring lock duration and rollback feasibility;
• making migrations restartable or explicitly recording progress;
• avoiding destructive changes until recovery windows have passed;
• separating schema deployment from data backfill when the backfill is large;
• validating constraints before enforcing them strictly.

Document databases offer flexible schemas, but flexibility is not the absence of schema. Applications still rely on document versions, required fields, and data-type assumptions. A document can carry a schema version, and readers can support older versions while a background process upgrades records.

7. The N+1 Query Problem

The N+1 query problem occurs when an initial query retrieves N parent records and the application performs one additional query for each parent. One query becomes N + 1 database round trips. The issue often arises from lazy-loaded ORM relationships or serialization that navigates child properties.

For example, listing 100 orders might execute one query for orders and 100 queries for customer data. Even if each query is fast, network latency, connection usage, database parsing, and transaction overhead can make the endpoint slow.

Solutions include:

• eager loading when the related data is always required;
• explicit joins or projection queries that select only needed fields;
• batch loading, where child records are fetched using one IN query;
• a data loader that groups requests within a request context;
• endpoint-specific read models or materialized views;
• pagination and bounded result sizes.

Eager loading is not universally correct. Joining several one-to-many relationships can multiply rows and transfer excessive data. The best solution depends on cardinality, response shape, indexes, and measured query plans. Query count, total duration, rows returned, and database CPU should be monitored rather than inferred from source code alone.

8. Applied Case: A Concurrent Order API

The following is an illustrative case. An online retailer exposes POST /orders, GET /orders/{id}, and POST /orders/{id}/cancel. Orders are stored in a relational database, while a search index provides fast product discovery.

Background and Problem

During a seasonal sale, two requests sometimes purchase the final unit of a product. In addition, clients retry after network timeouts and occasionally create duplicate orders. The order list endpoint becomes slow because the ORM loads the customer and lines separately for every order.

Analysis

The inventory invariant is that confirmed reservations must not exceed available stock. A read-then-write sequence without concurrency control is unsafe. Retry behavior creates an idempotency problem, and the search index is eventually consistent with the relational source of truth. The list endpoint exhibits an N+1 pattern.

Approach and Solution

The service uses a relational transaction containing:

1. retrieval of the idempotency key record;
2. insertion or verification of the order request;
3. conditional inventory reservation;
4. creation of the order and order lines;
5. insertion of an outbox event.

Inventory is updated with an atomic conditional statement conceptually equivalent to:

UPDATE inventory
SET available = available - :quantity,
    version = version + 1
WHERE product_id = :product_id
  AND available >= :quantity;

The service checks that exactly one row changed. A zero-row result means insufficient stock or a competing update, so the transaction does not create a confirmed order. The idempotency record stores the original response, allowing a repeated request with the same key to return the same result rather than create a second order.

The outbox publisher sends OrderCreated events to the indexing service. The indexer is idempotent and records the event identifier. Search results may lag briefly, but order confirmation reads from the relational source of truth.

For the order list, the service uses a projection query that returns order summaries and customer names in one controlled query. A second batched query retrieves lines for the selected page, or a dedicated read model is used when the response is frequent and complex.

Outcome

The design prevents overselling, makes retries safe, isolates search lag from order correctness, and removes the unbounded query pattern. It also introduces operational responsibilities: monitoring outbox age, detecting failed index updates, measuring transaction contention, and reconciling inventory with physical stock.

Learning Points

• Business invariants must be enforced at the point where concurrent writes occur.
• Idempotency is essential when clients retry uncertain requests.
• An outbox provides reliable handoff but not instantaneous cross-system consistency.
• Query optimization requires examining the response shape and data cardinality.
• Correctness, performance, and operational complexity must be evaluated together.

9. Governance, Security, and Professional Practice

Persistence design has governance consequences. Databases often contain personal, financial, or health information. Engineers should apply least privilege, encrypt data in transit and at rest where appropriate, avoid logging secrets and sensitive payloads, and define retention and deletion procedures. Audit records should distinguish who initiated a change, what changed, and when, while respecting privacy requirements.

Transactions and retries must not accidentally duplicate financial actions. Payment operations should use provider-supported idempotency mechanisms and reconciliation processes. Backups must be tested through restoration exercises; a backup that has never been restored is an unverified assumption.

Operational metrics should include transaction latency, rollback rate, deadlocks, serialization failures, replication lag, connection-pool saturation, migration duration, outbox backlog, cache staleness, and slow-query frequency. Distributed tracing can connect an HTTP request to database calls and downstream events.

Artificial intelligence does not remove these responsibilities. AI-assisted code generation may produce unsafe ORM queries, missing transaction boundaries, or incorrect assumptions about isolation. Generated persistence code must be reviewed, tested under concurrency, checked for injection risks, and evaluated against the actual database documentation.

10. Best-Practice Checklist

• Define the business invariant before selecting a transaction strategy.
• Keep transaction boundaries around complete business operations.
• Use database constraints as a final line of defense.
• Prefer parameterized queries and least-privilege database accounts.
• Choose isolation deliberately; do not assume the default is sufficient.
• Use optimistic locking when lost updates are unacceptable and conflicts are manageable.
• Make retried commands idempotent.
• Use an outbox or a deliberately designed saga for cross-service effects.
• Treat NoSQL denormalization as a consistency trade-off, not a free optimization.
• Version and test schema changes using expand–migrate–contract where necessary.
• Inspect generated SQL and query plans.
• Detect N+1 queries with profiling and integration tests.
• Monitor replication lag and asynchronous processing delay.
• Test deadlocks, timeouts, partial failures, and recovery procedures.

Chapter Summary

Persistence is an architectural boundary between REST resources and durable state. Repositories and ORMs can improve structure and productivity, but developers must understand the queries and database behavior they produce. Relational databases provide strong constraints and expressive transactions; NoSQL stores offer models optimized for particular access patterns and scale, often with denormalization and different consistency trade-offs.

Transactions provide atomicity, consistency, isolation, and durability, but their boundaries must be aligned with business operations. Isolation levels influence concurrency anomalies, while pessimistic and optimistic control offer different responses to contention. Version tokens and conditional updates prevent silent lost updates. Distributed workflows commonly require idempotency, transactional outboxes, or sagas rather than one global transaction.

Schema evolution should support rolling deployments through expansion, migration, and later contraction. The N+1 query problem demonstrates why object-oriented navigation can produce inefficient database access. Effective persistence engineering combines correctness, performance, security, observability, migration discipline, and an explicit understanding of failure modes.

Key Terms

Transaction: A logical unit of work governed by atomicity and related execution guarantees.

Atomicity: The property that a transaction commits all its intended changes or none of them.

ACID: Atomicity, consistency, isolation, and durability.

Repository: An abstraction that provides domain-oriented access to persisted data.

ORM: Object–relational mapper that translates between objects and relational structures.

Data mapper: A component that translates between domain objects and persistence records.

Isolation level: A database-defined guarantee describing how concurrent transactions may observe one another.

Lost update: A concurrent-write anomaly in which one update overwrites another based on stale state.

Optimistic locking: Conflict detection using a version or modification token.

Pessimistic locking: Concurrency control that prevents conflicting operations through locks.

Strong consistency: A model in which reads observe the latest committed value according to the system’s ordering rules.

Eventual consistency: A model in which replicas converge when updates cease, although reads may temporarily be stale.

Idempotency: The property that repeating an operation produces the same intended effect as performing it once.

Transactional outbox: A pattern that stores business changes and outgoing events in one local transaction.

Saga: A distributed workflow composed of local transactions and compensating actions.

Schema evolution: The controlled modification of a data structure over time.

Expand–migrate–contract: A migration strategy that adds compatible structures, moves usage, and later removes obsolete structures.

N+1 query problem: Inefficient execution of one parent query followed by one query per parent record.

Replication lag: The delay before a replica reflects a change made by a primary or source system.

Review Questions

Short Answer Questions

1. What responsibilities should normally belong to a repository rather than an HTTP controller?
2. Define the four ACID properties.
3. What is a lost update, and how can optimistic locking prevent it?
4. Why can an eventually consistent search index not be treated as the authoritative source for order confirmation?
5. What is the N+1 query problem?

Long Answer Questions

1. Compare relational and document databases for a REST API that manages customers, orders, and order lines.
2. Explain how isolation levels affect dirty reads, non-repeatable reads, phantom reads, and concurrency.
3. Describe the expand–migrate–contract approach to a backward-compatible schema change.
4. Explain the transactional outbox pattern, including its benefits and limitations.
5. Discuss how ORMs improve development productivity while also creating performance and correctness risks.

Analytical / Application Questions

1. Two clients read the same product with version 12 and both attempt to update its price. Design the database condition and API response behavior needed to prevent a silent overwrite.
2. An order service writes to a relational database and a product-search index. Analyze the failure that occurs when the database commit succeeds but index publication fails, and propose a recovery design.
3. An endpoint returns 50 orders and executes 101 SQL queries. Diagnose the likely cause and propose two alternative solutions, explaining the trade-offs of each.

Multiple Choice Questions with Answers

Question 1

Which component should normally coordinate a business use case and transaction boundary?

A. HTTP router

B. Application service

C. CSS stylesheet

D. DNS resolver

Correct Answer: B. Application service

Question 2

Which ACID property means that all intended transaction changes occur or none do?

A. Atomicity

B. Durability

C. Isolation

D. Indexing

Correct Answer: A. Atomicity

Question 3

What is the primary purpose of an optimistic-lock version condition?

A. To compress database records

B. To detect a stale update

C. To encrypt a password

D. To create a database backup

Correct Answer: B. To detect a stale update

Question 4

Which pattern stores an outgoing event in the same local transaction as a business change?

A. Circuit breaker

B. Transactional outbox

C. Round-robin routing

D. Content negotiation

Correct Answer: B. Transactional outbox

Question 5

A read of uncommitted data that may later be rolled back is called what?

A. Phantom read

B. Dirty read

C. Lost update

D. Monotonic read

Correct Answer: B. Dirty read

Question 6

What is a common purpose of an idempotency key in a POST operation?

A. To prevent duplicate effects after retries

B. To increase image resolution

C. To select a CSS theme

D. To disable database constraints

Correct Answer: A. To prevent duplicate effects after retries

Question 7

Which migration sequence is generally safest for a rolling deployment?

A. Delete, deploy, then recreate

B. Contract, expand, then migrate

C. Expand, migrate, then contract

D. Back up, delete, and ignore old readers

Correct Answer: C. Expand, migrate, then contract

Question 8

An endpoint makes one query for parent records and one additional query for each of N parents. What problem does this represent?

A. N+1 query problem

B. Dirty write problem

C. Schema-less problem

D. Dead-letter problem

Correct Answer: A. N+1 query problem

Question 9

Which approach uses compensating actions across multiple local transactions?

A. Saga

B. Foreign key

C. Lazy loading

D. Snapshot query

Correct Answer: A. Saga

Question 10

A search index may show older data immediately after an order is created because it is what type of system?

A. Eventually consistent

B. Strictly serializable by definition

C. Stateless and immutable

D. Unindexed relational storage

Correct Answer: A. Eventually consistent