Studyspark Study Document

Database Security Backup or Recovery Question Answer

Pages:7 (2090 words)

Subject:Technology

Topic:Database

Document Type:Question Answer

Document:#98484993


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…


Cite this Document

Join thousands of other students and "spark your studies."

Sign Up for FREE
Related Documents

Studyspark Study Document

Database Security Plan and Requirements Definition for

Pages: 8 (2108 words) Sources: 6 Subject: Education - Computers Document: #78636677

Database Security Plan and Requirements Definition for a University Department The database security plan and requirements definition were developed. The plan included, at the outset, the inclusion of major stakeholder at the University and described their roles in initiating, implementing, and maintaining the plan. Individuals responsible for daily and other periodic tasks were developed. A major consideration in planning the security was the policy that governs granting of access. The need-to-know,

Studyspark Study Document

Database Security

Pages: 9 (2424 words) Sources: 1+ Subject: Education - Computers Document: #3582604

Database Security The focus of this study is that of database security. Databases and database technology are such that play critical roles in the use of computers whether it be in business, electronic commerce, engineering, medicine, genetics, law, education or other such entities requiring the use of computer technology. A database is quite simply a collection of data that is related such as a database containing customer information, supplier information, employee

Studyspark Study Document

Database Security Design of an

Pages: 11 (2976 words) Sources: 1+ Subject: Education - Computers Document: #78984111

With optimistic concurrency control, the database checks resources to determine if any conflicts have occurred only when attempting to change data. If a conflict occurs, the application must read the data and attempt the change again. Pessimistic concurrency, on the other hand, control locks resources as they are required, for the duration of a transaction. Unless deadlocks occur, a transaction is assured of successful completion 5.5 Audit Tracking Although auditing does

Studyspark Study Document

Database Administration Today in Evaluating

Pages: 11 (3489 words) Sources: 15 Subject: Education - Computers Document: #95290228

Design criteria exist at the levels of the technical, system integration aspects of the database to other systems through XML. This integration is critically important to ensure that the applications created can be effectively used over time and not have any scalability issues. There is also the need for designing the databases at the presentation layer to provide for scalability and flexibility of being able to create applications relatively quickly

Studyspark Study Document

Database and Data Mining Security

Pages: 4 (1199 words) Sources: 2 Subject: Education - Computers Document: #63062222

In addition to these two Director-level positions, the roles of the users of the databases and data mining applications also need to be taken into account. The sales, marketing, product management, product marketing, and services departments all need to have access to the databases and data mining applications. In addition, branch offices that access the company's applications over the shared T1 line will also need to have specific security

Studyspark Study Document

Security Policy: The Information Security Environment Is

Pages: 4 (1208 words) Sources: 5 Subject: Business - Management Document: #82654006

Security Policy: The information security environment is evolving because organizations of different sizes usually experience a steady stream of data security threats. Small and large business owners as well as IT managers are kept awake with various things like malware, hacking, botnets, and worms. These managers and business owners are usually concerned whether the network is safe and strong enough to repel attacks. Many organizations are plagued and tend to

Join thousands of other students and

"spark your studies".