Describe a scenario in which an update of QuantityOnHand cou

Describe a scenario in which an update of QuantityOnHand could be lost.

Solution

The coordination of the simultaneous execution of transactions in a multiuser database system is known as concurrency control. The objective of concurrency control is to ensure the serializability of transactions in a multiuser database environment. Concurrency control is important because the simultaneous execution of transactions over a shared database can create several data integrity and consistency problems.

The three main problems are lost updates, uncommitted data, and inconsistent retrievals.

Lost Updates:

The lost update problem occurs when two concurrent transactions, T1 and T2, are updating the same data element and one of the updates is lost (overwritten by the other transaction). Consider the following PRODUCT table example.

One of the PRODUCT table’s attributes is a product’s quantity on hand (PROD_QOH).

Assume that you have a product whose current PROD_QOH value is 35. Also assume that two concurrent transactions, T1 and T2, occur that update the PROD_QOH value for some item in the PRODUCT table.

The transactions are as follows.

Two concurrent transactions update PROD_QOH:

   Transaction                        Operation

T1: Purchase 100 units        PROD_QOH = PROD_QOH + 100
T2: Sell 30 units                PROD_QOH = PROD_QOH – 30

The Following table shows the serial execution of those transactions under normal circumstances, yielding the correct answer PROD_QOH = 105.

Time Transaction step Soredvalue

1 T1 readPROD_QOH 35

2 T1 PROD_QOH = 35+100

3 T1   WritePROD_QOH 135

4 T2   readPROD_QOH 135

5 T2 PROD_QOH = 135-30

6 T2 WritePROD_QOH 105

But suppose that a transaction is able to read a product’s PROD_QOH value from the table before a previous transaction (using the same product) has been committed.

The sequence depicted in the following Table shows how the lost update problem can arise.

Note that the first transaction (T1) has not yet been committed when the second transaction (T2) is executed. Therefore, T2 still operates on the value 35, and its subtraction yields 5 in memory. In the meantime, T1 writes the value 135 to disk, which is promptly overwritten by T2. In short, the addition of 100 units is “lost” during the process.

Time Transaction step Soredvalue

1 T1 readPROD_QOH 35

2 T2    readPROD_QOH 35

3 T1   PROD_QOH = 35+100

4 T2   PROD_QOH = 35-30

5 T1 WritePROD_QOH (lost update)    135

6 T2   WritePROD_QOH 5