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. The manager runs a query to sum the total stock for that wine from the inventory table. The result is that there are 15 bottles left, so the manager decides to place an order. However, he heads off to fill his coffee cup first, leaving the system displaying the query result.
A second stock manager arrives at her desk with the same intention: to order more of this popular wine if there are less than 24 bottles. The result of the query is the same: 15 bottles. The second manager orders a dozen bottles, and updates the inventory to 27, knowing the bottles will arrive in the afternoon. The problem occurs when the first manager returns: he doesn't rerun the query-why should he?-and he too orders 12 bottles and updates the inventory to 27. Now the system has record of 27 bottles, but two dozen will arrive in the afternoon to take the total to 39!
The first problem is a design issue-as well as an example of an unrepeatable read-and one that can be solved with more restrictive system requirements, knowledge of how the DBMS behaves, and some careful script development. The second is a classic problem-what textbooks describe as a lost update-and it requires more understanding of relational database problems and theory. We cover simple solutions to fundamental problems like these here, and discuss how MySQL implements locking for transactions, concurrency, and performance.
This section isn't intended as a substitute for a relational database text. Most textbooks contain extensive treatment of transaction and concurrency topics, and most of these are highly relevant to the state problems of web database applications.