PHP —

Transactions and Concurrency

We have illustrated two examples of the problems users have when they access a web database at the same time (that is, concurrently). Allowing uncontrolled interleaving of SQL statements-where each of the users is reading and writing-can result in several well-known problems. The management of a group of SQL statements-we call these transactions-is one important area of the theory and practice of relational databases. Here are four of the more common problems of concurrent read and write transactions:

Lost update problem

User A reads from the database, recording a value. User B reads the same value, then updates the value immediately. User A then updates the value, overwriting the update written by User B. An example of this lost update problem was described in the introduction to this section through the stock update example.

Dirty read problem

User A reads a value from the database, changes the value, and writes it back to the database. User B then reads the value, changes the value, and writes it back to the database. User A then decides not to proceed for some reason with the rest of his actions and therefore wants to undo the changes he made. The problem is that User B has read and used the changed value, resulting in a dirty read problem.

Consider an example: a manager decides to add a 3% surcharge to a particular wine inventory, so she reads and updates the cost of that wine in the inventory table. Another manager decides to apply a 10% discount to all wines made by a particular winery, which happens to include the wine just surcharged. After all this, the first manager realizes she has made a mistake: the wrong wine was updated! Unfortunately, the second manager has already used this incorrect value as input into his update, and the change can't be undone correctly.

Incorrect summary problem

One user updates values while another reads and summarizes the same values. Values summarized may be read before or after each individual update, resulting in unpredictable results.

For example, consider a case in the winestore in which one user updates inventories and another produces a management stock report.

Unrepeatable read problem

A value is read in by a user, updated by another user, and subsequently reread by the first user for verification. Despite not modifying the value, the first user encounters two different values, i.e., an unrepeatable read.

Fortunately, most of these problems can be solved through locking or careful design of scripts that carry out database transactions. However, some problems may be deliberately unsolved in a particular system because they restrict the system requirements or add unnecessary complexity. We discuss locking in the next section.