DATABASE CONTROL ISSUES: SECURITY, BACKUP AND RECOVERY, CONCURRENCY - Fundamentals of Database Management Systems - page 308

CONCURRENCY CONTROL

The Importance of Concurrency Control
Generally speaking, today's application systems, and especially those running
within the database environment, assume that many people using these systems will
require access to the same data at the same time. Modern hardware and systems
software are certainly capable of supporting such shared data access. One very
common example of this capability is in airline reservations, where several different
reservations clerks, as well as customers on the Web, may have simultaneous
requests for seats on the same flight. Another example is an industrial or retail
inventory application in which several employees on an assembly line or in an order
fulfillment role simultaneously seek to update the same inventory item.
When concurrent access involves only simple retrieval of data, there is no
problem. But when concurrent access requires data modification, the two or more
users attempting to update the data simultaneously have a rather nasty way of
interfering with each other that doesn't happen if they are merely performing
data retrievals. This is certainly the case in the airline reservations and inventory
examples, since selling seats on flights and using items in inventory require that
the number of seats or inventory items left be revised downwards; i.e., many of the
database accesses involve updates. The result can be inaccurate data stored in the
database!

The Lost Update Problem
Using the airline reservations application as an example, here is what can happen
with simultaneous updates, Figure 11.7. And before we begin the example, bear in
mind that we are not talking about simultaneous updates only at the ''microsecond''
level. As you are about to see, the problem can occur when the time spans involved
are in seconds or minutes. Suppose that there are 25 seats left on Acme Airlines
flight #345 on March 12. One day, at 1:45 PM, a reservations clerk, Ms. Brown,
is phoned by a customer who is considering booking four seats on that particular

Time Ms. Brown Mr. Green

1:45 PM Reads the record

Finds 25 seats left

1:48 PM Reads the record

Finds 25 seats left

1:52 PM Deducts 4 seats and

writes updated record
indicating 21 seats
left

1:56 PM Deducts 6 seats and

writes updated record
indicating 19 seats
left

But at this point the record
should show 15 seats left!

F IGURE 11.7
The lost update problem