WA UNIT 2 - Construct an E-R diagram for a car-insurance company whose customers own one PDF

Preview

Summary

Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents....

Description

CS 3306 Databases 2 February 9, 2022 Title: Concurrency Control

Introduction Deadlocks are a consequence of locks which are a necessary control mechanism for enforcing the isolation properties of database integrity. Depending on the model of a lock, it limits modification access to data, ensuring that only a single transaction can update a shared data at a time. Deadlock happens when every transaction is waiting for another transaction to finish (Silberschatz, et al, 2001). In other words, a deadlock is created when two applications lock data that is needed by the other, resulting in a situation in which neither application can continue executing. Since applications do not voluntarily release a lock on data until they are finished with the intended execution, there is a need for a detection mechanism to both detect locks in deadlock and process the release as required (IBM Documentation, 2014). This article discusses a detection algorithm and actions needed to break a deadlock.

Deadlock Detection Algorithm and Actions A deadlock detection algorithm can be built on the generated wait-for-graph by periodically checking at a predefined if a cycle has occurred in the directed graph since a deadlock exist if the directed graph contains a cycle. If a deadlock is detected, the algorithm attempts a rollback on one or more transactions to break the deadlock (Silberschatz, 2001). The actions taken include: 1. Select a victim transaction from the list of transactions held in the deadlock using a cost factor. The criteria for cost factor could be based on any or a combination of the following: a. The transaction that’d performed the least work. b. The transaction that’s farthest from completion. c. The number of transactions that would be rollback along with the selected victim. 2. Rollback with a consideration of whether to perform a total rollback by aborting the transactions and restarting, or perform a partial rollback by rolling back as far as necessary to break the deadlock. 3. The last action is to avoid starving selected transactions based on cost factor that result in a repeated selection of the same victim thereby leading to the transaction not being run at all. (Silberschatz, 2001)

Victim Selection for Figure 16.19 Deadlock. This deadlock includes multiple transactions that fit the victim selection criteria. For example, T26, T27, and T28 all have equal locks. However, T28 has the least dependence in terms of the number of transactions that will be impacted by its rollback. Consequently, T28 will be a better candidate for a victim. Rollback Action for Figure 16.19 Deadlock Based on the explanation above, the selection of T28 removed the breaks of the deadlock by removing the wait from T28  T27. A partial rollback can also be attempted. Starvation Action for Figure 16.19 Deadlock The T28 transaction may end up being selected for every round of victim selection and rollback. This will lead to starvation. To mitigate this experience, the rollback attempt will include a counter after which another victim will be selected. However, any other victim selected from the current deadlock scenario will result in a total rollback. In conclusion, while the deadlock detection and intervention can be optimized to improve performance, a prevention-based schema preemptively aborts the occurrence of a deadlock (Dinesh, nd). This schema is in contrast to the deadlock but is not considered a better option for all instances.

References Dinesh, T. (n.d). How to Deadlock Detect and Recover. https://ecomputernotes.com/database-system/rdbms/deadlock-detect-and-recover IBM Documentation. (2014). Deadlocks. https://www.ibm.com/docs/en/db2/11.1?topic=management-deadlocks Silberschatz, A., Korth, H.F., & Sudarshan, S. (2001). Database System Concepts (4th ed.). New York, NY: McGraw-Hill...