Introduction to Concurrency Control

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.

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. 

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.

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.

Frequently Asked Questions

  1. What is a Schedule?
    A series of operations from one or more Transactions is known as Schedule.
     
  2. 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.
     
  3. 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.

Key Takeaways

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.

Also, try CodeStudio 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.

Was this article helpful ?
0 upvotes

Comments

No comments yet

Be the first to share what you think