Concurrency Control in DBMS: How Locking, MVCC and Optimistic Strategies Keep Data Consistent

Concurrency control is the set of mechanisms a database management system (DBMS) uses to manage simultaneous transactions without corrupting data. When multiple users or processes read and write at the same time, uncontrolled access leads to data anomalies, such as lost updates, dirty reads and inconsistent results that can cascade through downstream systems.

Concurrency control enforces the “I” (Isolation) in ACID: concurrent transactions should produce the same result as if they ran one after another, a property known as serializability. Without it, a banking application could lose transfers, an inventory system could oversell products and an analytics pipeline could produce reports based on half-written data.

This guide covers the core concurrency problems, the major mechanisms for solving them — locking, multi-version concurrency control (MVCC) and optimistic and pessimistic strategies — along with isolation levels, deadlock handling and practical guidance for choosing the right approach for your workload.

Why concurrency control matters

Modern database systems routinely handle thousands to millions of concurrent transactions per second. Every time a user submits a query, updates a record or kicks off an ETL pipeline, the DBMS must coordinate that operation alongside every other operation happening at the same moment. Without concurrency control, interleaved transactions produce unpredictable, incorrect results.

Consider a concrete scenario: two users update the same bank account balance simultaneously. User A reads the balance ($1,000), subtracts $200 and writes $800. User B also reads $1,000, adds $500 and writes $1,500. The correct result depends on order — either $800 or $1,500 — but without coordination, one write simply overwrites the other. This is a classic lost update, one of several anomalies that concurrency control exists to prevent.

Concurrency control enforces serializability: the outcome of concurrent execution must match some serial ordering of those same transactions. This directly supports all four ACID guarantees — atomicity (all-or-nothing), consistency (valid state transitions), isolation (no interference) and durability (committed changes are permanent). This guarantees the foundation on which every application builds trust in its data.

Concurrency problems to solve

Before exploring solutions, it helps to understand the specific anomalies that arise when concurrent transactions run without proper controls.

• Dirty read. A transaction reads data written by another uncommitted transaction. If that second transaction rolls back, the first transaction based its logic on data that never actually existed. Dirty reads are among the most dangerous anomalies because they introduce phantom state into decision-making.
• Lost update. Two transactions read the same data item and then both write updated values. The second write overwrites the first without incorporating its changes, effectively erasing a valid update. The bank account scenario above is a textbook example.
• Non-repeatable read. A transaction reads the same row twice and gets different values because another transaction modified and committed the row between reads. This breaks any logic that depends on stable data within a single transaction.
• Phantom read. A transaction re-executes a range query and gets a different set of rows because another transaction inserted or deleted matching rows in the interval. Phantoms are particularly problematic for aggregate queries and reporting workloads.

These four anomalies are the “why” behind every concurrency control mechanism. Each mechanism prevents some or all of them, depending on the isolation level it enforces.

Lock-based concurrency control

Locking is the oldest and most intuitive approach to concurrency control. The DBMS assigns locks to data items — rows, pages or entire tables — before allowing transactions to access them. A lock acts as a gatekeeper: if another transaction already holds a conflicting lock, the requesting transaction must wait.

Shared and exclusive locks

• Shared (read) locks allow multiple transactions to read the same data simultaneously. Because no one is modifying the data, concurrent reads are safe.
• Exclusive (write) locks grant a single transaction sole access to a data item. While an exclusive lock is held, no other transaction can read or write that item. This prevents lost updates and dirty reads but also limits concurrency.

Lock granularity introduces an important trade-off. Row-level locks maximize concurrency because unrelated rows remain accessible, but they add overhead because the system must track many individual locks. Table-level locks are simpler and cheaper to manage but force unrelated transactions to wait, reducing throughput.

Two-phase locking (2PL)

Two-phase locking is the standard protocol for guaranteeing serializability through locks. It divides every transaction into two phases. During the growing phase, a transaction acquires all the locks it needs but never releases any. Once it releases its first lock, it enters the shrinking phase and can only release locks, never acquire new ones. This rule ensures that the order in which transactions lock data items is consistent, which in turn guarantees a serializable schedule.

Strict 2PL is a common variant that holds all locks until the transaction commits. This prevents cascading rollbacks, a situation where one aborted transaction forces other transactions that read its uncommitted data to abort as well. Most relational databases that use lock-based concurrency control implement some form of strict 2PL.

The downside of 2PL is that it introduces blocking — transactions must wait for locks held by others — and creates the conditions for deadlocks.

Multi-version concurrency control (MVCC)

MVCC takes a fundamentally different approach: instead of blocking access, it maintains multiple versions of each data item so that each transaction sees a consistent snapshot of the database. When a transaction writes a row, MVCC creates a new version rather than overwriting the existing one. Readers continue to see the version that was current when their transaction started.

The primary advantage is that readers never block writers and writers never block readers. This is a major performance benefit for read-heavy workloads, which is why MVCC is the dominant concurrency control mechanism in modern databases. PostgreSQL, MySQL/InnoDB, Oracle and most contemporary OLTP and OLAP systems use some form of MVCC.

Write conflicts are detected at commit time. If two transactions modify the same row, the first to commit wins and the second must retry. This optimistic approach to write conflict resolution keeps throughput high when conflicts are rare, which they usually are.

Snapshot isolation is the most common MVCC-based isolation level. Each transaction sees the database as it existed when the transaction started, preventing dirty reads and non-repeatable reads. However, snapshot isolation does allow a subtle anomaly called write skew, where two transactions each read overlapping data and then make writes based on what they read, resulting in a state that neither transaction would have produced alone. Serializable snapshot isolation (SSI) addresses this gap at the cost of additional overhead.

MVCC introduces its own trade-offs. Maintaining multiple versions consumes additional storage, and the system must periodically garbage-collect obsolete versions — a process known as VACUUM in PostgreSQL. Under heavy write contention, the retry cost and version management overhead can become significant.

Optimistic vs. pessimistic concurrency control

Beyond the specific mechanisms of locking and MVCC, concurrency control strategies fall into two philosophical camps: pessimistic and optimistic. Understanding which camp fits your workload is one of the most consequential architectural decisions a data team can make.

Pessimistic control

Pessimistic concurrency control assumes conflicts are likely and prevents them by acquiring locks before accessing data. This approach is best for write-heavy workloads where conflicts are frequent and the cost of rolling back and retrying a transaction is high. Financial ledger systems, where every write must be sequenced and verified, are a classic use case.

The downside is blocking. Under high concurrency, transactions queue up waiting for locks, which reduces throughput. In the worst case, competing lock requests create deadlocks.

Optimistic control

Optimistic concurrency control assumes conflicts are rare. Transactions execute freely without acquiring locks, operating on their own private copies of the data. At commit time, the system validates whether the transaction’s reads and writes conflict with any other committed transaction. If no conflicts are found, the transaction commits. If a conflict is detected, the transaction rolls back and retries.

This three-phase model — read, validate, write — excels in environments where most transactions don’t touch the same data. Content management systems, catalog browsing applications and reporting dashboards are typical examples.

The downside is wasted work. When conflicts are frequent, transactions repeatedly execute their full logic only to be rolled back at validation, consuming resources without producing results.

When to use which

The choice between pessimistic and optimistic control comes down to workload characteristics. High write contention, short transactions and low tolerance for retries point toward pessimistic control. Read-heavy workloads with low conflict probability and high concurrency requirements favor optimistic or MVCC-based approaches.

In practice, many production systems blend both strategies. A common pattern uses MVCC for general reads and writes while applying pessimistic locks on known hot-spot rows — for example, using SELECT ... FOR UPDATE to lock a specific row that multiple transactions are likely to contend for simultaneously.

Isolation levels and their trade-offs

Isolation levels define which concurrency anomalies a transaction can encounter. Stricter levels prevent more anomalies but reduce concurrency. The SQL standard defines four levels, and most databases implement some variant of them.

• Read uncommitted offers maximum concurrency but allows all anomalies, including dirty reads. It's rarely used in production because the risks far outweigh the performance gains.
• Read committed prevents dirty reads by ensuring a transaction only sees data committed before each statement executes. It allows non-repeatable reads and phantoms, which is an acceptable trade-off for most web applications and general-purpose workloads. PostgreSQL and Oracle use this as their default.
• Repeatable read prevents dirty reads and non-repeatable reads by guaranteeing that any row read during a transaction will return the same value if read again. Phantoms may still occur. MySQL/InnoDB defaults to this level.
• Serializable prevents all anomalies. Transactions behave as if they were executed one at a time, in some serial order. This is the highest consistency guarantee but comes with the lowest concurrency. It's typically reserved for financial, compliance or safety-critical workloads where absolute correctness is non-negotiable.

Most applications operate safely at Read Committed or Repeatable Read. Choosing Serializable is a deliberate trade-off that should be driven by specific business requirements rather than used as a default.

Deadlocks: prevention and resolution

A deadlock occurs when two or more transactions each hold locks that the other needs, creating a cycle of mutual waiting. Neither transaction can proceed, and without intervention, both would wait indefinitely.

• Detection. Most databases detect deadlocks by periodically checking for cycles in a wait-for graph — a data structure that maps which transaction is waiting for which lock. When a cycle is found, the DBMS selects a “victim” transaction and aborts it, freeing the locks so the remaining transactions can continue.
• Prevention. The most effective prevention strategy is to acquire locks in a consistent, predictable order across all transactions. If every transaction locks resources in the same sequence, circular wait conditions cannot form. Lock timeouts provide an additional safety net — if a transaction waits too long, it aborts rather than contributing to a potential deadlock.
• Resolution. The aborted victim transaction is rolled back and typically retried automatically by the application. Because deadlocks are usually infrequent, the retry cost is modest.

Practical tips for minimizing deadlocks: keep transactions as short as possible, access resources in a predictable order, set appropriate lock timeout values and monitor deadlock frequency as a system health signal.

Choosing the right concurrency control approach

There is no universal answer. The right mechanism depends on workload characteristics, consistency requirements and operational trade-offs. Several factors guide the decision.

• Read-to-write ratio. Workloads dominated by reads benefit from MVCC or optimistic control, which allow concurrent reads without blocking. Write-heavy workloads often need pessimistic locking to avoid the cost of frequent retries.
• Conflict frequency. When conflicts are rare — as they're in most analytical and web workloads — optimistic control delivers better throughput. When conflicts are frequent, the retry overhead makes pessimistic control more efficient overall.
• Transaction duration. Short transactions work well with locking because locks are held briefly. Long-running transactions benefit from MVCC, which avoids extended blocking.
• Consistency requirements. Strict requirements (financial, compliance) favor Serializable isolation with 2PL. Moderate requirements (web applications, catalogs) are well served by Read Committed with MVCC.
• Distributed vs. single-node. Distributed systems introduce coordination overhead — network latency, partition tolerance and consensus requirements — that makes concurrency control more complex and more expensive.

Many production systems combine strategies: MVCC as the default, pessimistic locks on known hot-spot rows and application-level retry logic for optimistic conflict resolution.

Concurrency control in distributed and analytical systems

Distributed databases face additional coordination challenges. Network latency between nodes, the need for partition tolerance and consensus requirements all increase the cost and complexity of concurrency control. Approaches include distributed 2PL, distributed MVCC with global timestamps — as used in Google Spanner’s TrueTime system — and consensus protocols like Raft and Paxos.

Analytical and lakehouse platforms handle concurrency differently than traditional OLTP databases. These systems are optimized for read-heavy, large-scan workloads with snapshot isolation rather than the row-level locking patterns common in transactional systems.

Databricks and Delta Lake use optimistic concurrency control for concurrent writes to the same table. Writes follow a three-stage process: read the latest table version to identify affected files, write new data files and then validate at commit time that no conflicting changes occurred. If transactions don’t conflict, they succeed. If a conflict is detected, automatic retry or conflict resolution handles the rest. Delta Lake implements a non-locking MVCC model, so readers always see a consistent snapshot while writers proceed independently.

ACID transactions on Delta Lake tables ensure data consistency even when multiple pipelines, users and queries access the same data simultaneously. For a detailed look at how row-level concurrency works in practice, the Databricks engineering blog provides a deep dive into the underlying mechanics.

This approach extends concurrency guarantees beyond traditional OLTP into data engineering, ETL and ML workloads where multiple writers and readers operate on shared datasets. By combining optimistic concurrency control, snapshot isolation and automatic conflict resolution, modern lakehouse platforms bring the reliability of database-grade transactions to the scale and flexibility of cloud data lakes.

Concurrency control without the complexity: how Databricks handles it

Understanding concurrency control theory is one challenge. Implementing it reliably across distributed data, multiple writers and diverse workloads is another. Teams that manage their own concurrency strategies spend significant engineering time configuring isolation levels, tuning lock behavior and building retry logic — time that doesn't move their actual work forward.

Databricks Lakebase eliminates that operational burden. Built on the Databricks Data Intelligence Platform, Lakebase is a fully managed, cloud-native database that brings transactional reliability to the lakehouse without requiring teams to implement or configure concurrency control themselves. Out of the box, it provides optimistic concurrency control, snapshot isolation for reads and write-serializable isolation for writes — the same mechanisms this article covers, applied automatically.

Because Lakebase uses optimistic concurrency control on Delta Lake, there are no locks to manage and no deadlocks to debug. Concurrent writes follow the read-validate-commit pattern described earlier in this guide: each transaction reads the latest table version, writes new data files and validates at commit time that no conflicting changes occurred. When multiple pipelines, users or queries write to the same table, Delta Lake's row-level conflict detection resolves non-overlapping changes automatically — even for concurrent MERGE, UPDATE and DELETE operations. Readers always see a consistent snapshot, unaffected by in-progress writes.

This isn't limited to analytical workloads. Lakebase extends transactional guarantees to operational workloads, data engineering, ETL pipelines and ML — all on the same platform. Instead of maintaining a separate OLTP database alongside a lakehouse and stitching them together with custom integration code, teams run everything through a single governed architecture. Data stays in open formats on low-cost cloud storage, with the transactional compute layer running independently on top.

The result: every concurrency control concept covered in this article — MVCC, optimistic validation, snapshot isolation, conflict resolution — works by default on Databricks. Teams can focus on their data and their workloads, not their concurrency strategy. Explore Lakebase to see how it works in practice.