QUESTION: Briefly explain how the level of locking (field, record or page) may c

Question

QUESTION: Briefly explain how the level of locking (field, record or page) may contribute to the likelihood of deadlock occurring?  

Branch Publisher Em ployee EmployeeID CHAR(8) BranchID INT Branch Name VARCHAR(45) Branch Address VARCHAR … ONumberOfEmployees INT PublisherID CHAR(3) PublisherName VARCHAR(45) PublisherCity VARCHAR (30) Publish&Contactio; AR(10) EmployeeName VARCHAR(45) EmployeeType VARCHAR(20) Supervisor-EmployeeID CH Indexes Indexes Inventory DutyRoster EmployeeID CHAR(8) BranchID INT t Branch_BranchID INT Book_BookID INT AvailableQuantity INT Indexes t WorkingShiftID INT Book BookID INT WorkingShift WorkingShiftID INT workingShiftWeekDay VARCHAR (15) workingShiftStartTime TIME(6) WorkingShiftEndTim e TIME(6) dutyType VARAR(20) Indexes BookTitle VARCHAR(45) BookType VARCHAR (45) Book Price DECIMAL (8,2) 1sPaperback VARCHAR(3) Writes Author_AuthorID INT Book_BookID INT Author AuthorID INT AuthorName VARCHAR(45) AuthorE mail Address V ARCHAR (45) Indexes sequenceNumber INT Publisher, Publisher!D CHAR(3) Indexes Figure 1 : Relational Data Model

Explanation / Answer

This query is a single update query and it will cause any locks. If this query concurrently combined with another query in a single row then there may occuers lockks. There are three types of locking possible.

The lowest level of the locking is row locking because it lockes when the table row gets locked.

The second locking is Page locking which locks the all or entire page.

The thirs locking is Table locking and it locks entire table.

Deadlock occuers when a single process P1 holds resource R1 and process P2 is waitng resourse R1 to executes.

In locking, deadlock occurs when the locks occurs in the command, if one command is requested same resourse which is held by the another command then deadlock may occurs.

There are two or more sessions possible if deadlock occures due to row level locking.

First sesson:

UPDATE WorkingShift

SET dutyType='security'

WHERE WorkingShiftID=5001;

Second sesson:

UPDATE WorkingShift

SET dutyType='mangaer'

WHERE WorkingShiftID=5001;

First sesson:

UPDATE WorkingShift

SET dutyType='mangaer'

WHERE WorkingShiftID=5001;

Second sesson:

UPDATE WorkingShift

SET dutyType='security'

WHERE WorkingShiftID=5001;

Hence, in first session duty type is set to security and manager both at the same time. There is deadlock detected because both commands perfomed same operation in same row and there is one command is waiting for command which holds by another one.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---