In this section, we discuss issues that emerge in database applications when multiple users access a database system; some users are inserting, updating, or deleting data, while others run queries.
To motivate the problems and solutions discussed here, consider an example. Imagine a user of the winestore wants to buy the last bottle of an expensive, rare wine that’s in stock. The user browses the database and finds the wine. There is only bottle left, and the user quickly adds this to her shopping cart. The shopping cart is a row in the order table with only one related row in the items table. Now, the user decides to finalize the purchase and is presented with a summary of the shopping cart.
However, while the user fumbles about finding her password to log in,
another user enters the system. This user quickly locates the same
wine, sees that there is only one bottle left, adds it to his
shopping cart, logs in to the system, and purchases the wine. When
our first user finally logs in to finalize the order, all the details
look fine, but the wine has actually been sold. Our database
UPDATE operation to deduct from the inventory
fails since the stock value is already zero, and we end up reporting
an error to our original—now very unhappy and
Consider another example. Imagine that one of our winestore stock managers wants to order 12 more bottles of a popular wine, but only if there are less than two dozen bottles currently in stock. ...