Databases, indexes, and concurrency

Status: Complete. Last reviewed 2026-08-28.

A relational database protects declared invariants while many transactions read and write shared state. Correct application code depends on the actual engine, statements, indexes, isolation level, and retry policy—not on the word “transaction” alone.

Relational guarantees and ownership

A table models a relation with a declared schema. Primary, unique, foreign-key, not-null, and check constraints reject invalid committed state regardless of which application path writes it. Application validation still improves error messages and can avoid doomed work, but a read that says “available” is not a concurrency guarantee. If an invariant can be expressed by a constraint, the database is usually its strongest final owner.

Constraints have scope. A unique (tenant_id, email) key expresses tenant-local uniqueness; email alone expresses global uniqueness. A nullable unique column has engine-specific null semantics. A foreign key protects references within the same database boundary but does not implement authorization, cross-service existence, or business lifecycle by itself.

Normalization separates facts to reduce update, insertion, and deletion anomalies. Denormalization duplicates or precomputes data for a measured read path and therefore needs an owner, refresh/transaction strategy, acceptable staleness, and reconciliation. “Avoid joins” is not a correctness model.

Transactions and ACID

Atomicity means a transaction’s database changes commit or roll back as a unit. Consistency means declared constraints and application invariants are preserved by correct transactions; the database cannot infer every business rule. Isolation governs observations among concurrent transactions. Durability describes committed data surviving failures according to the engine/configuration guarantees.

A transaction cannot roll back an email, HTTP call, file published elsewhere, or message already consumed. Keep external I/O outside database lock windows where possible and bridge boundaries with idempotency, an outbox, or reconciliation. Also avoid user input or slow computation while locks are held.

Transactions should be short and explicitly bounded. Errors can abort a transaction and require rollback. Retrying one failed statement is not always valid; after a serialization failure or deadlock, retry the complete transaction closure from fresh reads, only when its external effects are replay-safe.

Isolation and anomalies

Isolation-level names do not produce identical behavior across engines. Read each vendor’s definition and choose from the invariant and workload.

Dirty read: observe another transaction’s uncommitted data.
Non-repeatable read: rereading one row observes a later committed version.
Phantom: repeating a predicate observes a changed qualifying set.
Lost update: one writer overwrites another decision based on stale state.
Write skew: transactions read a shared invariant, then update disjoint rows so both commits jointly violate it.

MVCC commonly lets readers see snapshots while retaining older row versions; it does not make every multi-row decision serializable. Locking reads, predicate/range locks, serialization checks, and conflict behavior vary. PostgreSQL Read Committed takes a new statement snapshot; its Serializable level uses serialization anomaly detection. InnoDB’s default Repeatable Read and locking behavior differ. Never map a generic anomaly table onto an engine without verification.

For a balance decrement, prefer a single conditional statement:

UPDATE accounts
SET balance = balance - :amount
WHERE id = :id
  AND balance >= :amount;

Zero affected rows means missing or insufficient balance according to the defined contract. If ledger insertion and balance mutation belong together, include both in the transaction and protect a unique operation ID. Alternatively lock the account row before checking, or use a version column in the update predicate. The right choice depends on contention, error behavior, and the number of rows defining the invariant.

Pessimistic and optimistic control

Pessimistic locking reserves rows or ranges before a contested decision. It is direct under high contention but increases waiting and deadlock risk. Acquire locks in a stable order, index the locking predicate so it does not cover unintended rows, and keep the critical section short. SKIP LOCKED is useful for work claiming but changes fairness and visibility semantics.

Optimistic control reads a version and updates only where that version still matches. Conflicts do not block, but callers must reload/recompute or surface a conflict. It works well when collisions are uncommon and operations can be retried or merged. A timestamp can be a poor version if precision or independent updates allow equality; an explicit integer version is clearer.

Advisory locks coordinate participants agreeing on an application key. They may be transaction- or session-scoped and do not automatically protect rows from code that ignores them. Use them for coarse coordination or resources not naturally represented by one row, with lifecycle and connection-pool behavior understood.

Indexes are workload data structures

A B-tree orders key values and supports equality, range, ordered scans, and prefix use. Composite column order should follow actual predicates and ordering, not a selectivity slogan. For a query filtering tenant_id = ?, status = ?, and ordering by created_at DESC, id DESC, an index beginning with equality columns and continuing through the ordering can support a narrow ordered range. Whether status belongs before the time columns depends on which queries and cardinalities matter.

An index adds storage, cache pressure, and work to insert/update/delete. Included/covering columns can avoid heap/table access but make the index larger. Partial indexes can target a predicate where supported. Functional/expression indexes help computed predicates, but the query expression must match the engine’s rules. Too many overlapping indexes slow writes and complicate planning.

The leftmost-prefix heuristic is useful but incomplete: skip scans, bitmap combinations, index condition pushdown, statistics, correlation, and engine versions affect choices. An index can exist and still be rejected because a broad scan is cheaper, statistics are stale, the predicate transforms the column, types/collations differ, or the result needs many random table fetches.

Read the plan, not the ORM

EXPLAIN shows the planner’s estimated strategy; execution variants such as PostgreSQL EXPLAIN ANALYZE or MySQL EXPLAIN ANALYZE execute and report actual behavior. Use production-like data and be careful with mutating statements. Compare estimated and actual rows, loops, access method, filters, join order/algorithm, sorts, temporary structures, and buffers/I/O where available.

Rows returned are not rows examined. A query returning ten records can scan millions, sort a large intermediate set, or repeat an indexed lookup thousands of times. ORM query count also matters: N+1 produces many individually plausible plans plus network and connection overhead.

Parameter values can change optimal plans. Skewed tenant sizes, prepared-statement plan caching, stale statistics, and correlated columns explain production-only regressions. Capture the actual SQL and bindings safely, not a guessed equivalent. Optimization may require a new index, rewritten predicate, changed join/order, updated statistics, partitioning, or a different data model; verify write impact and regression on other queries.

Schema and deployment safety

Schema changes run concurrently with application traffic. Lock strength, table rewrite behavior, index-build modes, and transaction support differ by engine/version. Use expand/migrate/contract: add compatible schema, deploy code that can read/write safely, backfill in bounded resumable batches, switch reads, then remove old state after every old process is gone.

Backfills need progress keys, throttling, retryability, and replication/lock monitoring. Adding a constraint may require validating existing rows separately. “Online” does not mean zero impact; test the exact vendor operation on representative data and retain a cancellation/roll-forward plan.

Production failure patterns

Check-then-insert races despite application validation; fix with a unique constraint and handled conflict.
firstOrCreate-style convenience methods race because lookup and insert are separate; the constraint remains authoritative.
A transaction holds locks while calling a provider, causing pool exhaustion and deadlocks.
A new index helps one query but doubles write cost or is unused because the predicate casts the column.
Tests on SQLite pass while production engine types, locking, JSON, or isolation differ.
A retry repeats an external side effect because only the database portion rolled back.
Replica reads miss a just-committed write; route read-your-write paths appropriately or expose consistency semantics.

Current and legacy context

Current: Verify PostgreSQL and MySQL behavior against the deployed major/minor and configuration; this page intentionally avoids claiming identical isolation semantics.
Common: MVCC, B-tree indexes, connection pools, read replicas, and online schema tools still require engine-specific evidence.
Legacy: MyISAM/non-transactional assumptions, implicit coercions, missing constraints, and long blocking migrations appear in older estates. Migrate by characterizing real data and writers first.