12.b) Discuss about concurrency control and its types with relevant examples.
1) Concurrency Control :
Concurrency Control in DBMS is the technique used to manage simultaneous
execution of multiple transactions without conflicts.
It ensures database consistency, isolation, and serializability by preventing problems
such as lost updates, dirty reads, and uncommitted data access.
Objectives of Concurrency Control:
1. Maintain database consistency
2. Ensure serializability
3. Prevent interference between transactions
4. Avoid problems such as lost update, dirty read, unrepeatable read
5. Provide isolation as per ACID properties
TYPES OF CONCURRENCY CONTROL TECHNIQUES
DBMS mainly uses the following techniques:
1. Lock-Based Protocols
2. Two Phase Locking Protocol (2PL)
3. Timestamp-Based Protocols
4. Validation-Based Protocols
Lock-Based Protocols:
Lock-Based Protocols are concurrency control methods that use locks (shared and
exclusive) to regulate access to data items.
A transaction must acquire a lock before reading or writing, and other transactions
must wait if the lock is already held.
Real-Time Example: ATM balance update
Two people try to withdraw money from the same bank account at the same time.
Person A inserts ATM card → ATM system locks the account
A’s transaction checks balance and deducts money
After completion → lock is released
Now Person B can access the account and withdraw
Because ATM locks the account during Person A’s transaction, Person B cannot withdraw
at the exact same time.
This prevents double withdrawal and lost update.
Types:
Binary Locks: Simple lock with locked/unlocked states.
Shared & Exclusive Locks: Shared for reading, exclusive for writing.
Multiple Granularity Locking: Locks at different data levels (database, table, row).
Deadlock Handling: Wait-Die and Wound-Wait schemes to resolve deadlocks.
Two Phase Locking Protocol (2PL)
Two-Phase Locking is a locking protocol in which every transaction must obtain all required
locks during a growing phase and release them only during a shrinking phase.
Once a transaction starts releasing locks, it cannot acquire new locks, ensuring conflict
serializability.
Real-Time Example: Amazon order placement
When placing an order, the system must access two items:
Product stock quantity
User’s payment wallet
Growing Phase:
System locks product stock
System locks user wallet
Perform Operations:
Decrease product stock
Deduct money from wallet
Shrinking Phase:
Unlock user wallet
Unlock product stock
Once unlocking starts, system cannot lock new resources, avoiding conflicts during order
processing.
This ensures that stock and payment are updated safely together.
Types:
Basic 2PL: Growing and shrinking phases for locks.
Strict 2PL: Hold exclusive locks until commit for recoverability.
Rigorous 2PL: Hold all locks until commit for strict serializability.
Conservative 2PL: Acquire all locks before execution to avoid deadlocks.
Timestamp-Based Protocols
Timestamp-Based Protocols assign every transaction a unique timestamp when it
begins.
The system ensures that all operations occur in timestamp order, and if a transaction
violates this order, it is rolled back.
This ensures serializability without using locks.
Real-Time Example: Two users try to book the last seat.
Two users open the same seat at different times:
User 1 opens seat at 10:00 AM → older timestamp
User 2 opens at 10:02 AM → younger timestamp
User 1 tries to book the seat first → seat becomes unavailable.
When user 2 clicks Book, the system checks:
“This seat was already booked by an older timestamp user.”
So User 2’s request is rejected, he is asked to select another seat.
Older request always gets priority → avoids seat conflicts.
Types:
Basic Timestamp Ordering: Transactions ordered by timestamp.
Thomas’ Write Rule: Allows ignoring some outdated writes to reduce aborts.
Validation-Based Protocols
Validation-Based Protocols allow transactions to execute without locks and validate
them before commit.
A transaction goes through read, validation, and write phases.
If validation detects a conflict with other committed transactions, the transaction is
rolled back.
Real-Time Example: Two students filling the same college application form
Student A opens the form
Student B also opens the same form
(No locks → both freely edit)
Both complete and click Submit.
Validation step:
Server checks whether any update happened before the current submission.
If A submits first → data stored
B submits later → server checks and finds that the form was already
submitted/updated
So B’s submission is rejected or must refill.
👉 No locks used; conflict checked only when submitting.
This is Validation-based concurrency control.
Types:
Backward Validation: Checks conflicts with earlier committed transactions.
Forward Validation: Checks conflicts with later transactions.
Backward-Forward Validation: Combination of both methods.
Advantages of Concurrency Control
1. Reduced Waiting Time: Multiple transactions can proceed simultaneously, reducing
idle time.
2. Improved Response Time: Faster access and interaction with the database.
3. Better Resource Utilization: Hardware and database resources are efficiently shared.
4. Increased System Efficiency: Higher throughput and better overall performance.
Disadvantages of Concurrency Control
1. Overhead: Managing locks and timestamps adds system overhead.
2. Deadlocks: Circular waits between transactions can halt progress.
3. Complexity: Implementation in distributed or large systems can be difficult.
4. Inconsistencies: Rollbacks or long waits may cause temporary data inaccuracy or
staleness.