What is Concurrency Control?

The simultaneous occurrence of two or more operations is called concurrence and the situation is called concurrency. In concurrency, the function of one operation can affect the function of the other and put data integrity at risk. Hence, management for the control of concurrency is required. The management or controlling system in the Database Management System (DBMS) that coordinates concurrent access is called the concurrency control system.

Concurrency mostly occurs in a multi-user data system, where multiple users simultaneously read or write the same data set. In such a case, there are high chances of data losing its integrity. To understand concurrency control, think of a basic multi-user Google sheet whose data can be edited and extracted by several users. Don’t you think if concurrency control wouldn’t have been there, it would have come up as a great failure of the product, as there were high chances that several users can modify the same data at the same time.

Concurrency control in DBMS manages such situations and ensures data prevention. It is one of the most important functions of cloud-based databases.

What are the parameters involved in Concurrency Control?

Concurrency control ensures data integrity when a single database is accessed and modified by multiple users. The access and modification of data can be termed data transaction. Each data transaction in concurrency must follow the ACID rule to ensure data protection and to avoid data conflict. ACID stands for Atomicity, Consistency, Isolation, and Durability.

Apart from the ACID properties of data transactions, the following important parameters should be maintained under concurrency control.

• Data recovery
• Data replication
• Data serializability
• Data tracking

Concurrent execution in DBMS

To understand concurrency control, let us discuss the concurrent execution of two or more operations.

• In a multi-user system, when a set of data is accessed and modified by multiple users simultaneously, it is called concurrent execution.
• In database transactions, a multi-access system leads to concurrent execution. It also ensures the integrity and protection of data from possible damages.
• In concurrent execution, it is difficult to maintain data consistency for all. Problems may occur during concurrent execution that is to be addressed well in advance.

Problems with Concurrent Execution

In data transactions, mainly read and write operations occur. In concurrent execution, these two operations should be performed in an interleaved manner to maintain data consistency. Possible problems that can arise during concurrent execution are listed below:

Problem 1: Lost Update Problems (W-W Conflict)

Lost update problem or W-W conflict occurs when a database transaction (write) done by a user is lost due to overwriting by another user. In such a condition, the database becomes inconsistent as the update done by the first user is lost. 

Problem 2: Dirty Read Problems (W-R Conflict)

In concurrent transactions, dirty read problems occur when one user is allowed to read the data being edited by another user at the same time.  

Concurrency control protocol

For maintaining concurrency control in data transactions, some protocols are implemented to the database. The benefits of these protocols depend on the specifications of protocols. Some of the protocols are:

• Lock-based protocols
• Timestamp-based protocols
• Validation-based protocols

Lock-based protocols

Lock-based protocols restrict data transactions to a data set until an appropriate lock is implemented on the set. Various types of locks are: 

• Binary lock: Binary lock follows only two conditions—either the data under consideration is locked or unlocked.
• Shared lock: Shared locks are often termed read-only locks as they permit only the read operation to the user. For edit-type data transactions, the user has to request access from the author.
• Exclusive lock: Exclusive lock permits both read and write operations on data transactions. However, it does not allow multiple users to perform simultaneous operations on the same data.

There are four types of lock protocols:

Simplistic lock protocol

In this protocol, a lock is applied on every object of a data set until a write operation occurs and completes. After the data transaction is complete, the objects get unlocked.

Pre-claiming lock protocol

The pre-claiming protocol requires a list of data items to be locked during an operation. At the beginning of execution, all the locks are requested by the transaction. If the access is granted, the transaction is executed by releasing all the locks. Otherwise, the transaction rolls back and waits for the grant.

Two-Phase Locking (2PL)

In a distributed system, the deadlock condition is avoided by a two-phase lock protocol. The two-phase protocol works by acquiring all the required resources in a single transaction. Suppose at some point, one operation is in process, and another operation is in the queue. In that case, all the shared resources pull back and lock for the first operation as soon as another operation starts.

A two-phase lock protocol divides the execution process of a transaction into three parts. The first part is when the transaction starts executing and seeks the locks it requires. In the second part, the transaction acquires all the required locks. The transaction remains the same until the execution is done and the acquired locks are released. 

Acquiring and releasing all the locks as per the two-phase protocol are called growing and shrinking.

To claim an exclusive (write) lock, a transaction must acquire a shared (read) lock and upgrade it.

Strict two-phase locking

Strict two-phase locking holds all the locks until the committed point is achieved. Once the whole process is executed, it releases all the locks together.

Strict-2PL does not have cascading abort as 2PL does.

Timestamp-based protocols

To serialize the execution of concurrent processes, the system time or logical counter is used as a timestamp. Such algorithms, using logical counter or timestamp, are included in the timestamp-based protocol in DBMS. Every conflicting read and write operation is completed in the timestamp order according to this protocol.

In this strategy, the older transaction is always given priority. The transaction's timestamp is calculated using system time. It is the most commonly used concurrency protocol.

Validation-based protocols

A validation-based protocol is also known as the optimistic concurrency control technique. It avoids concurrency in data management as local copies of the transaction data are modified instead of the live data. Eventually, the chances of concurrency become zero, and no condition of data conflict occurs.

The validation-based protocol can be broken down into three stages:

1. Read phase
2. Validation phase
3. Write phase

Characteristics of good concurrency protocol

In DBMS, the characteristics of ideal concurrency control are:

• The technique is resilient to communication failures.
• It maintains parallel execution for maximum concurrency of transactions.
• The storage and computational methods are quick and easy to minimize overheads.
• It sets up a difference between two transactions, along with atomicity.

Conclusion

• Concurrency control is a DBMS process for handling many operations at the same time, without any conflict.

• Due to lack of concurrency management, problems such as lost updates, unclean reads, non-repeatable reads, and incorrect summaries arise.

• Concurrency handling protocols include lock-based, two-phase, timestamp-based, and validation-based protocols.

• There are two types of locks—shared (S) and exclusive (E) (X).

• The two-phase locking protocol, also known as the 2PL protocol, requires a transaction to acquire a lock after releasing one of its locks. It goes through the stages of growth and shrinkage.

• A timestamp is used to serialize the execution of concurrent transactions in the timestamp-based algorithm. As a timestamp, the protocol uses the system time or logical count.

Common Mistakes

When multiple transactions are running concurrently in an uncontrolled or unconstrained way, it may cause various issues. In a database context, these issues are typically referred to as concurrency issues. The following are the five types of database concurrency issues:

• Lost update
• Temporary update
• Incorrect summary
• Temporary update
• Phantom read
• Unrepeatable read

Context and Applications

This topic is significant for the professional exams of both graduate and postgraduate courses,
especially:

Master in Computer Application

Master in Computer Science

Bachelor of Technology in Computer Science and Engineering

Master of Technology in Computer Science and Engineering

Related Concepts

Graph-based concurrency control protocol in DBMS

MOSS concurrency control protocol (distributed locking in the database)

Testing serializability in DBMS

Transaction isolation levels in DBMS

Practice Problems

Q.1 Which of the following types of concurrency controls involves locking?

(A) Timestamp-based concurrency protocol

(B) Validation-based concurrency protocol

(C) Lock-based concurrency protocol

(D) Pointer-based concurrency protocol

Correct Option: (C)

Q.2 Which of the following is not a factor regulated in the concurrency control?

(A) Serialization

(B) Replication

(C) Recovery

(D) Liability

Correct Option: (D)

Q.3 Which technique is used to prevent data conflict in DBMS while multiple users are simultaneously working on the same data?

(A) Serialization

(B) Locking

(C) Concurrency control

(D) Monitoring

Correct Option: (C)

Q.4 To maintain database consistency and transactional integrity, which of the following techniques is used in DBMS?

(A) Arrays

(B) Locks

(C) Triggers

(D) Pointers

Correct Option: (B)

Q.5 Which of the following is not a lock-based protocol?

(A) Shared

(B) Exclusive

(C) Binary

(D) Security

Correct Option: (D)