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
| Factor | Sequential | Concurrent |
|---|---|---|
| Overlap | None | Transactions interleave |
| Throughput potential | Lower | Higher |
| Isolation anomalies | Avoided between sequential transactions | Possible without controls |
| Contention | Minimal | Possible on shared resources |
| Reasoning complexity | Lower | Higher |
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
- An application begins a transaction and issues statements.
- The database identifies rows, keys, ranges, and metadata involved.
- Isolation and engine rules select locks, versions, or validation.
- Compatible work proceeds; conflicting work waits, fails, or reads another version.
- Constraints and commit checks validate the resulting state.
- The transaction commits or rolls back and releases resources.
- 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
| Anomaly | What changes? | Typical protection |
|---|---|---|
| Dirty read | Uncommitted value becomes visible | Read Committed or stronger |
| Non-repeatable read | Known row changes | Repeatable snapshot or retained lock |
| Phantom | Predicate membership changes | Snapshot, range protection, or Serializable |
| Lost update | One write effect disappears | Atomic update, lock, or version check |
| Write skew | Different-row writes violate shared rule | Serializable, guard resource, or constraint |
| Dirty write | Uncommitted write is overwritten | Write 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
| Factor | Optimistic | Pessimistic |
|---|---|---|
| Conflict handling | Validate later | Reserve early |
| Best fit | Rare collision | Frequent collision |
| Main cost | Failed validation and retry | Blocking and deadlocks |
| User think time | Usually suitable | Poor 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
| Level | Typical visibility | Primary tradeoff |
|---|---|---|
| Read Uncommitted | May include uncommitted data | Weak correctness |
| Read Committed | Committed view per statement | Results can change |
| Repeatable Read | Stable rows or transaction snapshot | More retention or protection |
| Serializable | Serializable committed outcome | Blocking 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
| Database | Read approach | Notable control mechanism |
|---|---|---|
| PostgreSQL | MVCC snapshots | Row locks and SSI at Serializable |
| MySQL InnoDB | MVCC consistent reads | Record, gap, and next-key locks |
| SQL Server | Locking or row versioning | Rich lock hierarchy and Snapshot options |
| Oracle | Multiversion read consistency | Row locks and transaction snapshots |
| SQLite | Journal-mode snapshots | File 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
- State the business invariant before selecting a mechanism.
- Prefer constraints and atomic conditional statements.
- Keep transactions short and free from remote calls.
- Use one consistent order for shared resources.
- Index predicates involved in protected decisions.
- Choose isolation from required behavior, not habit.
- Use optimistic control for long user edits.
- Retry only documented transient failures as complete units.
- Make retries idempotent and bounded.
- Measure correctness, contention, and latency under load.
Concurrency Test Cases
- Two customers attempt to buy the last item.
- Two editors update one versioned document.
- Transfers lock accounts in opposite and consistent order.
- Concurrent inserts test a unique constraint.
- Snapshot transactions attempt a write-skew schedule.
- Read Committed and Repeatable Read repeat one query.
- Serializable produces a retryable conflict.
- A pool cleans up after rollback and timeout.
- Large batches overlap on key ranges.
- 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 UPDATEduring 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
- Demonstrate a lost update with two sessions.
- Replace it with an atomic conditional update.
- Add a version column and test one winning editor.
- Compare Read Committed and Repeatable Read snapshots.
- Create and then prevent a two-row deadlock.
- Demonstrate a capacity write-skew schedule.
- Inspect live blockers with native database views.
- Implement bounded JDBC retry and idempotency.
Quick Reference
| Technique | Primary idea |
|---|---|
| Locks | Reserve resources against incompatible access |
| MVCC | Read visible versions from a snapshot |
| Optimistic version | Validate that state did not change |
| Atomic SQL | Check and modify in one statement |
| Constraint | Reject invalid final state |
| Serializable | Guarantee an equivalent serial outcome |
| Retry | Repeat a fresh complete transaction after a transient conflict |
| Idempotency | Multiple attempts produce one logical effect |
Production Design Workflow
- Write the invariant and the invalid concurrent outcome.
- Draw the transactions and possible interleavings.
- Identify rows, predicates, ranges, and external effects.
- Choose constraints and atomic writes first.
- Select isolation or explicit control for remaining risk.
- Define complete retry and idempotency behavior.
- Verify indexes and actual execution plans.
- Build deterministic multi-session tests.
- Load-test hot paths and failure recovery.
- 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.