Below is a complete, detailed explanation of Concurrency Control Techniques—covering locking, timestamping, MVCC, optimistic methods, deadlocks, and all exam-important points.

---

⭐ CONCURRENCY CONTROL TECHNIQUES

Concurrency Control ensures that multiple transactions can execute simultaneously without violating data consistency, isolation, or ACID properties.

Without concurrency control, problems occur:

• Lost updates
• Dirty reads
• Inconsistent reads
• Unrepeatable reads
• Phantom reads

DBMS solves these using concurrency control techniques.

---

⭐ Major Categories of Concurrency Control Techniques

1. Lock-based Protocols
2. Timestamp-based Protocols
3. Optimistic Concurrency Control (Validation-based)
4. Multiversion Concurrency Control (MVCC)
5. Deadlock Handling Techniques
6. Granularity-based Methods (Hierarchical locking)

Let’s discuss each in depth.

---

⭐ 1. LOCK-BASED CONCURRENCY CONTROL

Locks are used to control access to data items.

---

A. Types of Locks

✔ 1. Binary Lock

• Locked (1)
• Unlocked (0)

Primitive model; not commonly used.

---

✔ 2. Shared & Exclusive Locks (S/X Locks)

Lock Type	Purpose	Conflicts With
Shared (S)	read-only	Exclusive
Exclusive (X)	read & write	Shared, Exclusive

Rules:

• Many S-locks allowed simultaneously
• Only one X-lock allowed
• X-lock requires exclusive access

These locks ensure conflict-serializability.

---

B. Two-Phase Locking (2PL)

Most widely used locking technique.

Two phases:

1. Growing Phase
   • Acquire locks
   • No release
2. Shrinking Phase
   • Release locks
   • No new acquisition

Guarantees serializability but may cause deadlocks.

---

✔ 1. Strict 2PL (Most important)

• All locks held until commit/abort
• Prevents cascading rollbacks
• Ensures recoverable and strict schedules

Used in most DBMS (Oracle, MySQL InnoDB).

---

✔ 2. Rigorous 2PL

• Holds all locks till commit
• Stronger than strict 2PL

---

✔ Deadlock Problem in Locking Protocols

Deadlock occurs when:

T1 waits for lock held by T2
T2 waits for lock held by T1

Deadlock Handling:

1. Deadlock Prevention

• Wait-die protocol
• Wound-wait protocol

2. Deadlock Detection

• Wait-for graph
• If cycle exists → victim aborted

3. Deadlock Avoidance

• Banker’s algorithm (rare)

---

⭐ 2. TIMESTAMP-BASED CONCURRENCY CONTROL

Each transaction receives a unique timestamp when it begins.

Two timestamps maintained for each data item:

• Read Timestamp (RTS)
• Write Timestamp (WTS)

✔ Rules:

When T tries to READ(X):

• If TS(T) < WTS(X) → old version too late → abort T
• Else → allow read, update RTS(X)

When T tries to WRITE(X):

• If TS(T) < RTS(X) → write conflicts with a newer read → abort T
• If TS(T) < WTS(X) → writing old value → abort T
• Else → allow write, update WTS(X)

Advantages:

✔ No deadlocks
✔ Ensures serializability

Disadvantages:

✘ Many rollbacks
✘ Timestamp assignment overhead

---

⭐ 3. OPTIMISTIC CONCURRENCY CONTROL (VALIDATION-BASED)

Used when conflicts are rare (low write frequency).

Phases:

1. Read Phase
   • Transaction reads data into private workspace
   • Does not lock
2. Validation Phase
   • Check if conflicts with other committing transactions
   • If conflict → abort
3. Write Phase
   • After validation, writes changes to the database

Advantages:

✔ No locks
✔ No deadlocks
✔ High concurrency

Disadvantages:

✘ High abort rate in update-heavy workloads
✘ Validation cost high

---

⭐ 4. MULTIVERSION CONCURRENCY CONTROL (MVCC)

Modern DBMS (PostgreSQL, MySQL InnoDB, Oracle) use MVCC.

Maintains multiple versions of a data item.

✔ Readers never block writers

✔ Writers never block readers

When a transaction writes:

• A new version is created
• Old version kept for older transactions

Benefits:

✔ Read queries do not lock data
✔ High parallelism
✔ Eliminates many read-write conflicts
✔ Better performance for OLTP

Issues:

✘ Version storage overhead
✘ Version cleanup (vacuuming) needed

---

⭐ 5. LOCK COMPATIBILITY MATRIX

	S	X
S	✔	✘
X	✘	✘

If lock incompatible → transaction must wait.

---

⭐ 6. MULTIGRANULARITY LOCKING (Hierarchical Locking)

Used for large databases.

Locks applied at multiple levels:

• Database
• Table
• Page
• Tuple
• Field

Modes used:

• IS (Intention Shared)
• IX (Intention Exclusive)
• S (Shared)
• X (Exclusive)

Advantages:

✔ Reduces number of locks
✔ Supports lock escalation

---

⭐ 7. CONCURRENCY PROBLEMS (must write in exam)

Without concurrency control, the following may occur:

1. Lost Updates
2. Dirty Reads
3. Unrepeatable Reads
4. Phantom Reads
5. Incorrect Summary problem

Locking, timestamping, and MVCC solve these issues.

---

⭐ Comparison of Techniques (Exam Table)

Technique	Advantages	Disadvantages
Lock-based	Simple, widely used	Deadlocks occur
Strict 2PL	No cascading rollback	Possible deadlocks
Timestamp	No deadlocks	Many aborts
MVCC	High concurrency	Storage overhead
Optimistic	Best for read-heavy systems	Rollbacks frequent in write-heavy

---

⭐ Perfect 5–6 Mark Short Summary

Concurrency control techniques ensure safe and correct execution of multiple transactions simultaneously.
Major methods include Lock-based methods (S/X locks, 2PL), Timestamp ordering, Optimistic validation, and MVCC. Locking ensures conflict serializability, while timestamp ordering avoids deadlocks. Optimistic control validates transactions at commit time, and MVCC uses multiple versions to allow high concurrency. These techniques prevent issues like lost updates, dirty reads, and maintain database consistency.

---