Assuming that pessimistic locking is being used but the two-phase locking protocol is not, create a chronological list of the locking, unlocking, and data manipulation activities that would occur during the complete processing of the transaction described in Problem 6a.

Lock

A lock is that important tool that is used in concurrency control which guarantees that the data item is being used by only one transaction at a time. That is if in any transaction T1 a lock is applied on one item ‘A’ then ‘A’ cannot be used by any other transaction until it is free from T1.

A data item exists in two forms first is locked and the other is unlocked form. In the locked form the item is being used by some transaction and in unloaced form the data item is free from any of the transaction.

Types of locking:

1. Pessimistic locking: It stops or acts as an obstacle for other applications or any user from searching or making any change to the same records at the same time. Pessimistic locking would have been a powerful tool but the limitations were put on by databases.

2. Two phase locking protocol: Two phase locking protocol is the locking protocol which first locks all the dataitems required by any of the transaction and then unlock it by using cascading unlock. It is divided into two phases:-

a. Growing phase: It can acquire locks, but cannot release locks

b. Shrinking phase: It can release locks, but cannot acquire locks.

Chronological list of locking using pessimistic locking strategy

In the following steps the chronological list of the activity performed by customer 10010 on 11 May,2012 using pessimistic locking is given. That is the transactions performed by a customer 10010 on 11 May 2012 is as:

1. Firstly the values will be inserted into INVOICE table which has the attributes INV_NUMBER, CUS_CODE, INV_DATE, INV_TOTAL, INV_TERMS, and INV_STATUS.

2. After that the updating will be done into LINE table for columns INV_NUMBER, LINE_NUMBER, P_CODE, LINE_UNITS, LINE_PRICE.

3. Then in the PRODUCT table the product quantity column P_QTYOH will be updated by reducing it by 1.

4. Finally the CUSTOMER table will be updated by setting the date of purchase CUS_DATELSTPUR as 11-MAY-2012 and incrementing customer balance by 118.80, as tax rate is 8%.

Chronological list shows the step by step activity of performing a job from starting till end. The chronological order of locking for the above steps is as below:

TIME  ACTION

1  Lock INVOICE

2  Insert row 10983 into INVOICE

3  Unlock INVOICE

4  Lock LINE

5  Insert into rows 10983, 1 into LINE

6  Unlock LINE

7  Lock PRODUCT

8  Update PRODUCT 11 QER/31,P_QTYOH from 47 to 46

9  Unlock PRODUCT

10  Lock CUSTOMER

11  Update CUSTOMER 10010,CUS_BALANCE from 345.67 to 464.47

12  Update CUSTOMER10010,CUS_DATELSTPUR from 05 May 2010 to 11 May 2012

13  Unlock CUSTOMER