Database Security Backup or Recovery

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 consideration includes another item “Tie”. 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 have questions. Only management is allowed to remove data about transactions. Fill out the table below to indicate the rights that you think would be appropriate for this situation. Use the following rights in your answer: read, insert, update, delete, modify structure, grant rights. There are examples in the textbook or online materials that might help you. (12 pts)
(You may need to make some of your own assumptions. If so, state them here.)
DATABASE RIGHTS GRANTED
Table
Sales
Management
Administrative
System Administrator
SALE
Insert
Insert
Read
modify structure, grant rights
SALE_ITEM
Read, update
Read, update
Read
modify structure, grant rights
ITEM
Update
Read, insert, update, delete
Read, insert, update
modify structure, grant rights
8. Bev’s Boutique has developed the following procedure for backup and recovery. The company backs up the entire database from the server to tape every Saturday night. The tapes are then taken to a safety deposit box at a local bank on the following Thursday. Printed paper records of all sales are kept in the shop for 5 years. If the database is ever lost, the plan is to restore the database from the last full backup and reprocess all the sales records. (10 pts)
(a) What problems might occur? (2 pts)
Answer: There could be a hardware failure in the central server for Bev’s Boutique, fire outbreak, human error/sabotage, etc. All these can cause a loss of information from the database. Also, in the case of a fire, the paper records at the store will be lost. Tape recordings are equally unreliable to some extent, especially for a large database back-up. All these are potential errors in this back-up plan.
(b) Do you think “reprocessing” is sufficient for Bev’s Boutique? (2 pts)
Answer: Bev’s boutique is expected to run multiple operational transactions concurrently. While this is good for ensuring a seamless operation across all sectors of the business, not all the transactions are entirely synchronized in their execution. This implies that it might not be feasible to restore all concurrently executed transactions exactly in the order of their implementation. Also, since concurrent transactions run locking techniques to avoid deadlocks, it has a consequence for scheduling data commits. All transactions that have not yet been committed before the manual save will then be lost.
(c) What would be the pros and cons of using a rollback/rollforward system instead? (3 pts)
Answer: The pros of using a rollback/rollforward recovery system is most significantly the reduced time it would take to achieve proper recovery. The manual re-processing approach takes almost the same time as the actual processing, which is a labour-intensive task. Also, with the incorporation of automatic checkpoints and database logging, all committed transactions (before the crash) can be guaranteed an exact recovery. Finally, the automated recovery option requires little to no human intervention i.e. less prone to human error.
The con of using the automatic back-up option is that it is more complicated/difficult to implement.
(d) Describe any changes you think the company should make to this backup/recovery system. (3 pts)
Answer: Since the current back-up system is not very convenient or fool-proof, the automatic recovery option should be adopted. The amount of back-up tapes would be reduced if it only saves checkpoints from the database and the recovery would be more efficient.
The same back-up schedule can be used i.e. Saturdays for copying the latest checkpoint to tape. With less tapes, trips to the safety deposit box will also reduce (perhaps once a month).