Guaranteeing transaction integrity with database locks

Figure 8.36 illustrates how a row-level lock ensures that the transaction is completed first before the next one is run:

Figure 8.36 – Workflow with a row-level lock

Step 3 for Person A will add $10 to the price, making it $110. Because a row-level lock has been applied, Person B will not be able to update the price for Ticket X until the update operation of Person A has finished. Then, once the change has been made for Person A, the update operation of Person B will proceed (after waiting in the queue), adding the $15 to the correct revised price of $110, leading to $125. Amazing, right?

Note

Note that Person A and Person B have no idea that another concurrent operation is happening! Row-level locks simply ensure that even if multiple transactions are happening concurrently, the operations updating the same record would queue automatically and the numbers would still add up!

Here are...