Issues in Writing Data to Databases

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 confused—user.

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. ...

Get Web Database Applications with PHP, and MySQL now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.