Consider the relational schema above. The following SQL statement updates the du

Question

Consider the relational schema above. The following SQL statement updates the duty type of a particular work shift:

UPDATE WorkingShift

SET dutyType = 'Security'

WHERE WorkingShiftID = 5001;

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

Publisher Em ployee EmployeeID CHAR(8) Branch BranchID INT Branch Name VARCHAR(45) PublisherID CHAR(3) PublisherName VARCHAR(45) PublisherCity VARCHAR(30) PublisherContactNo CHAR(10) EmployeeName VARCHAR(45) | EmployeeType VARCHAR (20) Branch Address VARCHAR. . . Supervisor-EmployeeID CH ONum berofEm ployees INT Inventory Branch BranchID INT DutyRoster EmployeeID CHAR(8) BranchID INT ? Book_BookID INT AvailableQuantity INT tWorkingShiftID INT Book WorkingShift WorkingShiftID INT workingShiftweekDay VARCHAR(15) BookID INT BookTitle VARCHAR(45) BookType VARCHAR(45) Book Price DECIMAL (8,2) >1sPaperback VARCHAR(3) Publisher-Publisher!D CHAR(3) Writes ? Author AuthorID INT ? Book_BookID INT Author WorkingShiftStartTime TIME(6) WorkingShiftEndTim e TIME(6) dutyType VARCHAR(20) AuthorID INT AuthorName VARCHAR(45) AuthorE mail Address VARCHAR(45) sequenceNumber INT

Explanation / Answer

Hi,
Given query in question is a single update query which will not cause any locking on its own, but when it is combined concurrently with any other query on the same row, then it might cause locking,
so, the actual questions seems an independent one from the query or schema above it,
Lets understand locking first,
there can be 3 kinds of locks
Row locking- lowest level lock possible when a row of a table gets locked
Page locking- a page holds 8k data and the page lock locks the entire page
Table locking - lock that locks the entire table from any further operations
Now, a deadlock  is a situation where a set of commands are blocked because few commands are holding a resource and waiting for another resource acquired by some other
so analogous of this in SQL is,
when a row/page/table lock occurs and we have one more command waiting to acquire the row/page/table and the second command acquires another lock on a separate table/row/page that is being used by command1
so in short both command, wait for each other to release locks holding dependent resources each, this could cause a deadlock,
Note that a lock always cannot cause a DeadLock, but when the locks are on dependent resources, then deadlock could happen

Thumbs up if this was helpful otherwise let me know in comments

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---