EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

Concurrency Control in DBMS

By Priya PedamkarPriya Pedamkar

Home » Data Science » Data Science Tutorials » Database Management Tutorial » Concurrency Control in DBMS

Concurrency Control in DBMS

Introduction to Concurrency Control in DBMS

Concurrency control in DBMS is an important concept that is related to the transactions and data consistency of the database management systems. Concurrency control refers to the process of managing independent operations of the database that are simultaneous and considered as a transaction in DBMS. Concurrency Control works on the principle of maintaining the transactions state that can be a complete transaction or an incomplete transaction. In the case of the complete transaction, all the associated database operations need to be completed with specified rules and sequences, whereas an incomplete transaction occurs in case all the database operations are not completed due to some technical, power failure, or network connectivity issue.

How does Concurrency Control work in DBMS?

The concurrency control is the process to maintain the data where there are multiple resources or users are accessing the data element and performing the database operations. There are several enterprise systems such as banking, ticket booking, and traffic light systems that use a shared database as part of the data store associated with concurrent transactions. There is a chance of conflict for these transactions and resulting data inconsistency.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

We will discuss the protocols and the problems related to concurrency control in DBMS.

Concurrency Control Protocols

Concurrency control protocols are the techniques used to maintain data consistency, atomicity, and serializability. Following are some of the  concurrency control protocols

1. Lock based

The lock-based protocol is the technique of applying lock condition on the data element, which helps in restricting another resource to perform read and write operation until the lock is active. There are mainly two types of lock such as shared or read-only lock and exclusive lock.

2. Validation based

The validation based protocol is also known as an optimistic concurrency control technique. It involves the read phase, validation phase, and writes phase for concurrency control.

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,952 ratings)
Course Price

View Course

Related Courses
PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

3. Timestamp based

The timestamp-based protocol uses system time or logical count as a timestamp to serialize the execution of concurrent transactions that helps to maintain the order of the transactions.

4. Two-phase protocol

The two-phase protocol (2PL) is a locking mechanism that ensures serializability by using two distinct phases of lock condition. It uses the expanding phase and shrinking phase to acquire and release the lock condition to maintain concurrency control.

Concurrency Control Problems

There are multiple problems that can arise in concurrent transaction scenarios. Some of the common problems are:

1. Dirty Read

Dirty read or temporary update problems happen while there is an incomplete transaction. In this scenario, the data element or item got updated by one transaction and filed before completing it. And another transaction tries to access the data element before it is modified or rolled back to its last value.

Transaction T1 Transaction T2
Read(X)

X=X-n

Write(X)

operation Failed

Read(X)

# X Value X-n

X=X+n1

Write(X)

Explanation: As shown in the table the transaction T1 reads a data item X as Reading (X) operation and performs some arithmetic operation on the Value X  using a numeric value n with Write (X-n) operation. While the write operation in action, it got interrupted and not yet reverted to the databases, The Transaction T2 tries to read the Value X through reading (X) operation that represents the value as X-n. This results in a dirty read problem.

2. Unrepeatable Read

Unrepeatable Read is the scenario where two or more read operations read the same variable as different values and that value is modified by a different transaction by writing operations.

Transaction T1 Transaction T2
Read(X)

X=X-n

Write(X)

Read(X)

Read(X)

Explanation: The table shows two transactions T1 and T2. The T1 reads the Variable X and performs an arithmetic operation as X-n with numeric value n, At the Same time T2 reads the value X and captures the initial value of X. Next T1 performs a Write(X) operation and modified the value of X in the database. Next T2 reads the X values again and this time it finds a different value of X due to the T1. This results in an unrepeatable read problem.

3. Phantom Read

Phantom read problem refers to the scenario where the Transaction reads a variable once and when it tries to read the variable again it gets an error showing the variable does not exist, as the variable is deleted by another transaction.

Transaction T1 Transaction T2
Read(X)

Delete(X)

Read(X)

Read(X)

Explanation: The Table shows T1 reads Variable X, simultaneously T2 reads X. The T1 Deletes X with Delete(X) operation, without T2 acknowledgment. While, T2 tried to read the variable X again, it not able to find the variable. This results in the phantom read problem.

4. Lost updates

Lost updates are the concurrency problem scenario where modification to the variable done by a transaction is lost due to write operation by another transaction.

Transaction T1 Transaction T2
Read(X)

X=X+n

X=X+n1

Write(X)

Explanation: The Table shows the T1 reads the variable X and modifies the values by adding a numerical value n in the operation X=X+n statement. However, T2 performs X=X+n1 statement that overwrites the T1 arithmetic operation. This results in a lost update problem for the T1 transaction.

5. Incorrect Summary

An incorrect Summary problem in concurrency control scenario appears while a transaction applies an aggregate function to some of the variables while another transaction tries to update the variable.

Transaction T1 Transaction T2
Read(X)

Sum=0

Sum=Sum+X

Read(X)

X=X+n

Write(X)

Explanation: The tables show Transaction T1 reads the variable X and uses the Value of X to generate the aggregate value of Sum=Sum+X, whereas T2 reads the value of X, Modifies it by X=X+n statement and writes it to the database using Write(X) operation. It results in an incorrect summary problem in T1.

Conclusion

Concurrency Control in DBMS is a very useful technique to maintain mutually exclusive transactions for database operations. It manages the requests and streamlines the operations where multiple systems or processes try accessing the same database resource. It helps in data integrity across systems and avoids the occurrence of transaction conflicts.

Recommended Articles

This is a guide to Concurrency Control in DBMS. Here we discuss an introduction to Concurrency Control in DBMS along with how does it work, protocols, and problems. You can also go through our other related articles to learn more –

  1. Introduction To DBMS
  2. MongoDB Alternatives
  3. Operational Database
  4. Lock Based Protocols in DBMS

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Database Management Tutorial
  • DBMS
    • Introduction To DBMS
    • DBMS ER Diagram
    • What is DBMS?
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • Netezza Database
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Data Administrator
    • Database Administrator
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • What is DB2?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Checkpoint in DBMS
    • Teradata Create Table
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • PL/SQL
    • What is PL/SQL?
    • Careers in PL/SQL
    • PLSQL procedure
    • PL/SQL Exception
    • PL/SQL LIKE
    • PL/SQL Raise Exception
    • PLSQL rowtype
    • PLSQL? bind variables
    • PL/SQL Record
    • PL/SQL WITH
    • PL/SQL bulk collect
    • PL/SQL Block Structure
    • PL/SQL else if
    • PL/SQL nvl2
    • PL/SQL Package
    • PL/SQL exists
    • PL/SQL instr
    • PL/SQL listagg
    • PL/ SQL Formatter
    • PLSQLlength
    • PL/SQL Commands
    • PL/SQL Data Types
    • CASE statement in PL/SQL
    • PL/SQL IF Statement
    • Loops in PL/SQL
    • PL/SQL Add Column
    • For Loop in PLSQL
    • PL/SQL Cursor Loop
    • PLSQL Array
    • Cursors in PL/SQL
    • PL/SQL FOR Loop Cursor
    • PL/SQL Queries
    • PL/SQL SELECT INTO
    • PL/SQL TO_CHAR
    • PL/SQL UNION
    • PL/SQL NOT EQUAL
    • PL/SQL varray
    • PL/SQL Concatenate
    • PL/SQL UPDATE
    • PL/SQL TRIM
    • PL/SQL GROUP BY
    • PL/SQL GOTO
    • PL/SQL Date Functions
    • PL/ SQL having
    • PL/SQL to_DATE
    • PL/SQL NVL
    • PLSQL format date
    • PLSQL mod
    • PLSQL round
    • PL/SQL Boolean
    • PL/SQL exit
    • PL/SQL DECODE
    • PL/SQL ROWNUM
    • PLSQL?pivot
    • PLSQL string functions
    • PL/SQL Block
    • PL/SQL Function
    • PL/SQL Unwrapper
    • PL/SQL Table
    • PL/SQL ALTER TABLE
    • PLSQL execute immediate
    • Triggers in PL/SQL
    • PL/SQL Collections
    • PL/SQL stored procedure
    • PL/SQL Anonymous Block
    • PLSQL Interview Questions
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • MariaDB
    • MariaDB Versions
    • MariaDB?list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB?boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL
  • SQLite
    • What is SQLite
    • SQLite Commands
    • SQLite Data Types
    • SQLite COUNT
    • SQLite Boolean
    • SQLite autoincrement
    • SQLite select
    • SQLite? Bulk Insert
    • SQLite? add column
    • SQLite? concat
    • SQLite BETWEEN
    • SQLite group by
    • SQLite CASE
    • SQLite group_concat
    • SQLite array
    • SQLite? enum
    • SQLite sum
    • SQLite create table
    • SQLite Alter Table
    • SQLite Create Database
    • SQLite Delete
    • SQLite connection string
    • SQLite Database
    • SQLite Describe Table
    • SQLite Show Tables
    • SQLite exit
    • SQLite create index
    • SQLite foreign key
    • SQLite Stored Procedures
    • SQLite Extension
  • DB2
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
    • View Serializability in DBMS
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • Dataset Normalization
    • MariaDB Max Connections
    • jdbc connection
    • MariaDB GUI

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Special Offer - SQL Certification Course Learn More