Consider the relational schema in Figure 1. The following SQL statement updates

Question

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

UPDATE WorkingShift

SET dutyType = 'Security'

WHERE WorkingShiftID = 5001;

QUESTION/TASK: Give an example of a query or statement that would conflict with the above UPDATE statement (when run concurrently) and would lock the table exclusively (write lock) at page/file level.

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,
Lets first understand what could make the locking happen,
when a table is being updated and concurrently, if its being read/updated by another session then there is a possible locking condition, that is because to update a row, the query is executed such that a lock is obtained and then when some one else is accessing it, this locks the table, so a simple query like
SELECT dutyType where WorkingShiftID =5001 could call the table to get locked

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

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---