Concurrency Control in SQL

Introduction

Concurrency control is the collection of database and application mechanisms that let overlapping transactions access shared data without producing invalid committed results. It decides when work can proceed, when it must wait, which versions a query can see, when a conflict causes failure, and which operations must be retried.

The goal is not to force every request into a single line. Sequential execution is easy to reason about but wastes capacity and increases latency. A good design permits independent work to overlap while coordinating only the data and business rules that truly conflict.

What Is Concurrency Control?

A database may serve thousands of reads, inserts, updates, and deletes at once. Concurrency control ensures those interleaved operations preserve the isolation promises of each transaction and the integrity rules of the database. Locks, row versions, timestamps, validation, constraints, and transaction retries all contribute.

Consider a product with one unit remaining. Two customers can both read a quantity of one if the workflow is not protected. Correct concurrency design ensures that only one purchase can decrement the final unit, while the other receives a clear out-of-stock result or safely retries against current state.

Why Concurrency Control Matters

Concurrent defects often pass ordinary tests because each transaction is correct in isolation. The failure appears only under a specific overlap. Banking, inventory, reservations, payroll, enrollment, appointments, and publishing systems all depend on mechanisms that preserve balances, limits, uniqueness, ownership, and workflow transitions under simultaneous requests.

Concurrency control also supports throughput. Overly broad locks can be correct but unnecessarily slow. Weak isolation can be fast but invalid for a shared invariant. Engineering chooses the narrowest coordination that guarantees correctness and then measures its behavior under realistic load.

Concurrent Transactions

Transactions are concurrent when their lifetimes overlap. Their instructions do not need to execute on separate processors at the exact same instant; a single engine can interleave operations from A and B. What matters is that one transaction has not completed before another begins interacting with related state.

Sequential vs Concurrent Execution

FactorSequentialConcurrent
OverlapNoneTransactions interleave
Throughput potentialLowerHigher
Isolation anomaliesAvoided between sequential transactionsPossible without controls
ContentionMinimalPossible on shared resources
Reasoning complexityLowerHigher

The correct benchmark is not maximum simultaneous activity. It is useful throughput while committed state remains valid and latency stays predictable.

How Concurrency Control Works

  1. An application begins a transaction and issues statements.
  2. The database identifies rows, keys, ranges, and metadata involved.
  3. Isolation and engine rules select locks, versions, or validation.
  4. Compatible work proceeds; conflicting work waits, fails, or reads another version.
  5. Constraints and commit checks validate the resulting state.
  6. The transaction commits or rolls back and releases resources.
  7. The application retries documented transient conflicts when appropriate.

This is a system, not one SQL command. Schema design and transaction boundaries are as important as the chosen isolation level.

Dirty Reads

A dirty read occurs when one transaction observes another transaction's uncommitted change. If the writer rolls back, the reader acted on a value that never became committed state. Read Committed and stronger levels prevent ordinary dirty reads, while some products do not provide true dirty reads even when a similarly named mode exists.

Non-Repeatable Reads

A non-repeatable read occurs when a transaction reads the same identified row twice and receives different committed values because another transaction modified or deleted it between statements. Statement-level snapshots commonly allow this; a transaction snapshot or retained read lock can prevent it.

Phantom Reads

A phantom changes the set of rows matching a repeated predicate. Another transaction can insert, delete, or update a row into or out of the set. Protecting existing rows is not always enough because a future matching key may occupy a gap or logical predicate range.

Lost Updates

A lost update occurs when concurrent read-modify-write workflows overwrite or omit one another's effect. An atomic expression such as SET balance = balance + ?, a row lock, or an optimistic version condition can preserve both changes. Blindly writing a value calculated from an earlier read is risky.

Write Skew

Write skew occurs when transactions read a shared condition but update different rows, so no direct write-write conflict occurs even though their combined outcome violates the condition. Snapshot isolation can permit this. Serializable isolation, an explicit guard row, or a database constraint may be needed.

Dirty Writes

A dirty write would overwrite another transaction's uncommitted modification. Mainstream transactional databases prevent it because recovery could not reliably determine which value to restore. Even weak read isolation still requires coordination between conflicting writers.

Anomaly Comparison

AnomalyWhat changes?Typical protection
Dirty readUncommitted value becomes visibleRead Committed or stronger
Non-repeatable readKnown row changesRepeatable snapshot or retained lock
PhantomPredicate membership changesSnapshot, range protection, or Serializable
Lost updateOne write effect disappearsAtomic update, lock, or version check
Write skewDifferent-row writes violate shared ruleSerializable, guard resource, or constraint
Dirty writeUncommitted write is overwrittenWrite locking or equivalent control

Main Control Techniques

Databases combine lock-based control, Multi-Version Concurrency Control (MVCC), timestamp ordering, optimistic validation, and serializable conflict detection. Applications add atomic SQL, version columns, idempotent retries, resource ordering, and business-level constraints. No single technique is ideal for every workload.

Selecting a Technique by Conflict Shape

Start with the resource and decision being protected. A counter or inventory decrement usually fits one atomic conditional update. A long-running user edit fits an optimistic version. A short workflow that must inspect and then change one current row can use a locking read. A rule about a set of rows may need a unique allocation model, a guard row, range protection, or Serializable. Read-only reports generally need a stable snapshot rather than writer-blocking locks.

Then evaluate conflict frequency and recovery cost. Pessimistic control is reasonable when collisions are frequent and waiting is cheaper than discarding expensive work. Optimistic validation preserves concurrency when conflicts are rare and retries are inexpensive. If a rule can be represented as a constraint, use that constraint regardless of the surrounding strategy because it protects every write path. The mechanism should match the invariant, not merely the SQL statement currently used by one service.

Lock-Based Concurrency Control

Locks protect resources with modes such as shared and exclusive. Compatible requests coexist; incompatible requests wait or fail. Locks can cover rows, keys, ranges, pages, tables, or metadata. They provide direct conflict coordination but can cause blocking, deadlocks, and memory overhead.

Shared and Exclusive Locks

A shared mode commonly protects a locking read and can coexist with other shared holders. An exclusive mode protects modification and conflicts with incompatible access. MVCC reads may not request shared row locks at all, so the simple matrix is conceptual rather than universal across products.

Two-Phase Locking

Two-Phase Locking has a growing phase in which a transaction acquires locks and a shrinking phase in which it releases them. Strict variants hold write locks until commit or rollback, which hides uncommitted writes and simplifies recovery. Real databases may blend lock protocols with versions and product-specific optimizations.

Timestamp-Based Control

Timestamp-ordering techniques assign transactions or versions an order and reject operations that violate that order. They can avoid some waiting but may abort work under conflict. Timestamp concepts also appear in MVCC visibility, distributed ordering, and validation systems, although implementation details differ greatly.

Optimistic Concurrency Control

Optimistic control allows work to proceed without reserving data for the entire business operation and validates that assumptions remain current before commit or update. A version column is a common application pattern:

UPDATE products
SET price = ?, version = version + 1
WHERE product_id = ?
  AND version = ?;

Zero affected rows means the version changed. The application must reload, merge, reject, or retry. Optimistic control fits low-conflict workloads but repeated collisions waste work.

Pessimistic Concurrency Control

Pessimistic control reserves a resource before a likely conflict, often with SELECT ... FOR UPDATE or an immediate conditional write. It fits short, high-value operations with frequent collision. Its costs are waiting, deadlocks, reduced concurrency, and sensitivity to long transaction duration.

Optimistic vs Pessimistic Control

FactorOptimisticPessimistic
Conflict handlingValidate laterReserve early
Best fitRare collisionFrequent collision
Main costFailed validation and retryBlocking and deadlocks
User think timeUsually suitablePoor fit for open transactions

What Is MVCC?

MVCC stores or reconstructs multiple versions of a logical row so readers can see a consistent version without waiting for many writers. Visibility rules choose which versions belong to a statement or transaction snapshot. Writers still coordinate conflicting updates, uniqueness, foreign keys, and metadata.

How MVCC Works

When a row changes, the engine preserves enough information for older snapshots while exposing the new version to transactions for which it is committed and visible. Old versions are later reclaimed when no active snapshot needs them. Long transactions can delay cleanup and increase storage or vacuum pressure.

MVCC vs Lock-Based Reads

Lock-based reads coordinate by waiting behind incompatible writers or holding protection against later writers. MVCC reads commonly return an older committed version instead. Both approaches still use locks or equivalent mechanisms for writes. Product settings can also enable versioned reads in an otherwise lock-oriented platform.

Isolation Levels

An isolation level defines observable transaction guarantees, not a portable recipe for exact locks. The same level name can use statement snapshots, transaction snapshots, retained locks, range locks, or conflict validation depending on the database.

Read Uncommitted

Read Uncommitted offers the weakest standard read isolation and can permit dirty reads on supporting systems. It does not remove write coordination. It is unsuitable for decisions involving balances, ownership, limits, or any state that must correspond to committed truth.

Read Committed

Read Committed prevents ordinary dirty reads. Each statement commonly receives a current committed view, so later statements may see changed rows or new predicate matches. This is often an effective default for short atomic writes, but a check-then-write workflow may need explicit protection.

Repeatable Read

Repeatable Read keeps previously read data stable through retained locks or a transaction snapshot. SQL-standard phantoms can remain possible, while some products provide stronger snapshot behavior. Stable visibility does not necessarily enforce a multi-row capacity invariant against concurrent writers.

Serializable

Serializable ensures committed outcomes are equivalent to some serial transaction order. Lock-based engines may protect ranges and block conflicts. Serializable snapshot systems may track dependencies and abort unsafe schedules. Applications must be ready for deadlocks, serialization failures, or both.

Isolation Comparison

LevelTypical visibilityPrimary tradeoff
Read UncommittedMay include uncommitted dataWeak correctness
Read CommittedCommitted view per statementResults can change
Repeatable ReadStable rows or transaction snapshotMore retention or protection
SerializableSerializable committed outcomeBlocking or aborts

Inventory Example Setup

CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    available_quantity INTEGER NOT NULL,
    version INTEGER NOT NULL DEFAULT 0,
    CHECK (available_quantity >= 0)
);

INSERT INTO inventory
    (product_id, product_name, available_quantity)
VALUES (500, 'Wireless Keyboard', 1);

The Check-Then-Update Problem

Two sessions read quantity one. Each decides a purchase is allowed and later writes zero based on its earlier read. Both customers may receive confirmation even though one unit existed. A final nonnegative quantity does not prove both logical purchases were valid; the workflow lost one reservation decision.

Solution 1: Atomic Conditional Update

UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 500
  AND available_quantity > 0;

Exactly one concurrent statement can change one to zero. The other affects no rows and reports out of stock. This is usually simpler and shorter than reading first.

Solution 2: SELECT FOR UPDATE

BEGIN;
SELECT available_quantity
FROM inventory
WHERE product_id = 500
FOR UPDATE;

-- Validate, then update on the same connection.
UPDATE inventory
SET available_quantity = available_quantity - 1
WHERE product_id = 500;
COMMIT;

The locking read serializes access to the selected row. Keep the transaction short and recheck every condition after waiting. Syntax and range behavior vary by database.

Solution 3: Version Checking

UPDATE inventory
SET available_quantity = ?,
    version = version + 1
WHERE product_id = 500
  AND version = ?;

Only the session holding the current version succeeds. The loser reloads or reports conflict. For counters, the atomic decrement is generally more direct; version checks are valuable when a user edits a larger object over time.

Blocking, Deadlocks, and Serialization Failures

Blocking is a one-way wait behind an incompatible holder. A deadlock is a circular wait that forces the engine to abort a victim. A serialization failure can result from dependency validation even without a conventional lock cycle. All can be expected outcomes of correct control, but their retry rules and diagnostics differ.

PostgreSQL Concurrency Control

PostgreSQL uses MVCC snapshots for ordinary reads. Read Committed gives each command a new snapshot; Repeatable Read uses one transaction snapshot; Serializable adds Serializable Snapshot Isolation and can abort dangerous dependency patterns. Row locks and table modes coordinate writes and explicit locking operations.

Deadlocks commonly report SQLSTATE 40P01, while serialization failures use 40001. Both can be retryable at the complete transaction boundary.

MySQL InnoDB Concurrency Control

InnoDB combines MVCC consistent reads with record, gap, and next-key locks. Repeatable Read commonly gives consistent reads a transaction snapshot, while locking reads and modifications use current-read behavior. Index selection strongly affects protected records and gaps.

Deadlocks commonly return error 1213. Lock wait timeouts and deadlocks are distinct conditions and require deliberate retry classification.

SQL Server Concurrency Control

SQL Server provides shared, update, exclusive, intent, schema, and key-range modes, with optional row-versioned Read Committed and Snapshot isolation. Lock escalation can replace many fine locks with coarser protection. Serializable uses key-range behavior where required.

Error 1205 identifies a deadlock victim. Snapshot update conflicts and other transient failures have different signals and should not be grouped by message text alone.

Oracle Concurrency Control

Oracle uses multiversion read consistency, so ordinary readers generally do not block writers. Modified rows are locked, and SELECT ... FOR UPDATE reserves current rows. Read Committed uses statement consistency, while Serializable and Read Only transactions use transaction-level snapshot behavior.

Concurrent conflicts can produce errors such as deadlock or serialization-related failures. Application handling must follow Oracle's documented transaction state for each error.

SQLite Concurrency Control

SQLite coordinates access to a database file. WAL mode allows readers and a writer to overlap more effectively, but only one writer proceeds at a time. Transaction mode and journal mode shape when locks are acquired and when BUSY or LOCKED conditions appear.

Keep writes short and use a bounded busy timeout. Server-database row-lock assumptions do not transfer directly to SQLite.

Database Comparison

DatabaseRead approachNotable control mechanism
PostgreSQLMVCC snapshotsRow locks and SSI at Serializable
MySQL InnoDBMVCC consistent readsRecord, gap, and next-key locks
SQL ServerLocking or row versioningRich lock hierarchy and Snapshot options
OracleMultiversion read consistencyRow locks and transaction snapshots
SQLiteJournal-mode snapshotsFile coordination with one writer

Banking and Money Transfers

A transfer must validate funds, debit one account, credit another, and record the operation atomically. Access accounts in a consistent order, use guarded balance updates, and enforce one operation ID. Do not keep database locks while waiting for a remote payment network.

Inventory and Ticket Booking

Inventory, seats, and appointment slots require atomic claims. Conditional updates, pre-created slot rows, unique assignments, or guarded counters make the shared resource explicit. Counting existing reservations and then inserting without protection is unsafe under concurrency.

Enrollment and Payroll

Course capacity resembles inventory, while payroll applies related changes across many employees and tables. Capacity constraints need an atomic allocation design. Payroll work benefits from stable ordering, bounded batches, and scheduling that avoids overlap with corrections or benefits jobs.

Content Publishing Workflows

Editors can overwrite one another when a draft is open for a long time. A version column gives clear optimistic conflict detection without holding a database transaction during user think time. Publishing transitions can additionally use a conditional status update and a unique published-version rule.

Setting Isolation in JDBC

try (Connection connection = dataSource.getConnection()) {
    connection.setAutoCommit(false);
    connection.setTransactionIsolation(
        Connection.TRANSACTION_SERIALIZABLE
    );

    executeBusinessTransaction(connection);
    connection.commit();
}

Confirm driver mapping and database support. Isolation is often connection state, so pools must restore it. Every read and write in the unit must use the same connection and actual transaction boundary.

Optimistic Control in JDBC

Read the version with the business fields, include it in the update predicate, and inspect the update count. Zero rows means conflict, not success. Roll back and return a domain-level conflict or repeat the complete operation after reloading current state.

Handling Transient Failures

Deadlocks, serialization failures, and selected timeouts may be retryable. Classify by vendor code and SQLState, roll back, discard the failed transaction snapshot, wait with bounded exponential backoff and jitter, and execute the entire unit again. Do not retry all SQL exceptions.

External side effects need an idempotency key or transactional outbox. Otherwise a database retry can duplicate messages, emails, or payment requests.

Serializability

A schedule is serializable when its committed effect is equivalent to some serial order of the same transactions. Transactions can physically overlap while still producing a serializable outcome. This is the strongest general isolation goal, but it can require waiting or aborting conflicting schedules.

Conflict Serializability

Two operations conflict when they access the same logical item, belong to different transactions, and at least one writes. Read-write, write-read, and write-write ordering can therefore constrain the possible serial order. Nonconflicting operations can be swapped without changing the result.

Precedence Graph

A precedence graph has one node per transaction and an edge from A to B when A's conflicting operation occurs before B's. An acyclic graph has a topological order and is conflict-serializable. A cycle means no equivalent serial order can satisfy those conflict edges.

Snapshot Isolation

Snapshot isolation gives a transaction a stable view and rejects certain direct write conflicts. It prevents many read anomalies and reduces blocking, but can permit write skew when transactions update different rows after reading one shared condition. Snapshot isolation is not automatically equivalent to Serializable.

Serializable Snapshot Isolation

Serializable Snapshot Isolation tracks dependency patterns among snapshot transactions and aborts schedules that could produce a nonserializable result. PostgreSQL uses this strategy. It preserves much of MVCC's read concurrency but requires applications to handle serialization failures correctly.

Constraints as Concurrency Control

A unique key, foreign key, check constraint, exclusion rule, or guarded counter lets the database reject invalid state at the write boundary. Constraints are often stronger and simpler than relying on every application path to perform a pre-check correctly.

Not every cross-row business rule is expressible as a portable constraint. In those cases, redesign around explicit resources, use Serializable, or centralize the guarded operation in one database routine.

Connection Pools

Pooled connections can retain autocommit, isolation, read-only settings, and failed transaction state. Always commit or roll back in a guaranteed cleanup path and let the pool restore session state. An idle open transaction can hold locks and old snapshots even while doing no CPU work.

Retry orchestration belongs outside the transaction method so every attempt receives a fresh transaction context. Retrying inside a rollback-only framework transaction cannot recover it.

Distributed Systems

A local database transaction cannot atomically protect arbitrary remote services without a distributed transaction protocol. Holding locks across HTTP calls creates long waits and cross-system failure chains. Sagas, outbox patterns, durable workflow states, idempotent consumers, and compensation are usually better boundaries.

Global uniqueness or inventory ownership should have a clear authority. If several services can independently approve the same scarce resource, local isolation alone cannot preserve the global invariant.

Consistency and Performance

Concurrency is useful work that overlaps; consistency is preservation of required rules; performance is throughput and latency under load. Weakening correctness is not a valid optimization. Instead, narrow the protected resource, shorten transactions, improve indexes, partition hot data, and move read-only analytics to suitable infrastructure.

Common Causes of Concurrency Problems

  • Check-then-write logic without atomic protection.
  • Transactions that begin before slow non-database work.
  • Different resource order across code paths.
  • Missing indexes that broaden scans and lock ranges.
  • Isolation chosen by default rather than invariant.
  • Retries that repeat external side effects.
  • Connection pools returning open or failed transactions.
  • Tests that never coordinate concurrent sessions.

Monitoring Concurrency

Monitor transaction age, lock waits, blockers, deadlocks, serialization failures, retries, version retention, pool wait time, query plans, and latency percentiles. Correlate database events with an operation name and safe request identifier without exposing sensitive bind values.

  • PostgreSQL: activity, lock, statistics, and conflict views.
  • MySQL: Performance Schema, InnoDB transaction, lock, and deadlock data.
  • SQL Server: request, waiting-task, transaction-lock DMVs, Query Store, and Extended Events.
  • Oracle: session, transaction, lock, and wait-event performance views.
  • SQLite: busy errors, write duration, checkpoint behavior, and journal configuration.

Concurrency Control Best Practices

  1. State the business invariant before selecting a mechanism.
  2. Prefer constraints and atomic conditional statements.
  3. Keep transactions short and free from remote calls.
  4. Use one consistent order for shared resources.
  5. Index predicates involved in protected decisions.
  6. Choose isolation from required behavior, not habit.
  7. Use optimistic control for long user edits.
  8. Retry only documented transient failures as complete units.
  9. Make retries idempotent and bounded.
  10. Measure correctness, contention, and latency under load.

Concurrency Test Cases

  1. Two customers attempt to buy the last item.
  2. Two editors update one versioned document.
  3. Transfers lock accounts in opposite and consistent order.
  4. Concurrent inserts test a unique constraint.
  5. Snapshot transactions attempt a write-skew schedule.
  6. Read Committed and Repeatable Read repeat one query.
  7. Serializable produces a retryable conflict.
  8. A pool cleans up after rollback and timeout.
  9. Large batches overlap on key ranges.
  10. Retries preserve exactly one external logical effect.

Coordinate sessions with barriers or latches rather than arbitrary sleeps, and run against the production database family, schema, indexes, and isolation configuration.

Common Mistakes

  • Assuming one isolation name behaves identically everywhere.
  • Calling every changed result a dirty read.
  • Believing MVCC eliminates write conflicts.
  • Using SELECT FOR UPDATE during user think time.
  • Retrying only the final failed statement.
  • Using unlimited retries without jitter.
  • Depending on pre-checks instead of constraints.
  • Ignoring triggers, foreign keys, and hidden resource access.
  • Testing sequentially and assuming concurrency safety.
  • Optimizing throughput without verifying final state.

Interview Questions and Answers

What is concurrency control?

It is the set of mechanisms that coordinates overlapping transactions so committed outcomes preserve isolation and data integrity.

What is the difference between MVCC and locking?

MVCC lets many readers select older committed versions, while locking coordinates incompatible access directly. Most MVCC engines still lock or validate writes.

When should optimistic control be used?

It fits low-conflict or long-edit workflows where holding a database transaction is undesirable and the application can handle version conflicts.

Does Snapshot isolation guarantee serializability?

Not generally. It can allow write skew unless the database adds serializable dependency detection or the design adds another guard.

How should transient conflicts be retried?

Roll back, wait with bounded jitter, begin a fresh transaction, and repeat the complete idempotent unit using documented error codes.

Practical Exercises

  1. Demonstrate a lost update with two sessions.
  2. Replace it with an atomic conditional update.
  3. Add a version column and test one winning editor.
  4. Compare Read Committed and Repeatable Read snapshots.
  5. Create and then prevent a two-row deadlock.
  6. Demonstrate a capacity write-skew schedule.
  7. Inspect live blockers with native database views.
  8. Implement bounded JDBC retry and idempotency.

Quick Reference

TechniquePrimary idea
LocksReserve resources against incompatible access
MVCCRead visible versions from a snapshot
Optimistic versionValidate that state did not change
Atomic SQLCheck and modify in one statement
ConstraintReject invalid final state
SerializableGuarantee an equivalent serial outcome
RetryRepeat a fresh complete transaction after a transient conflict
IdempotencyMultiple attempts produce one logical effect

Production Design Workflow

  1. Write the invariant and the invalid concurrent outcome.
  2. Draw the transactions and possible interleavings.
  3. Identify rows, predicates, ranges, and external effects.
  4. Choose constraints and atomic writes first.
  5. Select isolation or explicit control for remaining risk.
  6. Define complete retry and idempotency behavior.
  7. Verify indexes and actual execution plans.
  8. Build deterministic multi-session tests.
  9. Load-test hot paths and failure recovery.
  10. Monitor committed correctness and operational cost.

Key Takeaway

SQL concurrency control allows independent transactions to overlap while coordinating conflicts that could invalidate committed state. Locks, MVCC, timestamps, optimistic validation, constraints, isolation levels, and retries are complementary tools rather than competing universal solutions.

Begin with the business invariant, express it through atomic writes and constraints where possible, keep transactions short, and select the weakest remaining mechanism that still guarantees correctness. Deterministic tests, complete idempotent retries, and production monitoring turn concurrency from an intermittent defect source into an explicit part of system design.