Concurrency Management in DBMS

1. Introduction to Concurrency in DBMS

Concurrency in Database Management Systems (DBMS) refers to the ability of multiple users or transactions to access and modify the database simultaneously without leading to inconsistency or conflicts.

💡 Example:

• Multiple users booking airline tickets at the same time.
• A banking system handling simultaneous deposits and withdrawals.

Why is Concurrency Management Important?

✅ Prevents data inconsistency.
✅ Avoids race conditions where multiple transactions compete for the same resource.
✅ Ensures correct execution of transactions in a multi-user environment.

---

2. Problems in Concurrency Control

When multiple transactions execute simultaneously, they can interfere with each other, leading to concurrency issues.

1. Lost Update Problem

• Occurs when two transactions update the same data without proper synchronization, and one update is lost.
  ✔ Example:

1. Transaction T1 reads Balance = 5000.
2. Transaction T2 also reads Balance = 5000.
3. T1 adds 1000 → New Balance = 6000.
4. T2 subtracts 500 → New Balance = 4500.
5. Final balance should be 5500, but T1’s update is lost!

---

2. Dirty Read Problem

• Occurs when a transaction reads uncommitted data of another transaction.
  ✔ Example:

1. Transaction T1 updates Balance = 7000 but hasn’t committed.
2. Transaction T2 reads this value.
3. T1 rolls back, restoring the balance to 5000.
4. T2 now holds an invalid value (7000 instead of 5000).

---

3. Unrepeatable Read Problem

• Occurs when a transaction reads the same data multiple times and gets different values.
  ✔ Example:

1. T1 reads Balance = 5000.
2. T2 updates Balance = 6000 and commits.
3. T1 reads again → Now Balance = 6000.
4. Inconsistent results for the same query!

---

4. Phantom Read Problem

• Occurs when a transaction retrieves a different set of records during repeated reads.
  ✔ Example:

1. T1 selects all employees with salary > 50000.
2. T2 inserts a new employee with salary = 60000.
3. T1 re-executes the query and sees an extra row!

---

3. Concurrency Control Techniques

To manage concurrency issues, DBMS implements various concurrency control techniques.

1. Lock-Based Concurrency Control

A lock is a mechanism that prevents multiple transactions from accessing the same resource simultaneously.

Types of Locks

🔒 Shared Lock (S-Lock) → Allows multiple transactions to read but not write.
🔒 Exclusive Lock (X-Lock) → Allows only one transaction to read and write.

✔ Example of Locking in SQL:

BEGIN TRANSACTION;
SELECT * FROM Accounts WITH (HOLDLOCK); -- Holds the lock until the transaction ends
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 1;
COMMIT;


Two-Phase Locking Protocol (2PL)

• Phase 1 (Growing Phase): Transactions acquire locks but don’t release them.
• Phase 2 (Shrinking Phase): Transactions release locks but don’t acquire new ones.

✅ Guarantees serializability, but may cause deadlocks.

✔ Example:

LOCK TABLE Accounts IN EXCLUSIVE MODE;


---

2. Timestamp-Based Concurrency Control

• Each transaction gets a unique timestamp based on start time.
• Transactions execute in timestamp order.

💡 Rules:
✔ If T1 (earlier) wants to write to an item read by T2 (later), T1 is allowed.
❌ If T2 (later) wants to write to an item read by T1 (earlier), T2 is rolled back.

✅ No deadlocks occur, but may lead to frequent rollbacks.

✔ Example:

ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION ON;


---

3. Optimistic Concurrency Control (OCC)

• Instead of locking, transactions execute without restrictions and validate changes before committing.
• Three phases:
  1. Read Phase – Transaction reads data without locks.
  2. Validation Phase – Before commit, check if any conflicts exist.
  3. Write Phase – If no conflicts, changes are applied.

✅ Works well when conflicts are rare (e.g., in read-heavy applications).

✔ Example:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;


---

4. Multi-Version Concurrency Control (MVCC)

• Instead of locking, multiple versions of a record are maintained.
• Transactions read previous versions instead of waiting for locks.

💡 Used in: PostgreSQL, MySQL (InnoDB), Oracle.

✔ Example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;


✅ Prevents dirty reads while allowing high concurrency.

---

4. Transaction Isolation Levels

SQL provides isolation levels to balance concurrency and consistency.

Isolation Level	Prevents Dirty Reads?	Prevents Unrepeatable Reads?	Prevents Phantom Reads?
Read Uncommitted	❌ No	❌ No	❌ No
Read Committed	✅ Yes	❌ No	❌ No
Repeatable Read	✅ Yes	✅ Yes	❌ No
Serializable	✅ Yes	✅ Yes	✅ Yes

✔ Setting Isolation Level in SQL:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;


---

5. Deadlocks in Concurrency Control

A deadlock occurs when transactions wait indefinitely for each other’s resources.

Example of Deadlock:

1. T1 locks Account A, T2 locks Account B.
2. T1 wants Account B, T2 wants Account A.
3. Both wait indefinitely!

---

Deadlock Prevention Techniques

1. Timeouts – If a transaction waits too long, it is aborted.sqlCopyEditSET LOCK_TIMEOUT 5000; -- Aborts transaction if locked for more than 5 seconds
2. Wait-Die Scheme – Older transactions wait, younger ones restart.
3. Wound-Wait Scheme – Older transactions force younger ones to rollback.
4. Deadlock Detection – DBMS periodically checks for deadlocks and aborts one transaction.

---

6. Summary

• Concurrency ensures multiple transactions execute correctly in a multi-user system.
• Concurrency problems include Lost Update, Dirty Read, Unrepeatable Read, and Phantom Read.
• Concurrency control techniques:
  • Locking mechanisms (2PL)
  • Timestamp Ordering
  • Optimistic Concurrency Control (OCC)
  • Multi-Version Concurrency Control (MVCC)
• Transaction isolation levels prevent concurrency problems.
• Deadlocks can be handled using prevention, detection, and resolution techniques.