Section A — 1 Mark Questions
1. Define Concurrency Control. [1 Mark]
Answer: Concurrency control is the process of managing simultaneous operations on a database to ensure that the database remains in a consistent state.
2. What is a lock in a database? [1 Mark]
Answer: A lock is a mechanism that controls access to a data item by a transaction to prevent unauthorized or conflicting simultaneous access.
3. What does an Exclusive Lock (X-lock) imply? [1 Mark]
Answer: An exclusive lock allows a transaction to both read and write a data item, but prohibits any other transaction from acquiring any lock on that item.
4. Define a Shared Lock (S-lock). [1 Mark]
Answer: A shared lock allows a transaction to read a data item but prevents other transactions from modifying it; other transactions may also acquire shared locks.
5. What is the primary purpose of Timestamp ordering? [1 Mark]
Answer: It ensures that transactions execute in an order consistent with their arrival times, preventing conflicts without requiring locks.
6. Define a deadlock. [1 Mark]
Answer: A deadlock is a situation where two or more transactions are waiting indefinitely for each other to release locks.
7. What is the objective of the validation phase in validation-based protocols? [1 Mark]
Answer: To verify that a transaction's execution does not violate consistency before committing its changes to the database.
8. What is a read-timestamp? [1 Mark]
Answer: It is the largest timestamp of any transaction that has successfully read a data item.
9. Define 'Write-timestamp' in timestamp protocols. [1 Mark]
Answer: It is the largest timestamp of any transaction that has successfully written a data item.
10. What is the 'Wait-For Graph' used for? [1 Mark]
Answer: It is used to detect deadlocks in a system by identifying cycles in the waiting dependencies between transactions.
11. Name a weak consistency level. [1 Mark]
Answer: Read Uncommitted.
12. What happens to a transaction that attempts to read an item with a later write-timestamp? [1 Mark]
Answer: The transaction is rolled back and restarted with a new timestamp.
13. Can multiple transactions hold a shared lock on the same item? [1 Mark]
Answer: Yes.
14. What is the main drawback of aggressive locking? [1 Mark]
Answer: Increased risk of deadlocks.
15. What is starvation in concurrency control? [1 Mark]
Answer: A state where a transaction is repeatedly denied access to resources and never completes.
16. What is the 'Write-only' phase in validation? [1 Mark]
Answer: The final phase where a validated transaction makes its updates permanent.
17. How is an Insert operation typically handled in locking protocols? [1 Mark]
Answer: Through index locking or predicate locking to prevent phantom rows.
18. What is the 'Read' phase of a transaction? [1 Mark]
Answer: The phase where a transaction performs all its operations on local copies of data.
19. Does Read Committed consistency prevent all types of dirty reads? [1 Mark]
Answer: Yes, it ensures that only committed data is read.
20. What is a phantom record? [1 Mark]
Answer: A record that appears in a query result set unexpectedly due to an insert operation by another concurrent transaction.
Section B — 2/3 Mark Questions
1. Explain the difference between Exclusive and Shared locks. [2 Marks]
Answer: A Shared lock is used for reading and allows multiple transactions to access the data simultaneously. An Exclusive lock is used for writing and prohibits any other transaction from accessing the data at all, whether for reading or writing.
2. Briefly explain the 'Wait-For Graph' method for deadlock detection. [3 Marks]
Answer: The system maintains a directed graph where nodes represent active transactions. An edge from T1 to T2 exists if T1 is waiting for a lock held by T2. If the graph contains a cycle, a deadlock exists, and the system must abort one or more transactions to break it.
3. What are the three phases of validation-based protocols? [3 Marks]
Answer: The three phases are: 1. Read Phase, where the transaction performs updates on local variables; 2. Validation Phase, where the system checks for conflicts; 3. Write Phase, where the updates are permanently applied to the database.
4. Describe the concept of 'Dirty Read' in weak consistency levels. [2 Marks]
Answer: A dirty read occurs when a transaction reads data that has been modified by another concurrent transaction but has not yet been committed. If the first transaction aborts, the second transaction is left with invalid, 'dirty' data.
5. Explain how a timestamp protocol prevents conflict. [2 Marks]
Answer: Each transaction is assigned a unique timestamp at start. The protocol forces an ordering of operations based on these timestamps, ensuring that transactions appear to execute in their serialized timestamp order.
6. List two techniques to handle deadlocks. [2 Marks]
Answer: 1. Deadlock Prevention: Ensuring the system never enters a deadlock state by imposing ordering constraints. 2. Deadlock Detection: Periodically checking for cycles in the wait-for graph and aborting transactions to resolve them.
7. Why are Insert and Delete operations complex in locking? [3 Marks]
Answer: They are complex because they don't just change the value of existing records; they change the structure of the data set. They can create 'phantom' issues where a range query might see a different set of records before and after an insert/delete, requiring special range or predicate locks.
8. What is the 'Read Committed' level? [2 Marks]
Answer: Read Committed is a consistency level that guarantees that any data read has been committed at the moment it is read. It prevents dirty reads but allows non-repeatable reads.
9. How can a transaction be rolled back? [2 Marks]
Answer: A rollback is initiated by the system when a conflict (like a deadlock or violation of serialization) is detected. The system undoes all operations performed by that transaction to return the database to its previous consistent state.
10. What is 'Starvation' in the context of concurrency? [3 Marks]
Answer: Starvation occurs when a transaction is never able to acquire the necessary locks because it is continuously preempted or blocked by other, higher-priority or faster-executing transactions. It is a state where the transaction remains perpetually in a wait or abort state.
Section C — 5 Mark Questions
1. Explain the Lock-Based Protocol mechanism in detail. [5 Marks]
Answer:
Introduction
Lock-based protocols are the most common mechanism for concurrency control. They ensure serializability by forcing transactions to request and release locks on data items.
Working Principle
When a transaction intends to perform an operation, it must request a lock from the lock manager. If the item is already locked by another transaction in an incompatible mode, the requesting transaction must wait. There are two primary types of locks: Shared (S) for reading and Exclusive (X) for writing.
Key Rules
- S-lock can be shared: Multiple transactions can hold S-locks on the same item.
- X-lock is exclusive: Only one transaction can hold an X-lock, and no other transaction can hold any lock on that item.
- Lock Release: Locks must be released by the transaction after the operation completes or upon commit/abort.
Importance
This protocol ensures that competing transactions do not corrupt the database state, but it requires careful management to prevent deadlocks.
2. Describe the process of Timestamp-based protocols. [5 Marks]
Answer:
Introduction
Timestamp-based protocols assign a unique timestamp to each transaction at its inception. These timestamps determine the serialization order, ensuring that if T1 starts before T2, the system acts as if T1 occurred before T2.
Protocol Steps
- Each data item holds two timestamps: W-timestamp (latest write) and R-timestamp (latest read).
- When a transaction T attempts to read/write, the protocol compares T's timestamp with the data item's timestamps.
- If T's timestamp is less than the data's timestamp, the operation violates the chronological order and the transaction is aborted.
- Otherwise, the operation is permitted, and the data item's timestamp is updated to T's value.
Advantage
The primary advantage of this protocol is that it is deadlock-free, as transactions do not wait for locks but instead restart upon conflict.
3. Elaborate on the Validation-Based Protocol. [5 Marks]
Answer:
Introduction
This protocol, also known as Optimistic Concurrency Control, assumes conflicts are rare. It allows transactions to proceed without locks and validates them only at the very end.
The Three Phases
- Read Phase: The transaction executes normally but only writes to private local buffers.
- Validation Phase: The system checks if the transaction's read/write operations conflict with other recently committed transactions.
- Write Phase: If validation is successful, changes are made permanent; otherwise, the transaction is rolled back.
Comparison
Unlike lock-based protocols that prevent conflicts proactively, validation-based protocols detect conflicts after they happen, making them highly efficient in systems with low contention.
4. Discuss Deadlock Handling: Detection and Resolution. [5 Marks]
Answer:
Introduction
Deadlock handling is essential in systems using lock-based protocols to ensure system availability. Deadlocks occur when circular dependencies are formed among transactions.
Detection Mechanisms
The system maintains a 'Wait-For Graph' (WFG). If transaction Ti requests a lock held by Tj, an edge (Ti → Tj) is added. A cycle detection algorithm runs periodically to find closed loops in the graph. If a cycle exists, a deadlock has occurred.
Resolution
- Selection of Victim: The system chooses one transaction in the cycle to abort based on factors like age, progress made, or resources locked.
- Rollback: The victim transaction is aborted, and all its locks are released, allowing the other transactions in the cycle to proceed.
- Restart: The victim transaction is typically restarted after a delay to ensure it can successfully complete without re-triggering the deadlock.
5. Explain Weak Levels of Consistency. [5 Marks]
Answer:
Introduction
Weak consistency levels are used to increase performance by reducing the strictness of locking requirements. While they sacrifice total isolation, they allow for higher transaction throughput in large-scale databases.
Types of Consistency
- Read Uncommitted: The lowest level; transactions can read uncommitted data, risking 'dirty reads'. Useful for quick reports where absolute accuracy is less critical than speed.
- Read Committed: Ensures no dirty reads occur. A transaction only sees committed data. However, it allows 'non-repeatable reads', where data might change between two reads in the same transaction.
- Repeatable Read: Ensures that if a transaction reads a row, the same data remains unchanged until the transaction finishes. It prevents non-repeatable reads but might still allow phantom records.
Conclusion
Choosing the correct consistency level involves a trade-off between strict data integrity and system throughput, depending on the application requirements.