Concurrency Control – Locks, Deadlock Prevention, Isolation Levels

1. What is Concurrency Control?

👉 It manages multiple transactions running together so they don’t mess up data.
Goal: Correct results ✅, no conflicts ❌, consistency maintained.

2. Locks (to control access)

  • Shared Lock (S):
    • For read only.
    • Many can read at the same time.
    • Example: 2 users check the bank balance together.
  • Exclusive Lock (X):
    • For write (update/delete).
    • Only 1 transaction allowed → no read/write by others.
    • Example: Updating balance.

Lock Compatibility Table

Lock on DataNew Shared Lock?New Exclusive Lock?
Shared (S)✅ Yes❌ No
Exclusive (X)❌ No❌ No

3. Deadlock

What is it?
👉 Two transactions wait for each other’s locks → stuck forever.

Example:

  • T1 locks A, waits for B.
  • T2 locks B, waits for A.
  • Both are stuck.

Prevention Methods:

  • Timeout: Cancel if waiting too long.
  • Wait-Die: Old waits, young dies (cancelled).
  • Wound-Wait: Old wounds (forces young to cancel).

4. Isolation Levels (Control accuracy vs speed)

LevelDirty ReadNon-Repeatable ReadPhantom Read
Read Uncommitted❌ Possible❌ Possible❌ Possible
Read Committed✅ Prevented❌ Possible❌ Possible
Repeatable Read✅ Prevented✅ Prevented❌ Possible
Serializable✅ Prevented✅ Prevented✅ Prevented

Quick Meaning:

  • Read Uncommitted: Fastest ⚡, least safe (reads dirty data).
  • Read Committed: Prevents dirty reads, but data may change later.
  • Repeatable Read: Same data stays same if read again.
  • Serializable: Safest 🛡️, like transactions run one by one (but slowest).

5. Key Takeaways (for quick revision)

  • Locks: S → read, X → write.
  • Deadlock: Transactions block each other. Solutions = Timeout / Wait-Die / Wound-Wait.
  • Isolation Levels:
    • Read Uncommitted → Least safe.
    • Serializable → Most safe.

MCQ

1. Basic Concepts of Concurrency Control

  1. What is the primary goal of concurrency control in a database?
    • A) Improve transaction speed.
    • B) Prevent unauthorized access.
    • C) Ensure data consistency when multiple transactions execute.
    • D) Reduce database storage.
      Answer: C) Ensure data consistency when multiple transactions execute.
  2. Which of the following problems does concurrency control aim to prevent?
    • A) Data loss during system crashes.
    • B) Conflicts between multiple transactions.
    • C) Database schema corruption.
    • D) Excessive query optimization.
      Answer: B) Conflicts between multiple transactions.

2. Locks

  1. Which type of lock allows multiple transactions to read but not write the same data?
    • A) Shared Lock
    • B) Exclusive Lock
    • C) Write Lock
    • D) Deadlock
      Answer: A) Shared Lock
  2. What happens when an exclusive lock is applied to a data item?
    • A) Multiple transactions can read the item.
    • B) Only one transaction can write to or read the item.
    • C) The lock is automatically converted to a shared lock.
    • D) Other transactions are allowed to access the item freely.
      Answer: B) Only one transaction can write to or read the item.
  3. Which of the following combinations is valid under lock compatibility rules?
    • A) Shared lock and exclusive lock on the same data item.
    • B) Multiple shared locks on the same data item.
    • C) Multiple exclusive locks on the same data item.
    • D) A shared lock followed by an exclusive lock on the same data item.
      Answer: B) Multiple shared locks on the same data item.

3. Deadlock

  1. What is a deadlock?
    • A) A state where the database server stops responding.
    • B) A situation where two or more transactions wait indefinitely for each other’s locks.
    • C) A mechanism to manage multiple locks in a database.
    • D) A technique to improve transaction speed.
      Answer: B) A situation where two or more transactions wait indefinitely for each other’s locks.
  2. Which technique helps prevent deadlocks by setting a time limit for waiting?
    • A) Wound-Wait
    • B) Timeout
    • C) Wait-Die
    • D) Timestamp Ordering
      Answer: B) Timeout
  3. In the Wound-Wait scheme for deadlock prevention:
    • A) Older transactions force younger ones to release locks.
    • B) Younger transactions force older ones to release locks.
    • C) Transactions wait for a fixed period before acquiring a lock.
    • D) Deadlocks are resolved by restarting all transactions.
      Answer: A) Older transactions force younger ones to release locks.
  4. Which of the following is NOT a method for deadlock prevention?
    • A) Wait-Die
    • B) Timeout
    • C) Shared Locks
    • D) Wound-Wait
      Answer: C) Shared Locks

4. Isolation Levels

  1. At which isolation level can dirty reads occur?
    • A) Serializable
    • B) Repeatable Read
    • C) Read Committed
    • D) Read Uncommitted
      Answer: D) Read Uncommitted
  2. What is the highest level of isolation in databases?
    • A) Serializable
    • B) Repeatable Read
    • C) Read Committed
    • D) Read Uncommitted
      Answer: A) Serializable
  3. Which isolation level allows phantom reads?
    • A) Serializable
    • B) Repeatable Read
    • C) Read Committed
    • D) Read Uncommitted
      Answer: B) Repeatable Read
  4. Which problem does the Read Committed isolation level prevent?
    • A) Dirty reads
    • B) Non-repeatable reads
    • C) Phantom reads
    • D) Lost updates
      Answer: A) Dirty reads
  5. Match the isolation level with the problems it prevents:
    • A) Read Uncommitted → Dirty Reads, Non-Repeatable Reads
    • B) Read Committed → Dirty Reads
    • C) Serializable → Dirty Reads, Non-Repeatable Reads, Phantom Reads
    • D) Repeatable Read → Dirty Reads, Phantom Reads
      Answer: C) Serializable → Dirty Reads, Non-Repeatable Reads, Phantom Reads

5. Scenario-Based Questions

  1. A transaction reads uncommitted changes made by another transaction. Which isolation level is being used?
    • A) Read Committed
    • B) Repeatable Read
    • C) Serializable
    • D) Read Uncommitted
      Answer: D) Read Uncommitted
  2. Two transactions are accessing the same row:
    • T1 has a shared lock to read the row.
    • T2 wants an exclusive lock to update the row.
      What will happen?
    • A) T2 proceeds immediately.
    • B) T2 waits until T1 releases the shared lock.
    • C) T1 and T2 share the lock simultaneously.
    • D) Both transactions are aborted.
      Answer: B) T2 waits until T1 releases the shared lock.
  3. A transaction T1 locks data item A, and T2 locks data item B. If T1 waits for B and T2 waits for A, which method can resolve the deadlock?
    • A) Wound-Wait
    • B) Timeout
    • C) Wait-Die
    • D) Any of the above
      Answer: D) Any of the above

6. Advanced Concepts

  1. Which isolation level ensures that transactions execute as if they were running one at a time?
    • A) Serializable
    • B) Repeatable Read
    • C) Read Committed
    • D) Read Uncommitted
      Answer: A) Serializable
  2. What is a “phantom read”?
    • A) Reading uncommitted changes from another transaction.
    • B) Reading the same data twice but getting different results.
    • C) New rows appear or existing rows disappear between reads.
    • D) A transaction failing due to system crashes.
      Answer: C) New rows appear or existing rows disappear between reads.
  3. Which isolation level balances speed and data integrity by preventing dirty reads but allowing phantom reads?
    • A) Read Uncommitted
    • B) Read Committed
    • C) Repeatable Read
    • D) Serializable
      Answer: B) Read Committed