Concurrency Control – Locks, Deadlock Prevention, Isolation Levels
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!
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
