Part 1: Concurrency Issues in the Database 1. Considering that these various transactions use some of the same tables and fields in the database, explain why it is important for the changes made by each of these transactions to be atomic. (2 pts)

Answer: The necessity of an atomic transaction, in this case, is to maintain state consistency in the database system. Since various transactions require the use of the same fields/data within the database, a sequential update to each field (prioritized based on order of transactions conducted) could lead to an imbalance in the overall system. For example: assuming a few items are sold to a customer and the sales information is updated. There is a need for an immediate update to the “QuantityOnHand” field (a reduction, since a certain unit of items have been sold), along with an update to the “total sales”, “Profit” and other relevant fields. Also, atomic implementation of transactions allows for easy recall/rollback of temporary updates to fields during the “transaction processing period”. This ensures that the database is left unchanged in the case of a failed event in the transaction, which maintains correct state in the said database: if the customer’s card gets declined for example.

2. Describe a scenario in which an update of QuantityOnHand could be lost. (See the example in the textbook and apply it to Bev’s) (5 pts)

Answer: In the case of Bev’s store, the likelihood of concurrency in database update can exist when there is a re-stocking of the store due to the arrival of new items from vendors. It is expected of the store personnel to stock the items and run an “Item Quantity Received Transaction”, which updates the “QuantityOnHand” field. Since this transaction is independent of the sales transaction, which also updates the “QuantityOnHand” field, there lies a potential concurrency. If one of the items being stocked is a “hand crafted handbag”, and a sales transaction was initiated at the same time as an “Item Quantity Received Transaction” on 5 extra units of that bag. The likely consequence is that both the sales transaction and “Item Quantity Received Transaction” reads a copy of the “QuantityOnHand” field value, of the bag item, to temporary memory within their local function spheres. If sales transaction has priority (perhaps due to initiation at a slightly earlier instance than the Item Quantity Received Transaction), then the sales transaction decrements the “QuantityOnHand” field by the “input Quantity” value and updates the “QuantityOnHand” field accordingly. While the new value for “QuantityOnHand” has been decremented, the “Item Quantity Received Transaction” still operates on the earlier copied variable value of the “QuantityOnHand” and increments its value by 5! Upon update, the new “QuantityOnHand” will be more than its actual value by a factor of the “input Quantity value” i.e. it will not reflect the decrement due to sales.

3. Explain how locking could be used to prevent the lost update in your answer to Q2. (3 pts)

Answer: Resource locking disallows sharing of resource values that have been requested for update i.e. once one application has requested for an update on a field, it is temporarily made unavailable to other applications until the update is registered (as either failed or successful). This intuitively solves the problem identified in Q2, because resource locking would allow just one of the transactions access to the value of the “QuantityOnHand” field at a time. Hence, the sales transaction (which requested first) would have to finish its update on the “QuantityOnHand” i.e. decrement by the “input Quantity” value, before the “Item Quantity Received Transaction” can access the “QuantityOnHand” field (having reflected the decrement due to sales). When the “Item Quantity Received Transaction” increments the “QuantityOnHand” field by ‘5’, the value will be accurate.

4. Assume you are taking a “pessimistic” approach and lock entire tables needed for a transaction. You lock each table before you access it, and you don’t release all locks until the end of a transaction. Is it possible for deadlock to occur between two Sale Transactions? (2 pts) Why or why not? (2 pts)

Answer: Yes, it is possible for a deadlock to occur.

While a “pessimistic locking” strategy anticipates the possibility of a lock, there is going to be a deadlock in the implementation of locks for concurrent sales transactions if there is no specific order in which sales transactions obtain and release locks.

For example: say customer A orders for a product “Shirt” and after extra...

It is likely that Customer A’s order looks like this:
Lock shirt; decrement “Quantity On Hand” for shirt by ‘Input Quantity value’ and Update; Lock tie; decrement “Quantity On Hand” for Tie by ‘Input Quantity value’ and Update; release all locks.

Assuming another transaction by customer B is implemented to request for Tie first, and then buy some shirts. Their order would look like this:

Lock tie; decrement “Quantity On Hand” for tie by ‘Input Quantity value’ and Update; Lock shirt; decrement “Quantity On Hand” for shirt by ‘Input Quantity value’ and Update; release all locks.

The database receives the following requests for a concurrent operation of both transactions:

Lock shirt for A; Lock tie for B; decrement “Quantity On Hand” for shirt by ‘Input Quantity value’ and Update for A; decrement “Quantity On Hand” for Tie by ‘Input Quantity value’ and Update for B; “wait for lock on Tie to be released by B, then decrement “Quantity On Hand” for Tie by ‘Input Quantity value’ and Update for A”; “wait for lock on Shirt to be released by A, then decrement “Quantity On Hand” for Shirt by ‘Input Quantity value’ and Update for B; release all locks for A and then B.

As can be inferred from the sequence of the instructions received by the database, the waiting period for both transactions will be indefinite i.e. a deadlock.

5. Assume you are taking a “pessimistic” approach and lock entire tables needed for a transaction. You lock each table before you access it, and you don’t release all locks until the end of a transaction. Is it possible for deadlock to occur between a Sale Transaction and an Item Quantity Received Transaction? (2 pts) Why or why not? (2 pts)

Answer: No, it is impossible for a deadlock to occur.

In this case, the two transactions are only related in operation by the “QuantityOnHand” field. Since the pessimistic approach locks a transaction before updating it, whichever process first requests for the lock will have to complete its update and unlock the field before the other can do likewise. So irrespective of how the order of the transactions occur, there can never be a lock using the pessimistic approach. There would, however, be a delay of resources if one transaction takes too much time to complete.

6. Considering the three transactions and your answers to previous questions, describe whether you think optimistic or pessimistic locking would be better. Explain the reasons for your answer. (5 pts)

Answer: In all these cases, the optimistic locking approach would be a more suitable option. In the case of a lost update, the optimistic locking technique will check for consistency between its “about to be updated” data and the current value of the same field. If no changes have occurred since the transaction was initiated, the lock is implemented, update is accepted, and a verification is done to ensure a successful update. This benefit is also obtained using the pessimistic approach (even better, without need for extra verification of a successful update); however, the optimistic approach is more suitable for such instances when the sales and Item Quantity Received Transactions are run concurrently. As explained in 5, while pessimistic locking ensures there will be no deadlock in the operation, it leads to a longer delay as each process must wait for the “QuantityOnHand” resource to be unlocked before processing. If the optimistic approach is taken, most of the operations for both transactions are run independently until the lock, verify, and update part, which is a relatively shorter delay and equally without deadlock. Lastly for the sales transaction, an optimistic approach would be better as most of the requests would have been processed before reaching the lock part, leading to a 0% chance of deadlock. At worst, the requested transaction will fail if it cannot obtain the value it needs due to a lock from another transaction. Since it is an offline store, it is most likely that there a just few customers buying a certain product at a time. This reduces the tendency for multiple failed transactions and speeds up the entire operation.

Part 2: Security and Backup/Recovery

7. Suppose that management at Bev’s Boutique identifies four groups of users: sales personnel, managers, administrative personnel, and system administrators. Managers and administrative personnel can perform Item Quantity Received Transactions, but only managers can perform Item Price Adjustment Transactions. Sales personnel and managers can complete sales transactions, but administrative personnel are able to look up previous sales if customers…