Concurrency Control in DBMS

Concurrency control in DBMS helps to maintain the integrity and consistency of the data when there are multiple transactions happening at the same time. Without proper queuing and execution control, data loss, inconsistencies, and even system crashes may occur. Managing a database with a large amount of data and users that access the database simultaneously requires knowledge about concurrency to ensure a proper flow of communication. In this blog, you will learn about concurrency control in DBMS, various concurrency control techniques, along with their best practices in detail.

Table of Contents:

• What is Concurrency Control in DBMS?
• Advantages and Disadvantages of Concurrency Control in DBMS
• Concurrency Control Techniques in DBMS
  1. Lock-Based Concurrency Control Mechanisms in DBMS
  2. Timestamp-Based Concurrency Control Protocol in a DBMS
  3. Validation-Based Concurrency Control Protocol in DBMS
• Concurrency Control Problems in DBMS
• Serializability in Concurrency Control in DBMS
• Deadlock Handling in Concurrency Control in DBMS
• Best Practices for Managing Concurrency Control in a DBMS
• Conclusion

What is Concurrency Control in DBMS?

Concurrency control in DBMS is a technique that manages and maintains multiple transactions without interfering with each other. It makes sure that there will be no inconsistencies and compromises in the data integrity when handling multiple users at the same time. The concurrency control in DBMS coordinates the order and execution of operations from multiple transactions to avoid any mistakes like data loss or incorrect updates.

The concurrency concept is crucial when working in a multi-user environment where overlapping one data on another is highly possible if not controlled and regulated properly. For example, if two users want to update their data at the same time, concurrency control in DBMS will make sure that those updates will happen in logical order and regulate only one update at a time. Key benefits of concurrency control in DBMS are that it helps to maintain data consistency, which is very helpful for the accurate execution of database operations.

Master SQL: Empower Your Data Skills Today!

Unlock the power of databases with hands-on SQL training and become a data-driven professional

Explore Program

Advantages and Disadvantages of Concurrency Control in DBMS

Below are some advantages and disadvantages of concurrency control in DBMS.

Advantages of Concurrency Control in DBMS:

1. Preserves Data Consistency: Concurrency control ensures transactions maintain consistent and accurate data without interference.
2. Avoids Data Anomalies: Handles issues like dirty reads, lost updates, unrepeatable reads, etc.; thus, a user will always get the intended output from the database and will have confidence that they have received the correct result.
3. Provides Transaction Isolation: Concurrency control will provide a degree of transaction isolation in the DBMS, where each transaction is processed as an independent unit. This isolation ensures that one transaction does not interfere with another and that ACID properties are preserved.
4. Has the ability to function in multi-user systems: Concurrency control allows hundreds or even thousands of operations to run simultaneously without affecting performance or causing data conflicts. This helps ensure the system operates smoothly and supports regular business activities without interruptions.
5. Provides reliability and consistency to the system: When transactions are carefully managed to maintain ACID properties and avoid conflicts, the system remains consistent, reliable, and free from crashes or data corruption.

Disadvantages of Concurrency Control in DBMS

1. Complicated System: The additional context surrounding concurrency control means that extra logic and algorithms will now be required. This increases the complexity of the DBMS and the difficulties associated with the implementation, management, or debugging of the DBMS architecture.
2. Performance Cost: Managing concurrent transactions introduces checks that may cause delays, especially in high-throughput systems, thereby slowing performance.
3. Deadlocks: DBMSs do, however, exhibit the problems associated with poor concurrency controls. The most damaging of these is deadlocks, whereby two or more transactions wait on resources held by one another.
4. Resource Utilisation: The additional controls required for concurrency often demand more memory, CPU usage, and control structures.
5. Maintenance: As mentioned, the more complicated the control logic related to concurrency becomes over time, the harder it will be to debug problems related to transaction failures, waiting on locks, or rollback mechanisms.

Concurrency Control Techniques in DBMS

In a database management system that supports multiple users, a fundamental requirement is to regulate and coordinate concurrent transactions. Concurrency control in a Database Management System (DBMS) helps guarantee that concurrent transactions are executed correctly and do not affect the integrity of the data. In a DBMS, there are three commonly used concurrency control mechanisms: these are lock-based concurrency control mechanisms, timestamp-based concurrency control mechanisms, and validation-based concurrency control mechanisms. Each type of mechanism has its own approach for ensuring conflicts are avoided and there is consistency between databases.

1. Lock-Based Concurrency Control Mechanisms in DBMS

Lock-based concurrency control mechanisms use locking for controlling access to database items. When a transaction requests permission to either read or write a data item, it must first acquire a lock on that item. Locking provides a mechanism to prevent a conflicting operation on the same data item from being executed simultaneously with respect to the transaction that previously locked the item.

Lock systems typically implement two types of locks:

• Shared Lock (S-Lock): If only being read, multiple transactions can S-Lock the data item.
• Exclusive Lock (X-Lock): If a transaction intends to read and write a data item, it must acquire an exclusive lock (X-Lock), which blocks others during that time.

Locks are typically governed by a lock manager as part of the database management system, which manages access and prevents unauthorised operations. The protocol defines rules like two-phase locking (2PL), where all locks are acquired before any are released, ensuring transaction safety and consistency.

2. Timestamp-Based Concurrency Control Protocol in a DBMS

This method assigns a unique timestamp to each transaction at the start. These timestamps help determine the logical execution order, even if the actual timing differs.

Every data item in the database maintains:

• The timestamp of the last transaction, which is read (Read Timestamp – RTS).
• The timestamp of the last transaction that wrote (Write Timestamp – WTS) to it.
• When a transaction tries to read or write to a data item, its timestamp gets compared against the last RTS and the WTS.
• If the transaction is found to achieve a read or write that does not match up with the expected ordering (e.g., the transaction is asking to write to some data that has already been read by a later transaction), the transaction will be aborted and subsequently restarted.

Example:
Transaction T1 (timestamp 101) reads data X. Then, Transaction T2 (timestamp 105) tries to write to X. T2 is active and checks if its timestamp is greater than RTS. If so, it can go ahead. Otherwise, if T1 had a greater timestamp than T2, T2 might be rolled back, preventing a write-read conflict.

This protocol ensures all read and write operations follow timestamp order, maintaining logical consistency without using locks. Importantly, it also manages to provide this without locks, so each read-write logic executes without blocking.

3. Validation-Based Concurrency Control Protocol in DBMS

Also known as Optimistic Concurrency Control, this technique is similar to earlier protocols in that it assumes transaction-level conflict occurs rarely. Nonetheless, optimistic approaches do not prevent access by different transactions during the execution of the transaction itself. Instead, conflicts are checked at the end, just prior to completing the commit process. When considering all transactions that will execute, these transactions execute in three phases with respect to time:

• Read Phase: The transaction will read data and save it in a local buffer, where it will be manipulated without changing anything in the actual database. The transaction will run these manipulations without affecting the database (for failing validation) until the transaction completes its commit phase.
• Validation Phase: During validation and before committing, the transaction checks the database state and results of other concurrent transactions to detect any conflicting modifications.
• Write Phase: If the validation is successful, the transaction is permitted to write its modifications to the database record as described in the validation phase. If this validation is not successful, the transaction will be rolled back.

For example:
Transaction T1 reads product data for reporting. At the same time, T2 updates the stock for the same product. If T1 gets to the validation stage and T1 sees that T2 has modified the data that T1 has read, T1 is rolled back to ensure the data does not get into a bad state.

This model works well in situations where transactions will not interfere with each other, like reporting systems or with databases that are read mostly. As it allows transactions to proceed without locks, it could provide better performance if the intention was not to interfere with one another.

Concurrency Control Problems in DBMS

Concurrency control ensures the correctness of transactions in the DBMS. However, without proper control and coordination, we can end up with several issues in multi-user environments when multiple transactions are executed simultaneously. When issues from concurrency arise, we may end up compromising the accuracy, consistency, and reliability of our data.

1. Dirty Read: This happens when a transaction reads a data item that is written by another transaction that hasn’t yet committed. If the second transaction rolls back, then the first transaction is using invalid data.

2. Unrepeatable Read: This is when a transaction re-reads the same data but gets different results. The second read was affected by another transaction modifying the same data between the reads.

3. Phantom Read: This occurs when a transaction reads a set of rows based on a particular condition, but when the transaction is re-executed, new rows are read because another transaction has inserted rows matching the condition.

4. Lost Update: This is a condition in which two transactions are reading the same data item and both subsequently update it. One of the updates will be lost, and effectively, the prior update is lost.

5. Incorrect Summary: This occurs when a transaction computes aggregates while others update the same data (e.g., total sales) while one or more other transactions are updating the same data.

Serializability in Concurrency Control in DBMS

Serializability in concurrency control in DBMS ensures that the result of executing several transactions in parallel is the same as executing them sequentially (one after another in a fixed order). To make things a bit clearer, serializability ensures consistency in the data and disallows anomalies.

There are two types of serializability: conflict serializability and view serializability.

1. Conflict Serializability

Conflict serializability is the most popular and widely used serializability. A schedule is conflict serialisable if it is possible to convert it to a serial schedule (where the transactions execute 1 at a time) by swapping out non-conflicting operations. Two operations conflict if:

• They are from different transactions.
• They access the same data item.
• At least one is a write operation.

Conflict serializability is defined via a precedence graph (or serializability graph) with:

• Nodes representing transactions.
• An edge from T1 to T2 means T1 has an operation that conflicts with and precedes an operation in T2.
• If the graph has no cycles, then the schedule is conflict serialisable.

If Transaction T1 writes to X and Transaction T2 reads X after, then T1 → T2 in the precedence graph. If there are no cycles created, then the schedule is conflict serialisable.

2. View Serializability

View serializability is a more lenient and more generalised type of serializability. A schedule is view serialisable if it produces a result equivalent to a serial schedule by the “view” of reads and writes.

Three conditions must be true for view serializability:

• Initial Reads Equivalent: Both schedules must read equivalent initial values.
• Reads Taken From Same Transactions: If a transaction reads a value written by another transaction for one schedule, the other Transactions must read the same value from the transaction.
• Final Writes Equivalent: The final writes on each data item must be equivalent in both schedules. View serializability accommodates more types of schedules than conflict serializability, making it more general but more difficult to analyse in practice.

For example, if transaction T1 writes A and transaction T2 reads A, then in any view-equivalent schedule, T2 must also read A from transaction T1. As long as the data states at the start and finish are the same and the reads are from the original source, then the schedules are view serialisable.

Both conflict serializability and view serializability are essential for understanding concurrency control in DBMS and ensuring safe transaction execution. Conflict serializability is easier to implement and verify in most systems. However, view serializability supports a broader range of valid transaction schedules by allowing transactions to read data from others (like T1), enabling greater flexibility and complexity in the database.

Deadlock Handling in Concurrency Control in DBMS

In the context of concurrency control in a DBMS, a deadlock occurs when two or more transactions are waiting for each other, such that none of the waiting transactions are able to access a lock. A good analogy for this is the traffic jam example, when each car waits for another to move, but no car is willing to go first. This can effectively stop the database system from functioning properly.

In order to resolve deadlocks, DBMS systems will implement some techniques for deadlock management. The most common deadlock management techniques are:

1. Deadlock Prevention

In this case, the deadlock is completely avoided by ensuring that at least one of the required conditions for a deadlock to occur does not hold. Some common methods include:

• Wait-Die Scheme: In the wait-die scheme, older transactions may wait for younger transactions to finish. However, if a younger transaction tries to access a resource that is held by an older transaction, the younger transaction is aborted.
• Wound-Wait Scheme: In the wound-wait scheme, older transactions are allowed to preempt (wound) younger transactions by forcing the younger transactions to roll back if they hold the requested resource.

2. Deadlock Avoidance

In deadlock avoidance, the transactions are considered at run-time to avoid the structure of the deadlock. The well-known example of this method is the Banker’s Algorithm. Whenever the DBMS wishes to give a lock, it will check to see if doing so is safe. If not, the request must wait.

3. Deadlock Detection and Recovery

In this method, the DBMS permits deadlock to occur and then detects and resolves it as it occurs. It uses a wait-for graph, which is constructed such that:

• Nodes in the graph represent transactions.
• Edges depict which transaction is waiting for another.

In the case that the wait-for graph has a cycle, it indicates a deadlock. When a deadlock is detected, the system picks one of the transaction as the victim transaction to roll back, thus releasing the resources it has acquired so that other transactions can continue.

4. Timeout-based methods

In some systems, any transaction that has waited too long for a resource is rolled back. This method is simplistic in design but may roll back transactions needlessly if delays are simply the result of ordinary load.

Handling deadlocks is an important aspect of concurrency control in a DBMS that requires prompt resolution to keep the system running, especially in systems where a large volume of transactions is processed, as with websites (e.g., shopping/money transfer/sharing applications). The above techniques can be used based on the user’s requirement, depending on load, complexity, and objectives for performance.

Best Practices for Managing Concurrency Control in a DBMS

1. Use the Right Isolation Level: Choose the correct isolation level based on your application’s need for accuracy and speed. This helps balance the correctness of the data and performance.

2. Use the Right Locking Method: Use only the locks that are really needed. Too many or large locks can slow things down or make the system hang.

3. Keep Checking Performance and Activity: Watch how your application runs and handles the transactions. Change the settings if there are any errors.

4. Keep Transactions Short: Short transactions are faster and safer. Transactions that are long can block others and create system issues.

5. Build Transactions the Right Way: Write clean and clear transactions. Handle errors effectively to avoid unnecessary rollbacks and maintain data integrity.

Kickstart Your SQL Journey – For Free!

Learn how to write powerful queries and manage databases with our beginner-friendly free SQL course.

Explore Program

Conclusion

Concurrency control in DBMS is important to ensure data integrity when many transactions happen in parallel. Techniques such as locking, timestamps, and validation ensure data consistency and minimize concurrency issues. You can prevent common problems like dirty reads and lost updates. Understanding serializability and appropriate deadlock detection will ensure that the transactions are executed correctly. Following the best practices will further improve performance and consistency in any multi-user database system. In this article, you have learnt about concurrency control in DBMS, its function, and its techniques.

Take your skills to the next level by enrolling in the SQL Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions prepared by industry experts.

Concurrency Control in DBMS – FAQs