For each type of data anomaly (lost update, dirty read, nonrepeatable read, and phantom read), construct an example transaction schedule to illustrate the anomaly. Be sure to include a brief description of the expected (i.e., correct) behavior and what actually occurs. (solutions)

The transaction schedule for each anomaly should show the interleaving of two transactions (e.g., Alice and Bob are both travel agents trying to manage individuals’ reservations). For example,

Alice Bob
First operation by Alice  
  First operation by Bob
Second operation by Alice  
  Second operation by Bob

and so forth.

Examples

  1. Lost update

    In the following example, Alice and Bob manage two reservations concurrently. Alice books 3 seats on a flight, and Bob cancels an existing reservation (1 seat) for that flight.

    Alice Bob
    Alice starts a reservation for a client  
    Alice checks the remaining seats on a flight (currently 20)  
      Bob opens an existing reservation for a client
      Bob reads the current number of seats (currently 20)
    Alice books 3 seats (17 seats remaining)  
      Bob cancels the client’s existing reservation, releasing 1 seat (21 seats remaining)

    Bob’s change overwrites Alice’s, leading to an incorrect number of seats being recorded.

  2. Dirty read

    In the following example, Alice works on a reservation for a client, but at the last minute, that reservation is canceled due to the client’s credit card being declined. Meanwhile, Bob cancels a separate reservation.

    Alice Bob
    Alice starts a reservation for a client  
    Alice checks the remaining seats on a flight (currently 20)  
    Alice books 3 seats (17 seats remaining)  
      Bob opens an existing reservation
      Bob reads the current number of seats (currently 17)
    Alice’s client chooses to cancel the reservation (currently 20 seats remaining)  
      Bob cancels the existing reservation, releasing 1 seat (18 seats remaining)

    Bob’s change results in the wrong number of seats being recorded because it is based on the number of seats from an aborted transaction.

  3. Nonrepeatable read

    In the following example, Alice starts to make a reservation, but her client changes the number of seats being requested from 3 to 13 (for a group trip to Europe). In the interim, Bob cancels an existing reservation.

    Alice Bob
    Alice starts a reservation for a client  
    Alice checks the remaining seats on a flight (currently 20)  
      Bob opens an existing reservation
      Bob reads the current number of seats (currently 20)
      Bob cancels the existing reservation, releasing one seat (21 seats remaining)
    Alice checks the remaining seats on a flight (currently 21)  

    When Alice checks the remaining seats on the flight, it is inconsistent with what she saw previously – 21 seats available instead of 20 seats.

  4. Phantom read

    Alice receives an audit request from Acme Corporation, requesting a summary of all their employees’ flights in the latter half of January. Alice initially mistypes the dates, but quickly corrects her mistake. In the interim, though, Bob cancels a reservation for an Acme employee during this time span.

    Alice Bob
    Alice receives a request for the flights of all employees of the Acme Corporation from 15–30 January  
      Bob opens an existing reservation for an Acme employee
      Bob cancels the existing reservation for 18 January
    Alice updates the summary for the latter half of the month (16–31 January)  

    In this instance, the reservation deleted by Bob is included in the first summary but not the second even though it is within the range of both queries.