Assume that each individual SQL statement is executed in isolation, that the DBMS uses intention locking and sets locks on tables and rows, and that host variable f contains the number of the flight to be booked. The transaction should not overbook the flight.

An airlines database has two tables: Flights, with attributes flt_num, plane_id,
num_reserv; and Planes, with attributes plane_id, and num_seats.
The attributes have the obvious semantics. A reservation transaction contains the
following steps:

SELECT F.plane_id, F.num_reserv

INTO :p, :n

FROM Flights F

WHERE F.flt_num = :f

A. SELECT P.num_seats

INTO :s

FROM Planes P

WHERE P.plane_id = :p

B. . . . check that n < s ...

C. UPDATE Flights F

SET F.num_reserv = :n + 1

WHERE F.flt_num = :f

D. COMMIT

a. Assuming that the transaction is run at READ COMMITTED, w hat locks are held at points A, B, and D?
b. The database can be left in an incorrect state if concurrently executing reservation transactions that are running at READ COMMITTED are interleaved in such a way that one transaction is completely executed at point B in the execution of another. Describe the problem.
c. In an attempt to avoid the problem described in (b), the SET clause of the UPDATE statement is changed to F.num_reserv = F.num_reserv + 1. Can reservation
transactions nowbe run correctly at READ COMMITTED? Explain.
d. Assuming that the transaction is run at REPEATABLE READ and that the tables are accessed through indices, what table locks are held at points A, B, and D?
e. What problem does the interleaving of (b) cause at REPEATABLE READ? Explain.
f. Does the interleaving of (b) cause an incorrect state if the transaction (either version) is run using SNAPSHOT isolation? Explain.
g. To keep track of each passenger, a newtable, Passenger, is introduced that has
a rowdescribing each passenger on each flight with attributes name, flt_num,
seat_id. SQL statements are appended to the end of the transaction (1) to read the
seat_id’s assigned to each passenger on the flight specified in f and (2) to insert a
rowfor the newpassenger that assigns an empty seat to that passenger. What is the
weakest ANSI isolation level at which the transaction can be run without producing
an incorrect state (i.e., two passengers in the same seat)? Explain.

a. A - No locks
B - No locks
D - X lock on row in Flights, IX lock on Flights
b. Lost update causing database to incorrectly record number of passengers and flight
might be overbooked
c. Flight can still be overbooked, but database correctly records number of passengers
d. A - S lock on row in Flights, IS lock on Flights
B -The locks named in (A), plus S lock on rowin Planes, IS lock on Planes
D - X lock on row in Flights, IX lock on Flights, S lock on rowin Planes, IS
lock on Planes
e. Solution:
Deadlock
f. No. If two transactions attempt to update the same tuple, one will be aborted.
g. REPEATABLE READ. No phantom is possible since only one transaction can add a
tuple to Passenger for a particular flight at a time (since a transaction must hold
an X lock on the rowin Flights before inserting a rowin Passenger).