#9.17What is deadlock? How can it be avoided? How can it be resolved once it occ
ID: 3713893 • Letter: #
Question
#9.17What is deadlock? How can it be avoided? How can it be resolved once it occurs?
#9.18Explain the difference between optimistic and pessimistic locking.
Assume that Morgan has created a database with the tables described at the end of Chapter 7 (note that STORE uses the surrogate key StoreID):
EMPLOYEE (EmployeeID, LastName, FirstName, Department, Phone, Fax, EmailAddress)
STORE (StoreID, StoreName, City, Country, Phone, Fax, EmailAddress, Contact)
PURCHASE_ITEM (PurchaseItemID, StoreID, PurchasingAgentID, PurchaseDate, ItemDescription, Category, PriceUSD)
SHIPMENT (ShipmentID, ShipperID, PurchasingAgentID, ShipperInvoiceNumber, Origin, Destination, ScheduledDepartureDate, ActualDepartureDate, EstimatedArrivalDate)
SHIPMENT_ITEM (ShipmentID, ShipmentItemID, PurchaseItemID, InsuredValue)
SHIPPER (ShipperID, ShipperName, Phone, Fax, Email, Contact)
SHIPMENT_RECEIPT (ReceiptNumber, ShipmentID, PurchaseItemID, ReceivingAgent, ReceiptDate, ReceiptTime, ReceiptQuantity, isReceivedUndamaged, DamageNotes)
Assume that all relationships have been defined as implied by the foreign keys in this table list. Note: If you are using the older SQL Server 2005, see the notes at the end of this document.
James Morgan wants to modify the database design of the Morgan Imports procurement information system (MIPIS) to separate the items in PURCHASE_ITEM in a separate table named ITEM. This will allow each item to be tracked as a unique entity throughout its acquisition and sale. The schema for the ITEM table is:
ITEM (ItemID, ItemDescription, Category)
PURCHASE_ITEM will then be replaced by two tables named INVOICE and INVOICE_LINE_ITEM, linked in a modified sales order configuration as shown in Figure 8-7 (compare this figure to Figure 6-18(b)).
Similarly, the shipping part of the MIPIS will be modified by changes to the SHIPMENT_ITEM tables as follows:
SHIPMENT_LINE_ITEM (ShipmentID, ShipmentLineNumber, ItemID, InsuredValue)
If you want to run these solutions in a DBMS product, first create a version of the MI database described in Chapter 7 and name it MI-CH08.
FIGURE 8-7 The Morgan Importing MIPIS Modified SALES ORDER Configuration EMPLOYEE STORE EmployeelD StorelD LastName FirstName Department Phone Fax EmailAddress StoreName City Country Phone Fax EmailAddress INVOICE InvoiceNumber InvoiceDate StorelD(FK) PurchasingAgentlD(FK) SubtotalUSD TaxUSD TotalUSD TEM Item ID ItemDescription Category INVOICE LINE ITEM InvoiceNumber(FK) LineNumber ItemlD(FK Quantity UnitPriceUSD ExtendedPriceUSD Create a dependency graph that shows dependencies among the original set of tables Explain how you need to extend this graph for views and other database constructs such as stored proceduresExplanation / Answer
9.17.
A deadlock occurs when two different users or transactions require access to data that is being locked by the other user. It can be avoided in 2 ways, 1 is to set measures which prevent deadlocks from happening, and 2 is to set ways in which to break the deadlock after it happens. One way to prevent or to avoid deadlocks is to require the user to request all necessary locks atone time, ensuring they gain access to everything they need or nothing. Secondly, sometimes they can be avoided by setting resource access order, meaning resources must be locked in a certain order to prevent such instances. Essentially once a deadlock does occur the DBMS must have a method for detecting the deadlock, and then to resolve it, the DBMS must select a transaction to cancel and revert the entire transaction until the resources required become available, allowing one transaction to complete while the other has to be reprocessed at a later time
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.