Concurrency Control Strategies and Best Practices

Concurrency control is one of the most consequential design decisions in any database-backed system. When multiple transactions execute simultaneously, they can interfere with one another in ways that produce incorrect results — dirty reads, lost updates, phantom rows, and non-repeatable reads are all manifestations of insufficient concurrency control. At the same time, overly aggressive control mechanisms throttle throughput, introduce latency, and in the worst case create deadlocks that bring entire workloads to a halt. The challenge, then, is not simply preventing bad outcomes but doing so at the lowest possible cost to performance. Three primary strategies have emerged to meet this challenge — Optimistic Concurrency Control (OCC), Pessimistic Concurrency Control (PCC), and Multiversion Concurrency Control (MVCC) — each reflecting a different set of assumptions about how often conflicts actually occur and what the right response to them should be.

Optimistic Concurrency Control (OCC) is built on the premise that conflicts are rare. Rather than acquiring locks before accessing data, a transaction under OCC reads and writes freely during its execution phase, accumulating its changes in a private workspace. Only at commit time does the system perform a validation step, checking whether any of the data the transaction read or wrote has been modified by a concurrent transaction that already committed. If no conflict is found, the transaction commits and its changes become durable. If a conflict is detected, the transaction is rolled back and must be retried from scratch.

The most common way to implement OCC is with a version number or timestamp attached to each row. When a transaction reads a row, it records the version it saw. When it later tries to commit an update to that row, the database checks whether the current version still matches the recorded one. If another transaction has incremented the version in the interim, a mismatch is detected and the commit is rejected. A typical SQL pattern at the application layer looks like this:

-- Read the row and capture its version
SELECT id, balance, version
FROM accounts
WHERE id = 42;

-- Attempt the update, but only if the version has not changed
UPDATE accounts
SET balance = balance - 100,
    version = version + 1
WHERE id = 42
  AND version = :captured_version;

-- If zero rows were affected, a conflict occurred — retry the transaction

If the UPDATE affects zero rows it means the version has changed since the read, signaling that another transaction won the race. The application must then retry the entire transaction. This "zero rows affected" check is the application-layer equivalent of the database's own commit-time validation.

The principal advantage of OCC is that no locks are held during the transaction's execution, so concurrent readers and writers never block one another. In workloads where the same rows are rarely contended — analytics against slowly changing reference data, user-profile lookups, catalog browsing — OCC delivers excellent throughput. Its principal disadvantage is wasted work: a long transaction that reads dozens of tables and performs extensive computation may have all of that effort discarded at commit time. If the retry also fails, and the next one too, the system can fall into a livelock-like spiral under sustained high contention, making OCC a poor choice when many transactions compete for the same small set of hot rows.

Pessimistic Concurrency Control (PCC) takes the opposite stance: conflicts are expected, so it is better to prevent them up front by acquiring locks before accessing data. PCC relies on two fundamental lock types.

• Shared (read) locks can be held simultaneously by many transactions reading the same resource. A transaction that holds only a shared lock cannot modify the resource, and any transaction wishing to write must wait until all shared locks on that resource are released. This allows concurrent reads while serializing writes.
• Exclusive (write) locks grant sole ownership of a resource to one transaction. No other transaction may read or write that resource until the exclusive lock is released. This gives the writing transaction a guarantee that the data it modifies will not be read in a partially-updated state.

Lock granularity is a critical tuning dimension in PCC. A table-level lock is easy for the database to manage — it is a single lock object — but it prevents all other transactions from touching any row in that table, serializing access even when transactions work on completely disjoint rows. A row-level lock allows far more concurrency because two transactions updating different rows do not interfere at all, but the database may need to manage thousands or millions of individual lock objects for a single large batch operation, consuming significant memory and CPU. Page-level locks (locking a storage page containing multiple rows) sit between these extremes. Most modern relational databases default to row-level locking with automatic escalation to coarser granularity when the lock count grows too large.

The most dangerous pathology of PCC is the deadlock. A deadlock occurs when two or more transactions each hold a lock that the other needs, forming a circular wait from which no transaction can escape on its own:

-- Transaction A                  -- Transaction B
BEGIN;                            BEGIN;
UPDATE accounts SET ...           UPDATE orders SET ...
WHERE id = 1;  -- locks row 1     WHERE id = 99; -- locks row 99

UPDATE orders SET ...             UPDATE accounts SET ...
WHERE id = 99; -- waits for B     WHERE id = 1;  -- waits for A
                                  -- DEADLOCK!

Databases detect deadlocks by periodically scanning the lock dependency graph for cycles. When a cycle is found, the database selects one transaction as the victim — typically the one that has done the least work or holds the fewest locks — rolls it back, and allows the other to proceed. The rolled-back transaction may then be retried by the application. Deadlocks are not catastrophic in themselves, but a high deadlock rate is a strong signal of poor transaction design.

Multiversion Concurrency Control (MVCC) represents a fundamentally different architectural approach. Rather than using locks to mediate access, MVCC maintains multiple versions of each row simultaneously. When a transaction updates a row, the database does not overwrite the existing data; instead it writes a new version of the row and marks the old version with a deletion timestamp or transaction ID, keeping it accessible to any concurrent transaction that started before the update was committed.

The result is that every transaction operates against a consistent snapshot of the database as it existed at the moment the transaction began (or, depending on the isolation level, at the moment each statement began). Readers never block writers and writers never block readers, because they are looking at different versions of the same data. This is a significant throughput improvement over PCC for read-heavy workloads.

Consider two concurrent transactions in PostgreSQL, which uses MVCC natively:

-- Session A starts first
BEGIN;
SELECT balance FROM accounts WHERE id = 42;
-- Returns 1000 (version 1 of the row)

-- Session B commits an update concurrently
BEGIN;
UPDATE accounts SET balance = 900 WHERE id = 42;
COMMIT;
-- A new row version (version 2) now exists with balance = 900

-- Session A re-reads the same row
SELECT balance FROM accounts WHERE id = 42;
-- Still returns 1000 — Session A sees version 1, its snapshot
COMMIT;

Session A sees a perfectly consistent view of the data throughout its entire lifetime, unaffected by Session B's committed change, without any read locks being acquired. This behavior is called Snapshot Isolation and it eliminates dirty reads, non-repeatable reads, and phantom reads for ordinary SELECT statements.

The cost of MVCC is storage bloat. Old row versions accumulate on disk and must eventually be reclaimed. PostgreSQL uses a background process called VACUUM for this purpose; it scans tables for row versions no longer needed by any active transaction and marks that space as reusable. If VACUUM falls behind — because transactions run for a very long time holding snapshots open, or because the autovacuum process is misconfigured — table bloat can grow dramatically, degrading query performance. Oracle handles this differently, storing old versions in a separate undo tablespace rather than inline with the table, which keeps table files compact but shifts the bloat elsewhere.

Choosing the Right Strategy for Your Workload requires a clear-eyed analysis of several dimensions rather than defaulting to whatever the database offers out of the box.

• Read-to-write ratio: A workload dominated by reads benefits enormously from MVCC or OCC, both of which avoid read locks. A workload with frequent writes to the same rows — financial ledgers, inventory counts, auction bids — sees more conflicts under OCC and more lock contention under PCC, requiring careful design regardless of the strategy chosen.
• Transaction duration and complexity: Short, simple transactions are cheap to roll back and retry, making OCC practical. Long transactions that assemble data from many tables, perform complex calculations, and then write results are very expensive to discard. If a five-minute transaction fails at commit time and must restart, the system effectively wastes ten minutes of CPU for every successful commit. PCC may be preferable here even at some throughput cost, because it surfaces contention early rather than at the end.
• Database engine support: Every major database has a preferred concurrency model. PostgreSQL and Oracle are optimized for MVCC. MySQL's InnoDB engine also uses MVCC. SQL Server offers both pessimistic locking (the default) and MVCC-based snapshot isolation as an opt-in. Forcing a strategy the engine was not built for — for example, simulating MVCC with application-managed version tables in a lock-based engine — usually produces worse results than working with the engine's native mechanisms.
• Realistic load testing: Single-user benchmarks measure raw execution speed but reveal nothing about contention. A strategy that looks excellent under single-user testing may collapse under 200 concurrent users all updating the same account. Always test under a concurrency level and data access pattern that realistically approximates production before locking in a strategy.

Practical Design Patterns for Consistency Without Performance Loss apply regardless of which high-level strategy the database employs. These are engineering habits that reduce contention, minimize rollback rates, and keep the system well-behaved under load.

• Keep transactions short. Every moment a transaction stays open, it either holds locks (under PCC) or accumulates risk of a conflict at commit time (under OCC), and it keeps a snapshot alive (under MVCC, preventing cleanup of old row versions). Move any computation that does not need to be atomic — formatting output, calling external APIs, making decisions based on data already read — outside the transaction boundaries. Open the transaction, do the minimum necessary data work, and commit immediately.
• Access resources in a consistent order. Deadlocks under PCC almost always arise from transactions acquiring locks in different sequences. If Transaction A locks the accounts table then the orders table, and Transaction B locks the orders table then the accounts table, a deadlock is possible. Enforcing a global ordering — always lock accounts before orders, always update rows in ascending primary key order — eliminates the circular dependency and prevents the deadlock entirely, often without any schema or index changes.
• Implement optimistic locking at the application layer when necessary. Not every database natively exposes version-number-based OCC, but any application can implement it with a version integer or updated_at timestamp column. The pattern is: read the row and capture the version; include AND version = :v in every UPDATE's WHERE clause; check the affected row count; retry if it is zero. This technique works across virtually any relational database and imposes negligible overhead in low-contention scenarios.
• Shard hot rows. A single row that every transaction must update — a global counter, a shared account balance, a site-wide inventory number — becomes a serialization bottleneck regardless of which concurrency strategy is in use. Counter sharding distributes the write load: instead of one row holding the total count, maintain ten rows each holding a partial count and sum them at read time. Writes are distributed across ten rows, reducing contention by roughly a factor of ten. Amazon's DynamoDB, for example, formally documents this pattern as a best practice for high-write counters.
• Choose the lowest isolation level that still meets correctness requirements. SQL defines four standard isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — each preventing a progressively larger set of anomalies at progressively greater cost. Most applications do not actually need full Serializable isolation; Read Committed is the default in PostgreSQL and Oracle for good reason. Running at Serializable when Read Committed is sufficient imposes unnecessary locking overhead (under PCC) or conflict-detection overhead (under Serializable Snapshot Isolation). Understand exactly which anomalies your application must prevent and choose the level accordingly.

Monitoring and Tuning Concurrency in Production is an ongoing discipline, not a one-time configuration exercise. Workloads evolve: new features introduce new query patterns, data volumes grow, and what was a rare conflict at 100 users becomes a constant collision at 10,000.

• Track lock wait times and deadlock rates. Most databases expose these through system views or performance schema tables. In PostgreSQL, pg_locks and pg_stat_activity reveal which queries are waiting on which locks and for how long. In SQL Server, the sys.dm_exec_requests and sys.dm_os_wait_stats DMVs provide similar visibility. A sudden spike in lock waits often correlates with a schema migration that changed an index, a new batch job introduced to production, or a query regression that turned a fast point lookup into a full table scan (dramatically expanding the set of rows touched and therefore the lock scope).
• Monitor table bloat and vacuum lag in MVCC systems. In PostgreSQL, the pg_stat_user_tables view reports n_dead_tup, the number of dead (obsolete) row versions in each table. A table where n_dead_tup is consistently large relative to n_live_tup indicates that VACUUM is not keeping up. Check pg_stat_bgwriter for autovacuum activity and consider increasing autovacuum_vacuum_scale_factor aggressiveness or running manual VACUUM on the most bloated tables. Long-running transactions are the most common culprit: a transaction open for hours holds a snapshot that prevents VACUUM from removing any row version newer than that snapshot's start time.
• Profile individual transactions for lock scope and duration. Aggregate statistics tell you that a problem exists; query-level profiling tells you where. Tools like PostgreSQL's auto_explain module, SQL Server's Extended Events, or application-level tracing (OpenTelemetry, for instance) can reveal which specific transactions hold locks for an unusually long time. Common findings include transactions that open with a lock-acquiring UPDATE and then spend most of their time doing network I/O or application logic before committing — a pattern easily remedied by deferring the UPDATE to just before the commit.
• Establish baselines before problems occur. Concurrency issues are hard to diagnose when you do not know what normal looks like. Capture baseline metrics for lock wait times, deadlock frequency, average transaction duration, MVCC bloat ratios, and query latency percentiles during a representative normal workload. Store these time-series in a monitoring system. When anomalies appear — and they will — the baseline immediately answers the question "is this a concurrency problem or just higher load?" and guides the investigation toward the right strategy for resolution.

The following table summarizes the key trade-offs among the three major concurrency control strategies to aid in workload-appropriate selection:

Effective concurrency control is ultimately about matching the chosen strategy to the observed characteristics of the workload, implementing it with disciplined transaction design, and maintaining visibility into its behavior as the system evolves. No single strategy dominates in all scenarios; the right answer emerges from understanding the trade-offs deeply enough to apply them appropriately.