Concurrency Control in DBMS: Everything You Need to Know

What This Blog Covers

1. Concurrency allows multiple database transactions to overlap in execution, improving throughput and resource utilisation when managed effectively. 

2. Concurrency control helps maintain data consistency and transaction isolation when multiple users or processes access the same database simultaneously. 

3. Common concurrency control techniques include two-phase locking, timestamp ordering, Multiversion Concurrency Control (MVCC), and validation-based concurrency control. 

4. Poorly controlled concurrent transactions can cause anomalies such as dirty reads, non-repeatable reads, phantom reads, lost updates, and inconsistent summaries. 

5. The right concurrency control method depends on factors such as workload, transaction conflicts, isolation requirements, and performance needs, rather than one technique being suitable for every database.

Imagine a bustling database where multiple transactions juggle data like acrobats on a tightrope. But fear not. Concurrency control in DBMS steps in as the ringmaster, helping them perform without interfering with one another or creating data inconsistencies.

This blog dives into the key aspects of concurrency control in DBMS, detailing its importance, key principles, methods, and more! Read on and learn how this process keeps your database management system (DBMS) operations running like a well-oiled machine.

Understanding Concurrency

Concurrency in DBMS refers to the overlapping execution of multiple transactions. It improves throughput and resource utilisation, but uncontrolled concurrent access can create data anomalies.

Concurrent execution can improve database performance, but without appropriate control, interacting transactions may create conflicts or inconsistent results

What is Concurrency Control in DBMS?

Concurrency control is the process of coordinating simultaneous transactions so that multiple users or processes can access and modify data while preventing data loss, preserving data integrity, and avoiding inconsistent results.

It enables multiple transactions to execute concurrently while controlling interactions that could produce inconsistent results. Two fundamental operations involved in database transactions are read (R) and write (W). The main goal of concurrency control is to preserve transaction isolation and database consistency when multiple transactions execute concurrently.

One important correctness criterion is serialisability, which ensures that the outcome of concurrent transactions is equivalent to an appropriate serial execution of those transactions. However, if not managed effectively, concurrent execution of transactions can lead to a host of issues, including:

1) Inconsistent data

2) Lost updates

3) Uncommitted data

4) Inconsistent retrievals

The Need for Concurrency Control

Concurrency control is essential in DBMS for many reasons, including the following:

1) Ensuring Database Consistency: Proper concurrency control helps the database remain consistent while numerous transactions execute concurrently.

2) Avoiding Conflicting Updates: When two transactions attempt to update the same data at the same time, one update might overwrite the other without proper control. Concurrency control helps prevent or manage this issue.

3) Preventing Dirty Reads: Without appropriate concurrency control, one transaction may read data written by another transaction before that transaction commits.

4) Enhancing System Efficiency: Concurrency control allows multiple transactions to make progress during overlapping periods, improving system throughput and helping utilise resources effectively.

5) Supporting Transaction Isolation: Concurrency control helps prevent concurrent transactions from interfering with one another in ways that produce incorrect results. Atomicity itself is primarily handled by transaction and recovery mechanisms.

Master Teradata architecture, data manipulation, and performance optimisation with our Teradata Training – Join now!

ACID Properties and Concurrency Control

Concurrency control works alongside the ACID properties of database transactions, with isolation being particularly important during concurrent execution.

1) Atomicity (A): A transaction is completed in full or, if it fails, its changes are rolled back.

2) Consistency (C): The database must transition from one consistent state to another, preserving data integrity.

3) Isolation (I): Concurrent transactions should be controlled so that their interactions do not produce incorrect results. The exact visibility of changes depends on the isolation level used by the DBMS.

4) Durability (D): Once a transaction is committed, its changes should persist even if the system subsequently fails.

Concurrency Control Methods in DBMS

Common concurrency control methods include the Two-phase Locking (2PL) protocol, Timestamp Ordering, Multiversion Concurrency Control (MVCC), and validation-based or optimistic concurrency control. We explore them in detail below:

1) Two-phase Locking Protocol

Two-phase locking (2PL) is a concurrency control protocol that restricts when a transaction can acquire and release locks to help produce conflict-serialisable schedules. It’s called “two-phase” because each transaction has two distinct phases: the Growing phase and the Shrinking phase.

Here's a breakdown of the two-phase locking protocol:

a) Growing Phase: During this phase, a transaction can obtain (acquire) any number of locks as required but cannot release any. The growing phase continues until the transaction acquires its final required lock.

b) Shrinking Phase: This phase begins when the transaction releases its first lock. During this phase, the transaction may release locks but cannot acquire any new locks.

Lock Point: The lock point is the point at which the transaction obtains its final lock, marking the end of its growing phase.

Two-phase locking guarantees conflict serialisability when transactions follow the protocol, although some forms of 2PL can still permit deadlocks.

2) Timestamp Ordering Protocol

The Timestamp Ordering Protocol is a concurrency control method used in database management systems to maintain transaction serialisability. This method uses a timestamp for each transaction to determine its order relative to other transactions. Instead of relying on locks, basic timestamp ordering determines whether read and write operations are allowed according to transaction timestamps.

Here's a breakdown of the time stamp ordering protocol:

a) Read Timestamp (RTS): For a data item X, RTS(X) records the largest timestamp of any transaction that has successfully read X. Every time a data item X is read by a transaction T with timestamp TS, the RTS of X is updated to TS if TS is more recent than the current RTS of X.

b) Write Timestamp (WTS): For a data item X, WTS(X) records the largest timestamp of any transaction that has successfully written X. Whenever a data item X is written by a transaction T with timestamp TS, the WTS of X is updated to TS if TS is more recent than the current WTS of X.

This protocol uses these to determine whether a transaction’s request to read or write a data item should be granted. Because basic timestamp ordering does not make transactions wait for locks, it avoids lock-based deadlocks; however, transactions may need to be aborted and restarted when timestamp-order rules are violated.

3) Multiversion Concurrency Control

Multiversion Concurrency Control (MVCC) manages concurrent access by maintaining multiple versions of data. This allows eligible readers to access an appropriate version while another transaction may be updating a newer version.

The exact version visible to a transaction depends on the DBMS and its isolation rules, helping reduce unnecessary reader-writer blocking while maintaining an appropriate view of the data.

Here are some essential points to remember about this protocol:

a) Multiple Versions: When a transaction modifies a data item, instead of changing the item in place, it creates a new version of that item. This means that multiple versions of a database object can exist simultaneously.

b) Reads aren’t Blocked: In many MVCC implementations, readers can access an appropriate committed version of data while another transaction is modifying a newer version. The version visible to a transaction depends on the DBMS and isolation level; for example, some systems use transaction-level snapshots while others use statement-level snapshots.

c) Timestamps or Transaction IDs: Each version of a data item is tagged with a unique identifier, typically a timestamp or a transaction ID. This identifier determines which version of the data item a transaction sees when it accesses that item. A transaction can generally read changes it has made itself, although exact visibility rules depend on the DBMS implementation.

d) Garbage Collection: As transactions create newer versions of data items, older versions can become obsolete. A background process typically cleans up these old versions. This procedure is often referred to as “garbage collection.”

e) Conflict Resolution: If two transactions try to modify the same data item concurrently, the system will need to resolve the conflict. Different systems have different conflict resolution methods. When concurrent transactions attempt conflicting writes, the DBMS resolves the conflict according to its concurrency-control rules; one transaction may wait, fail, or be rolled back

Acquire deeper knowledge of logical data structure and database constraints with our Relational Databases & Data Modelling Course - Sing up now!

4) Validation Concurrency Control

Validation-based concurrency control, also called optimistic concurrency control, allows transactions to proceed without holding long-lived locks and checks for conflicts before commit.

Unlike traditional pessimistic concurrency control, validation-based concurrency control allows transactions to perform most of their work without holding long-lived locks on shared data. Changes can be kept temporarily and validated for conflicts before being applied to the database.

Validation-based concurrency control works best when transaction conflicts are expected to be relatively infrequent, allowing transactions to execute optimistically before conflicts are checked.

The essential features of VCC to remember include:

1) Phases: Each transaction in VCC goes through three distinct phases:

a) Read Phase: The transaction reads values from the database and changes its private copy, a local, temporary version of the data that only the transaction can see and modify without affecting the actual database.

b) Validation Phase: Before committing, the system checks whether the transaction conflicts with relevant concurrent transactions and whether its updates can be safely applied.

c) Write Phase: If validation succeeds, the transaction updates the actual database with the changes made to its private copy.

2) Validation Criteria: The system checks for potential conflicts with other transactions during the validation phase. For instance, if two transactions attempt to update the same data item, a conflict is detected. If validation fails, the transaction is typically aborted and may later be retried according to the implementation.

Lay a strong foundation in the field of Database Management through our Introduction To Database Course – Register now!

Challenges in Concurrency Control

Several challenges can arise when numerous transactions execute concurrently without appropriate coordination. These are commonly referred to as concurrency problems. We explore some of them below:

1) Dirty Read Issue

The dirty read issue in DBMS occurs when one transaction reads data written by another transaction before that transaction has committed. If the writing transaction later rolls back, the reading transaction has already used a value that was never permanently committed. Here’s an example of a dirty read issue:

This highlights the inconsistency caused by reading uncommitted data.

2) Non-repeatable Read Issue

A non-repeatable read issue occurs when a transaction reads the same data item multiple times and finds different values because another transaction has modified and committed the data between the reads. Here’s an example:

As you can see, T1 reads the value of X twice and finds different values due to T2's commitment between the two reads.

3) Phantom Read Issue

A phantom read occurs when a transaction repeats a query and obtains a different set of rows because another transaction committed an insert, delete or relevant update. Here's an example:

As this example shows, T1 executes a query, and T2 inserts a new row that matches the query condition, causing T1 to see different results when the query is re-executed.

4) Lost Update Issue

A lost update issue occurs when two transactions read the same data and then update it based on the value read, while one of the updates gets overwritten by the other. Here’s an example to illustrate this issue:

5) Incorrect Summary Issue

The incorrect summary problem occurs when a transaction calculates an aggregate result while another transaction is updating some of the values being included. As a result, the first transaction may combine older and newer values and produce an inconsistent summary. The following example illustrates this issue:

Here, T1 tries to calculate the total balance of the accounts. Meanwhile, T2 transfers an amount between two accounts. This update causes T1 to produce an incorrect total because it reads some values before the transfer and another value after it.

Advantages of Concurrency

Concurrency allows multiple transactions to make progress during overlapping periods instead of requiring each transaction to finish before another begins. When managed effectively, this can provide several advantages in a multi-user DBMS:

1) Improved Throughput: Multiple transactions can make progress within the same period, allowing the database to process more work than a strictly serial approach in suitable workloads.

2) Better Response for Multiple Users: Concurrency allows users to perform database operations without unnecessarily waiting for unrelated transactions to finish, helping applications remain responsive.

3) Improved Resource Utilisation: While one transaction is waiting for I/O or another resource, the DBMS may allow other transactions to make progress, helping use available system resources more effectively.

4) Support for Multi-user Workloads: Concurrency enables applications to serve many users and processes at the same time while concurrency control manages interactions that could otherwise produce inconsistent results.

Elevate your understanding of Database Management with our comprehensive InfluxDB Training – Sign up now!