Concurrency control
Concurrency control is the set of techniques a database uses to let multiple transactions execute at the same time without violating data consistency or isolation guarantees.
Overview
Whenever more than one transaction can run at the same time, the database needs a strategy to prevent them from interfering with each other — for example, two transactions both reading a balance, both computing a new value, and both writing it back, silently losing one of the updates. Concurrency control mechanisms are what make correct, isolated execution possible without simply running every transaction one at a time.
Pessimistic (Locking-Based) Concurrency Control
Lock-based systems have transactions acquire shared (read) or exclusive (write) locks on the data they touch before proceeding. If a lock is already held incompatibly, the requesting transaction waits, which can lead to blocking or deadlocks under contention. This approach is common in traditional OLTP databases tuned for many small, conflicting transactions.
Optimistic Concurrency Control and MVCC
Optimistic approaches let transactions proceed without locking and only check for conflicts at commit time. Multi-version concurrency control (MVCC) supports this by keeping multiple versions of each row, so readers can see a consistent snapshot without blocking writers, and writers don't block readers. Conflicts are detected — and one transaction aborted — only when two transactions actually try to modify the same data.
DuckDB's Approach
DuckDB uses MVCC combined with optimistic concurrency control, which is a good match for analytical workloads dominated by large reads and bulk appends rather than many small conflicting updates. Appends never conflict with each other, even on the same table, and readers never block writers. Only when two concurrent transactions modify the same rows does one fail with a conflict error at commit time, rather than waiting on a lock.
Related terms
A database transaction is a sequence of one or more operations that are executed as a single logical unit of work, either fully applied or fully rolled back.
Isolation level →An isolation level defines how much one transaction's in-progress changes are visible to other concurrently running transactions, trading off consistency against concurrency.
ACID →ACID is an acronym describing four properties — Atomicity, Consistency, Isolation, and Durability — that guarantee database transactions are processed reliably.
OLTP →OLTP (Online Transaction Processing) refers to systems designed for fast, high-concurrency, small read/write transactions — like inserting an order or updating an account balance — typically backed by normalized relational schemas.
Delta Lake →Delta Lake is an open table format, originally developed by Databricks, that adds ACID transactions, schema enforcement, and time travel to Parquet data stored in a data lake.
OLAP vs OLTP →OLAP (Online Analytical Processing) and OLTP (Online Transaction Processing) are two workload categories: OLTP handles many small, concurrent transactional writes on normalized schemas, while OLAP handles large read-heavy analytical queries on denormalized, columnar data.
FAQS
Pessimistic control acquires locks up front and makes conflicting transactions wait. Optimistic control lets transactions proceed freely and only checks for conflicts at commit time, aborting one side if a conflict is found.
