4.3Deadlock and isolation levels

For a DBA operating an order-processing system where an inventory-aggregation batch and order processing run concurrently, "which isolation level to choose" is one of the most important judgments, balancing the risk of data inconsistency against overall system throughput. The strictest level, SERIALIZABLE, prevents all read anomalies but sacrifices concurrency significantly; the loosest, READ UNCOMMITTED, is fast but even permits dirty reads. This section teaches the judgment needed to identify which kinds of inconsistency the business can tolerate and choose the optimal isolation level, along with how to handle deadlock.

4.3.1Deadlock detection and avoidance

• Deadlock is a state in which two or more transactions each wait indefinitely for the other to release a lock it holds, so that neither can proceed. The DBMS tracks "who is waiting for whom to release a lock" for each transaction as a wait-for graph, and determines that a deadlock exists the moment a cycle appears in this graph.
• There are two approaches to handling deadlock: detection and avoidance. In the detection approach, the wait-for graph is checked periodically for cycles, and once one is found, one of the involved transactions is chosen as the victim and rolled back so the other can proceed. In the avoidance approach, before a lock request is granted, a rule based on, e.g., timestamp order (such as the Wait-Die or Wound-Wait scheme) decides whether to grant the request or abort it immediately, so that a cycle never forms in the first place.