Question

A multi-user database system is used to support the customer payment operations of a XYZ store. Unfortunately, the system currently does not enforce concurrent control on transactions accessing and updating the data. A particular customer had a balance due of HK$300 when the following three transactions related to the customer were processed at the same time:

(i) Payment of HK$300;
(ii) Purchase on credit of HK$150; and
(iii) Merchandise return (credit) of HK$100.
Each of the three transactions read the customer record when the balance was HK$300. That is, all transactions have read the record before any of them was completed. The updated customer record was returned from the transactions to the database in the order of first (i)then (ii) and finally
(iii)(a) After the last transaction was completed, what balance will be recorded for the customer?
(b) What balance should be actually recorded for the customer after the three transactions have been processed?

Answer 1

Due to the lack of concurrent control in the XYZ store's multi-user database system, the recorded balance for the customer after processing three simultaneous transactions was HK$100, which is incorrect. The actual balance, after considering all transactions in sequence, should be HK$50.

Step-by-step explanation:

A multi-user database system used in the XYZ store's customer payment operations is facing issues with concurrent transactions. Let's analyze the transactions that occurred simultaneously on a customer's record, which initially showed a balance due of HK$300.

• Payment of HK$300 (transaction i)
• Purchase on credit of HK$150 (transaction ii)
• Merchandise return (credit) of HK$100 (transaction iii)

With the transactions processed in the order of (i), (ii), and then (iii), and all reading the initial balance before any updates, the resultant balance after the last transaction is completed would erroneously be HK$100 due to the lack of concurrent control. This is because each transaction was updated based on the original balance without considering the impact of the others.

The correct balance, however, should consider all transactions: the payment would bring the balance to HK$0, the subsequent purchase would increase it to HK$150, and finally, the merchandise return would decrease it to HK$50. Therefore, the actual balance should be HK$50.