Concurrency
Concurrency is the term used to mean the simultaneous processing of independent operations in a multi-user environment.
We have already considered the problems that could occur if a second user has access to the changes made by a transaction before the entire transaction is complete. This is one type of concurrency problem known as an inconsistent retrieval.
Things can get even worse if instead of just querying the data the second user tries to update the same records. In this situation there are two simultaneous transactions competing for the same resources. The next two sections outline specific problems that can occur using the example of the e-commerce Web site example.
Lost updates
Imagine two different customers buying quantities of the same product at the same time. Customer1 orders 10 units of the product and Customer2 orders 50, and the PRODUCT record shows that there are currently 100 units available. At the end of each transaction, the remaining quantity of the product needs to be updated. To see how problems can arise, we need to think about the individual step in the update of the product quantity as shown below:
- Read current product quantity
- Calculate new product quantity
- Store new value
If both transactions are running concurrently, it would be entirely possible for the steps of the two transactions to be interleaved as shown below:
| Step | Transaction | Operation | Stored value |
|---|---|---|---|
| 1 | 1 | Read quantity | 100 |
| 2 | 2 | Read quantity | 100 |
| 3 | 1 | Calculate: new value = 100 - 10 | 100 |
| 4 | 2 | Calculate: new value = 100 - 50 | 100 |
| 5 | 1 | Store new value | 90 |
| 6 | 2 | Store new value | 50 |
Notice that after both transactions are complete the value stored in the table is 50 when in fact it should be 40 (50 + 10). The update at step 5 has been lost because both transactions used the original value of 100 as the basis of their calculation.
Uncommitted data
If a transaction T2 has access to the data stored by another transaction T1, a further problem can occur if T1 is rolled back. Again the problem arises because of the interleaving of the low-level steps of the updates associated with the two transactions. The table below illustrate the situation:
| Step | Transaction | Operation | Stored value |
|---|---|---|---|
| 1 | 1 | Read quantity | 100 |
| 2 | 1 | Calculate: new value = 100 - 10 | 100 |
| 3 | 1 | Store new value | 90 |
| 4 | 2 | Read quantity (uncommitted) | 90 |
| 5 | 2 | Calculate: new value = 90 - 50 | 90 |
| 6 | 1 | ROLLBACK | 100 |
| 7 | 2 | Store new value | 40 |
Because the calculation in T2 is based on uncommitted data, the final value stored in the table is 40 when in fact it should be 50.
Data Concurrency and Consistency
Concurrency control