Scribd
Upload
9 views

Concurrency Control

Concurrency control in a Database Management System (DBMS) ensures multiple transactions can execute simultaneously without leading to data inconsistencies. It addresses issues like lost updates, dirty reads, unrepeatable reads, and phantom reads through mechanisms such as locking and timestamp ordering. Various lock types and protocols, including shared and exclusive locks, are employed to maintain data integrity and consistency during concurrent access.

Uploaded by

Shots Spots
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
9 views

Concurrency Control

Concurrency control in a Database Management System (DBMS) ensures multiple transactions can execute simultaneously without leading to data inconsistencies. It addresses issues like lost updates, dirty reads, unrepeatable reads, and phantom reads through mechanisms such as locking and timestamp ordering. Various lock types and protocols, including shared and exclusive locks, are employed to maintain data integrity and consistency during concurrent access.

Uploaded by

Shots Spots
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 12

Department of Computer Science and Engineering

Course Name- Relational Database Management System (RDBMS)


Course Code-COM-402
Lecture No- 3

Model Institute of Engineering & Technology


Concurrency control

◻ Concurrency control in a Database Management


System (DBMS) is a mechanism used to ensure that
multiple transactions can execute simultaneously
without leading to data inconsistency or conflicts. It
ensures the integrity, consistency, and isolation of
transactions while maintaining high system
performance.
Example: Online Railway Ticket
Booking System
1.User A and User B both check seat availability for Train
123 at the same time.
1. The system shows that Seat #45 is available.
2.Both users attempt to book Seat #45 simultaneously.
1. If there is no concurrency control, both transactions might read
the same seat status as "available" and proceed with the
booking.
3.Issue Without Concurrency Control:
1. Both transactions might commit successfully, resulting in two
passengers being assigned the same seat—this is called the Lost
Update Problem.
4.How Concurrency Control Solves This Issue:
1. DBMS uses techniques like locking, timestamp ordering
concurrency control to ensure that only one transaction can
successfully book the seat.
Problems Due to Concurrency

◻ Problem 1: Lost Update Problems (write-write conflict) -When two transactions


update the same data simultaneously, the update made by one transaction may
be lost.
◻ Consider the below diagram where two transactions TX and TY, are
performed on the same account A where the balance of account A is 300.
Dirty Read

◻ Occurs when a transaction reads uncommitted changes


made by another transaction.
Unrepeatable Read

•Occurs when a transaction reads the same row multiple times but
gets different values due to another transaction's updates.
Phantom Read Problem-

◻ This problem occurs when a transaction reads some variable from


the buffer and when it reads the same variable later, it finds that
the variable does not exist.
Handling of Concurrency Problems

◻ To prevent these issues, Concurrency Control


Mechanisms are used, such as:
◻ Lock-Based Protocols
◻ Timestamp-Based Protocols
◻ Multiversion Concurrency Control (MVCC)
◻ Deadlock Detection and Prevention
Lock-Based Protocols

◻ In a Database Management System (DBMS), lock-based


concurrency control (BCC) is a method used to manage
how multiple transactions access the same data.
◻ This protocol ensures data consistency and integrity
when multiple users interact with the database
simultaneously.
What is a Lock?

◻ A lock is a mechanism that restricts access to a


database item (like a row or table) to ensure that only
one transaction can modify it at a time.
◻ How Does a Lock Work?
1.Before a transaction can read or write a data item, it
must first request a lock on that item.
2.If the lock is granted, the transaction proceeds.
3.If the lock is denied (because another transaction
holds it), the requesting transaction must wait.
4.Once the transaction is completed, it releases the
lock, allowing other transactions to access the data.
Types of Lock

◻ Shared Lock (S): Shared Lock is also known as Read-


only lock.
1.If a transaction has a Read lock on the data item, other
transaction can obtain Read Lock on the data item but
no Write Locks.
◻ Exclusive Lock (X): Data item can be both read as
well as written.
1. If a transaction has a write Lock on the data item,
then other transactions cannot obtain either a Read
lock or write lock on the data item.
Types of lock protocols

◻ Simplistic lock protocol


◻ Pre-claiming Lock Protocol
◻ Two-phase locking (2PL)
◻ Strict Two-phase locking (Strict-2PL)

You might also like