1. What is Concurrency Control?
- Manages how multiple transactions run at the same time without messing up data.
Goal: Ensure correctness, avoid conflicts, and maintain database consistency.
2. Locks
- Locks are rules to stop two transactions from using the same data at the same time.
Types of Locks:
- Shared Lock (S):
- For reading data only (many can read at once).
- Example: Two users can view a bank balance together.
- Exclusive Lock (X):
- For writing data (only one transaction can write).
- Example: If one user updates a balance, no one else can read or write until done.
Lock Compatibility:
| Lock on Data | Can Grant New Shared Lock? | Can Grant New Exclusive Lock? |
|---|---|---|
| Shared | Yes | No |
| Exclusive | No | No |
3. Deadlock
- What is Deadlock?
- Two transactions are waiting for each other’s locks and can’t move forward.
Example:- T1 locks A, waits for B.
- T2 locks B, waits for A.
- Both stuck!
- Two transactions are waiting for each other’s locks and can’t move forward.
How to Prevent Deadlocks:
- Timeout: If a transaction waits too long, cancel it.
- Simple but may cancel useful transactions.
- Wait-Die: Older transaction waits, younger is canceled (dies).
- Ensures fairness.
- Wound-Wait: Older transaction forces younger to cancel (wound).
- Prioritizes older transactions.
4. Isolation Levels
- Isolation levels decide how much one transaction affects another.
Goal: Balance between accuracy and speed.
Types of Isolation Levels:
- Read Uncommitted:
- Fastest but least safe (can read uncommitted/dirty data).
- Example Problem: Transaction reads values that later get rolled back.
- Read Committed:
- Can only read committed data.
- Prevents dirty reads but allows non-repeatable reads.
- Repeatable Read:
- Ensures the same data is read again without changes during the transaction.
- Prevents dirty and non-repeatable reads but allows phantom reads.
- Serializable:
- Safest but slowest. Acts like transactions run one at a time.
- Prevents dirty, non-repeatable, and phantom reads.
Quick Problems Isolation Levels Solve:
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible |
| Serializable | Prevented | Prevented | Prevented |
5. Key Points to Remember
- Locks:
- Shared → For reading.
- Exclusive → For writing.
- Deadlock:
- Happens when transactions block each other.
- Use timeout, wait-die, or wound-wait to prevent it.
- Isolation Levels:
- Read Uncommitted → Fastest, least safe.
- Serializable → Slowest, most safe.
MCQ
1. Basic Concepts of Concurrency Control
- 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.
- 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
- 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
- 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.
- 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
- 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.
- 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
- 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.
- 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
- At which isolation level can dirty reads occur?
- A) Serializable
- B) Repeatable Read
- C) Read Committed
- D) Read Uncommitted
Answer: D) Read Uncommitted
- What is the highest level of isolation in databases?
- A) Serializable
- B) Repeatable Read
- C) Read Committed
- D) Read Uncommitted
Answer: A) Serializable
- Which isolation level allows phantom reads?
- A) Serializable
- B) Repeatable Read
- C) Read Committed
- D) Read Uncommitted
Answer: B) Repeatable Read
- 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
- 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
- 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
- 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.
- 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
- 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
- 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.
- 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