🔄 Concurrency Control Techniques

---

📌 What is Concurrency Control?

Concurrency control refers to the mechanisms used by a DBMS to manage simultaneous execution of transactions in a multi-user environment to ensure:

• Data consistency
• Isolation of transactions
• ACID compliance

🔍 Why is it needed?

When multiple users or applications access the database at the same time, several problems can occur:

---

⚠️ Common Problems in Concurrent Transactions

Problem	Description
Lost Update	Two transactions update the same data and the first update is overwritten.
Dirty Read	A transaction reads uncommitted data from another transaction.
Non-repeatable Read	A transaction reads the same row twice and gets different data.
Phantom Read	New rows are added or deleted in a range between two reads.

---

✅ Concurrency Control Techniques

Concurrency control techniques can be broadly classified into:

1. Lock-Based Protocols

2. Timestamp-Based Protocols

3. Optimistic Concurrency Control (Validation-Based)

4. Multiversion Concurrency Control (MVCC)

---

🔐 1. Lock-Based Protocols

🔑 Concept:

Locks are used to control access to data items. A transaction must acquire a lock before accessing a data item.

🔁 Types of Locks:

Lock Type	Description
Shared Lock (S-lock)	Used for read-only operations. Multiple transactions can hold shared locks.
Exclusive Lock (X-lock)	Used for read and write. Only one transaction can hold an exclusive lock at a time.

🔁 Two-Phase Locking (2PL):

A widely used protocol.

Phases:

1. Growing Phase: A transaction may obtain locks but not release any.
2. Shrinking Phase: A transaction may release locks but cannot obtain new ones.

✅ Ensures:

• Serializability (correct concurrent execution)
• But may cause deadlocks

🔄 Variants:

• Strict 2PL: Holds all locks until commit.
• Rigorous 2PL: Same as strict, but stricter.

---

⏳ 2. Timestamp-Based Protocols

🕒 Concept:

Every transaction is assigned a timestamp. Data items have read and write timestamps.

Rules:

• Read rule: A transaction can read a data item only if it is not modified by a newer transaction.
• Write rule: A transaction can write to a data item only if it hasn’t been read or written by a newer transaction.

✅ Advantages:

• Ensures serializability
• Avoids deadlocks

❌ Disadvantages:

• Can cause many transactions to abort and restart

---

🧪 3. Optimistic Concurrency Control (Validation-Based)

🔍 Concept:

• Assumes conflicts are rare
• Transactions execute without restrictions, but undergo validation before commit.

🔁 Phases:

1. Read Phase: Transaction reads and performs changes in a local copy.
2. Validation Phase: Before commit, system checks for conflicts.
3. Write Phase: If validation is successful, changes are written to the database.

✅ Suitable for:

• Low-conflict environments
• Systems with mostly read operations

❌ Drawback:

• High overhead if many transactions fail validation

---

📚 4. Multiversion Concurrency Control (MVCC)

🧬 Concept:

Instead of locking, MVCC maintains multiple versions of data to allow concurrent reads and writes.

• Each transaction sees a snapshot of the database.
• Writers create a new version of a data item.
• Readers access older, committed versions.

✅ Advantages:

• Non-blocking reads
• Reduces contention
• No dirty reads

❌ Disadvantages:

• Storage overhead (many versions stored)
• Complexity in version management

---

⚖️ Comparison of Techniques

Technique	Uses Locks?	Deadlock Possible?	Ideal For	Drawbacks
Lock-Based (2PL)	Yes	Yes	High-conflict environments	Deadlocks
Timestamp-Based	No	No	Time-critical systems	High abort rate
Optimistic	No	No	Read-heavy systems	Validation overhead
MVCC	No	No	Concurrent reads/writes	Storage usage

---

🛠️ Deadlock Handling in Lock-Based Protocols

Deadlocks occur when two or more transactions wait for each other’s locks.

🧯 Deadlock Solutions:

• Deadlock Prevention: Enforce ordering on lock acquisition.
• Deadlock Detection: Use wait-for graphs.
• Deadlock Recovery: Abort one or more transactions.

---

📝 Summary

Key Point	Details
Goal	Ensure consistent and correct transaction execution
Problems Solved	Lost update, dirty read, non-repeatable read
Techniques	Lock-based, timestamp-based, optimistic, MVCC
Best Practice	Choose based on workload (read-heavy, write-heavy, real-time)

---

✅ Final Tip for Exams:

Be ready to explain each technique, compare them, and identify which one is best suited for a given scenario.

---