Understanding Concurrency in Database Systems

Imagine a global e-commerce platform during a flash sale. In a single second, thousands of customers are simultaneously browsing products, adding items to carts, placing orders, and making payments. Behind the scenes, each of these operations translates into database transactions—operations that read, write, and modify data. If these transactions were forced to execute one at a time, in strict sequential order, the system would crawl to a halt, and customers would abandon their carts in frustration.

This is where concurrent execution becomes not just beneficial, but absolutely essential.

Concurrent execution allows a database management system (DBMS) to process multiple transactions at the same time, dramatically improving throughput and responsiveness. But this power comes with significant complexity: when transactions execute concurrently and access shared data, the potential for data corruption, inconsistency, and lost updates becomes very real.

This page explores the fundamental concept of concurrent execution—what it means, how it works at a technical level, and why understanding it is crucial for anyone working with database systems.

At its core, concurrent execution refers to the ability of a database management system to handle multiple transactions simultaneously, rather than processing them one after another in strict sequence. This concept is fundamental to how modern databases achieve the performance and responsiveness that applications demand.

The formal definition:

Concurrent execution occurs when two or more transactions have overlapping execution periods—meaning that while one transaction is still in progress (has executed BEGIN but not yet COMMIT or ROLLBACK), another transaction begins execution. The transactions' operations may be interleaved in various ways, depending on the scheduling decisions made by the DBMS.

Understanding the contrast with serial execution:

To appreciate concurrent execution, we must first understand its opposite: serial execution. In serial execution, transactions run one at a time, one after another, with no overlap. A transaction T₂ cannot begin until transaction T₁ has completely finished (committed or rolled back).

Consider two simple transactions:

• T₁: Transfer $100 from Account A to Account B
• T₂: Calculate the total balance across all accounts

In serial execution, we would have:

• Schedule S₁: T₁ completes entirely, then T₂ executes entirely, OR
• Schedule S₂: T₂ completes entirely, then T₁ executes entirely

In concurrent execution, operations from T₁ and T₂ can be interleaved:

• While T₁ is deducting from Account A, T₂ might be reading Account C
• T₂ might read Account A before T₁'s deduction, then read Account B after T₁'s credit
• The specific interleaving depends on the scheduler's decisions

The need for concurrent execution in database systems stems from fundamental realities of computer architecture, application requirements, and user expectations. Understanding these drivers helps explain why every modern DBMS is designed with concurrency as a core capability.

The CPU-I/O speed disparity:

The primary technical motivation for concurrency is the enormous speed gap between processors and storage devices. A modern CPU can execute billions of operations per second, while even the fastest NVMe SSDs have latencies measured in microseconds for individual operations. Mechanical hard drives are orders of magnitude slower still.

When a transaction performs a disk read, the CPU would be idle for thousands of CPU cycles if forced to wait. Concurrent execution allows the DBMS to put another transaction's operations on the CPU during this wait time, dramatically improving overall system utilization.

Quantifying the throughput improvement:

Consider a workload where each transaction spends 80% of its time waiting for I/O and 20% actually using the CPU (this ratio is common for database transactions). In serial execution, the CPU is utilized only 20% of the time.

With concurrent execution allowing 5 transactions to overlap:

• While Transaction 1 waits for I/O, Transactions 2-5 can use CPU
• CPU utilization can approach 100%
• Throughput can improve by up to 5x in this scenario

This is the fundamental performance argument for concurrency: exploiting I/O latency to keep the CPU productive.

Understanding the mechanics of concurrent execution requires examining how a DBMS manages multiple transactions, the components involved, and the lifecycle of concurrent operations. This knowledge forms the foundation for understanding concurrency control mechanisms covered in later modules.

The Transaction Scheduler:

At the heart of concurrent execution is the transaction scheduler (also called the scheduler or concurrency control manager). This component determines the order in which operations from different transactions are executed. The scheduler's decisions create what is called a schedule—a specific ordering of operations from multiple transactions.

The scheduler must balance two often-conflicting goals:

1. Maximize concurrency to achieve high throughput and resource utilization
2. Maintain correctness by ensuring the result is equivalent to some serial execution

The lifecycle of a concurrent transaction:

When multiple transactions execute concurrently, each follows this lifecycle while potentially overlapping with others:

1. Transaction Start: Client initiates a transaction (explicit BEGIN or implicit first statement)
2. Operation Submission: Application submits SQL operations (SELECT, INSERT, UPDATE, DELETE)
3. Scheduling: Scheduler determines when each operation can execute
4. Resource Acquisition: Lock manager grants necessary locks on data items
5. Operation Execution: Buffer manager retrieves data, modifications made in memory
6. Logging: Log manager records operations for recovery purposes
7. Transaction Completion: COMMIT writes changes permanently; ROLLBACK discards changes
8. Resource Release: Locks and other resources are released for other transactions

The concept of a schedule is central to understanding and reasoning about concurrent execution. A schedule is a formal representation of how operations from multiple transactions are ordered in time.

Formal Definition:

A schedule S for a set of transactions T₁, T₂, ..., Tₙ is a total ordering of all the operations in these transactions that preserves the ordering of operations within each individual transaction.

In other words:

• All operations from all transactions appear in the schedule
• If operation A comes before operation B in transaction Tᵢ, then A comes before B in the schedule
• Operations from different transactions can be interleaved in any order

Types of Schedules:

Serial Schedules: All operations of one transaction complete before any operation of another transaction begins. For n transactions, there are n! possible serial schedules.

Non-serial (Concurrent) Schedules: Operations from different transactions are interleaved. The number of possible non-serial schedules is astronomically larger than serial schedules.

The goal of concurrency control is to allow only those non-serial schedules that produce results equivalent to some serial schedule—these are called serializable schedules.

When transactions execute concurrently, they access shared data items. Understanding the patterns of these accesses—particularly which combinations can cause problems—is essential for designing and using concurrency control mechanisms effectively.

The fundamental operations:

From a concurrency perspective, all database operations reduce to two fundamental types:

1. Read (R): Retrieve the current value of a data item without modifying it
2. Write (W): Modify the value of a data item

Every SQL statement ultimately translates to some combination of reads and writes on specific data items (rows, pages, tables, etc.).

Understanding conflicts:

Two operations conflict if:

1. They belong to different transactions
2. They access the same data item
3. At least one of them is a write operation

Conflicting operations are significant because their relative order affects the final result. If we swap the order of two conflicting operations, we might get a different database state or different values read by transactions.

Modern database systems implement concurrency at multiple levels, from coarse-grained parallelism across queries to fine-grained parallelism within single operations. Understanding these levels helps in appreciating where concurrent execution occurs and how it impacts system behavior.

Inter-query Concurrency:

This is the classic form of database concurrency—different queries or transactions from different users executing simultaneously. When User A runs a SELECT and User B runs an UPDATE at the same time, this is inter-query concurrency. This is the primary focus of concurrency control mechanisms like locking and MVCC.

Intra-query Concurrency:

A single complex query can be broken into parallel operations. For example, a query scanning a large table might use multiple threads, each scanning a different portion of the table. The results are then merged. This is sometimes called query parallelism or intra-query parallelism.

Concurrent execution would be impossible without proper coordination at the buffer pool level. The buffer manager is the DBMS component that mediates all access to disk data, and its interaction with concurrency control is crucial to understand.

What is the Buffer Pool?

The buffer pool (or buffer cache) is a region of main memory where the DBMS caches pages of data read from disk. Instead of reading directly from disk for every operation, transactions read and write data in the buffer pool. Changes are later written back to disk.

Shared buffer access:

When multiple transactions execute concurrently, they may need to access the same data pages in the buffer pool. The buffer manager must coordinate this access to prevent corruption—but this is separate from the logical concurrency control that ensures serializability.

Buffer latches vs. locks:

The buffer manager uses latches (lightweight locks) to protect physical access to buffer pages. These are different from the locks used by the concurrency control system:

Understanding this distinction is important: even with proper logical concurrency control, the buffer manager must use latches to prevent race conditions during page access.

We have established what concurrent execution means and why it is essential for modern database systems. Let's consolidate the key concepts:

What's next:

Now that we understand what concurrent execution is and how it works, the next page explores why we want concurrency—the concrete performance benefits that make the complexity of concurrent execution worthwhile. We'll quantify throughput improvements, examine response time benefits, and understand why concurrent execution is non-negotiable for real-world systems.