Table of contents
1.
Introduction
2.
What is concurrency control in DBMS?
2.1.
Transaction
2.2.
Transaction states/ operations
2.3.
 Transaction Properties
2.4.
Concurrency 
2.5.
Concurrency Problems
2.5.1.
Dirty Read Problem
2.5.2.
Lost Update Problem
2.5.3.
Non-repeatable Read Problem
2.5.4.
Concurrency Control
3.
Relationship Between Processes of Operating System
3.1.
Parent and Child Processes
3.2.
Sibling Processes
3.3.
Orphan Processes
4.
Process Operation in Operating System
4.1.
Process Creation
4.2.
Process Termination
4.3.
Process Synchronization
5.
Advantages of Concurrency
6.
Disadvantages of Concurrency
7.
Issues of Concurrency
8.
Frequently Asked Questions
8.1.
What is a Schedule?
8.2.
Why do we use concurrency methods?
8.3.
What does Commit mean in transaction tables?
8.4.
What is concurrency in OOP?
8.5.
What is concurrency in networking?
9.
Conclusion
Last Updated: May 18, 2024
Easy

Concurrency Control in DBMS

Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

Concurrency is the ability of two or more transactions to use the same data simultaneously. Concurrency control in Database Management System (DBMS) is a procedure of managing simultaneous transactions without conflict.

Concurrency Control in DBMS

Accessing the data simultaneously is relatively easy if all the users only read the data from the database because there is no way they can interfere. 

What is concurrency control in DBMS?

Concurrency control in Database Management Systems (DBMS) is the process of managing simultaneous access to shared data by multiple transactions to ensure data consistency and integrity. It prevents conflicts such as lost updates, uncommitted data, and inconsistent reads that may occur when multiple transactions access and modify the same data concurrently. Concurrency control mechanisms include locking, timestamping, and optimistic concurrency control techniques to maintain data consistency while allowing for concurrent access and updates from multiple users or transactions.

Transaction

A transaction is the execution of a program that accesses or modifies the contents of a database. The primary operations of transactions are 

Read (A): Read operations R(A) or Read(A) reads the value A from the database and stores it in a buffer in the main memory.

Write(A): Write operations W(A) or Write(A) writes the value A back to the database from the buffer that presents in the main memory.

Transaction states/ operations

The system keeps track of the state of the Transaction to prevent the system from entering into an inconsistent state, For example, in case of a failure while execution, because of software or hardware issues, we can continue from the state before failure with the help of operations such as commit and rollback.

Following are the transaction states/operations. 

1. BEGIN: denotes the beginning of execution of the Transaction.

2. READ or WRITE: These specify read or write operations on the database items executed as part of a transaction.

3. END: After read-write operations are done, Transaction enters the end state. It is determined whether changes done during execution can be committed or aborted due to violation of concurrency control or to roll back to the previous stage.

4. Commit: it safely stores the changes done during transaction execution and makes them permanent. It marks the successful completion of the Transaction.

5. Rollback: if at any point in transaction operations are not executed successfully, then all changes done are reverted.

 Transaction Properties

A DBMS(Database Management System) is considered an RDBMS (Relational Database management system) if it follows the given ACID transactional properties.

  • A: Atomicity
  • C: Consistency
  • I: Isolation
  • D: Durability


The SQL Server takes care of the Atomicity, Consistency, and Durability of the system. Meanwhile, the user has to care about the isolation property of the Transaction. 

Atomicity

A transaction should be atomic. A transaction should either be performed as a whole or not performed at all, which means, Transaction does not partly happen. It does not leave the work incomplete.

Consistency

The database state should not be left inconsistent at the end of a transaction. It must be consistent.

Isolation

A transaction should not allow other transactions to make changes until it’s committed. This property, when applied strictly, solves the temporary update problem and makes cascading rollbacks unnecessary.

Durability

Once the database has committed a transaction, the changes are saved. These changes should be permanent and available to all other transactions later. 

Concurrency 

As explained in the introduction, Concurrency occurs when two or more users try to access the same data or information. DBMS concurrency is a problem because when multiple users access the data simultaneously, this can lead to inconsistent results. For example, when two users are updating the same data simultaneously, and some other users try to access that data, this will lead to invalid behavior or inconsistent results.

 

Recommended Topic, B+ Tree in DBMS

Concurrency Problems

Since the two main operations in a database transaction are Read and Write operations. This problem mainly arises when one user is writing and the other is reading or when both the users try to write the same data simultaneously. Following are some common concurrency problems:

  • Dirty Read Problem ( W-R conflict )
  • Lost Update Problem ( W-W conflict )
  • Non-repeatable Read Problem ( W-R conflict )


Dirty Read Problem

This problem occurs when one Transaction updates an item of the database, and somehow that Transaction fails. Before the data gets a rollback, another transaction can access that updated database item. This situation will cause the Write-Read conflict between both transactions.

For example:

Consider the two transactions Tx and Ty in the following diagram performing read or write operations on A. Let say the given balance in account A is 650 rupees.

Time

Tx

Ty

t1

R(A)

t2

A=A+250

t3

W(A)

t4

R(A)

t5

SERVER DOWN ROLLBACK

  • At t1 time, transaction Tx will read the value of account A 650 rupees.
  • At t2 time, transaction Tx adds 250 rupees to account A, which becomes 800.
  • At t3 time, transaction Tx writes the updated value in account A, which is 800.
  • At t4 time, transaction Tx rollbacks due to server problem, and A’s value changes back to 650 rupees(as initially).
  • But the value for account A remains 800 for transaction Ty (because at time t4, Ty Reads the value of A , i.e., 800 ), which is the dirty read. Therefore it is known as the Dirty Read Problem.


Lost Update Problem

This problem occurs when two different transactions perform the read or write operations on the same database items in an interleaved manner (concurrent execution ), making the values inconsistent, resulting in invalid behavior.

For example:

Consider the two transactions Tx and Ty in the following diagram performing read or write operations on A. Let say the given balance in account A is 650 rupees.

Time

Tx

Ty

t1

R(A)

t2

A=A-100

 

t3

R(A)

t4

A=A+100

t5

t6

W(A)

t7

 

W(A)

  • At t1 time, transaction Tx will read the value of account A that is 650 rupees.
  • At t2 time, transaction Tx deducts 100 rupees from account A, which becomes 550 rupees(only deducted and not updated yet).
  • At t3 time, transaction Ty read the value of A in account A, which is 650 only because Tx didn’t update the value yet.
  • At t4 time, transaction Ty adds 100 to account A, which becomes 750 (only added but not updated yet).
  • At t6 time, transaction tx writes the value of account A which becomes 550, as Ty hasn’t updated the value yet.
  • At t7 time, transaction ty writes the value of account A, which will become 750 as it was added at time t4. It means the value written by Tx at time t6 is lost. Hence data becomes incorrect, and database sets to inconsistent. This is known as the Lost Update problem.


Non-repeatable Read Problem

It is also known as Inconsistent Retrievals Problem. This problem occurs when in a transaction, two different values of the same item are read from the database.

For example:

Consider the two transactions Tx and Ty in the following diagram performing read or write operations on A. Let say the given balance in account A is 650 rupees.

Time

Tx

Ty

t1

R(A)

t2

R(A)

t3

A=A+250

t4

W(A)

t5

R(A)

  • At t1 time, transaction Tx will read the value from account A, that is, 650 rupees.
  • A t2 time, transaction Ty will read the value from account A, that is, 650 rupees.
  • At t3 time, transaction Ty adds 250 to account A, which will become 900 rupees( only added, not updated yet).
  • At t4 time, transaction Ty writes the updated value of A that is 900.
  • Later at t5 time, transaction Tx reads the value of account A, which is 900.


Within the same Transaction Tx, it reads two different values of (which are 650 at time t1 and 900 at time t5). It is a non-repeatable read and therefore known as a Non-repeatable read problem.

Thus to maintain consistency in the database and avoid such problems, concurrency control is introduced to manage this issue.

Concurrency Control

It is required for controlling and managing the concurrent execution of operations in the database and thus avoiding the inconsistencies caused by them in the database. Thus for maintaining this, we have concurrency control protocols.

Concurrency Control Protocols

These protocols always ensure the ACID properties(atomicity, consistency, isolation, durability ) and serializability of the concurrent execution of the transactions. These protocols are divided into three parts that are

  • Lock Based Concurrency Control Protocol
  • Time Stamp Concurrency Control Protocol
  • Validation Based Concurrency Control Protocol


We will learn these topics separately in their further blogs.

You can also read about the Log based recovery.

Relationship Between Processes of Operating System

Parent and Child Processes

In an operating system, processes can create new processes, known as child processes. These child processes inherit certain characteristics from their parent processes, such as memory space and file descriptors.

Sibling Processes

Sibling processes are processes that are created by the same parent process. They operate independently of each other but may share certain resources.

Orphan Processes

An orphan process is a child process whose parent process terminates before it does. In such cases, the orphan process is adopted by the operating system and assigned a new parent process.

Process Operation in Operating System

Process Creation

The operating system creates processes through system calls such as fork() or exec(). These calls duplicate the existing process or replace it with a new one, respectively.

Process Termination

Processes terminate when they complete their execution or encounter an error. The operating system deallocates their resources and updates their status accordingly.

Process Synchronization

Processes may need to synchronize their activities to avoid conflicts and ensure data consistency. Techniques such as semaphores and mutexes are used for process synchronization.

Advantages of Concurrency

  • Improved Performance: Concurrency allows multiple tasks to execute simultaneously, leading to better resource utilization and faster task completion.
  • Enhanced Responsiveness: Concurrent execution enables systems to respond to multiple inputs and events concurrently, improving responsiveness and user experience.
  • Resource Sharing: Concurrent processes can share resources such as CPU time, memory, and I/O devices, leading to more efficient resource utilization.

Disadvantages of Concurrency

  • Complexity: Concurrent programs are inherently more complex than sequential programs, making them harder to design, debug, and maintain.
  • Synchronization Overhead: Coordinating the activities of concurrent processes introduces synchronization overhead, potentially impacting performance.
  • Concurrency Bugs: Concurrency introduces new types of bugs such as race conditions, deadlocks, and livelocks, which can be challenging to identify and fix.

Issues of Concurrency

Concurrency introduces several issues, including:

  • Race Conditions: Occur when the outcome of a program depends on the sequence or timing of events.
  • Deadlocks: Happen when two or more processes are unable to proceed because each is waiting for the other to release a resource.
  • Livelocks: Similar to deadlocks, but processes are not blocked; they keep responding to each other without making progress.

Must Recommended Topic, Schema in DBMS

Frequently Asked Questions

What is a Schedule?

A series of operations from one or more Transactions is known as Schedule.

Why do we use concurrency methods?

The main reasons for using the concurrency control method in Database Management System(DBMS) are 
-To resolve the conflict issues like read-write and write-write as explained in this blog.
-These methods to control Concurrency helps to ensure serializability in the database.
-It helps to preserve the consistency in the database.
-To isolate using mutual exclusion between conflicting transactions.

What does Commit mean in transaction tables?

Commit is a transaction state/operation. It safely stores the changes done during transaction execution and makes them permanent. It marks the successful completion of the Transaction.

What is concurrency in OOP?

Concurrency in Object-Oriented Programming (OOP) refers to the ability of a program to execute multiple tasks concurrently, allowing different objects or parts of the program to run simultaneously and independently.

What is concurrency in networking?

Concurrency in networking refers to the ability of a networked system to handle multiple connections and requests simultaneously. It enables efficient utilization of network resources and improved responsiveness by allowing multiple clients to interact with a server concurrently.

Conclusion

In this blog, we start by introducing concurrency control in DBMS. We learn about the transition, its states, and its properties. 

We learn about concurrency and problems like dirty read, lost update, and not repeatable read problems with suitable examples. We learn about the methods to maintain Concurrency,i.e., concurrency control methods.

Visit here to learn more about different topics related to database management systems.

Check out this article - File System Vs DBMS

Also, try Codie360 to practice programming problems for your complete interview preparation. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Live masterclass