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;

1. Give an example of a query or statement that would conflict with the above UPDATE statement if both the statements are run at the same time (when run concurrently). Explain what could go wrong, and how locking tuples would solve the problem.

2. 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.

3. 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

1.

THe conflict query with the above query is:

update workingshift set dutytype='managers' where workingshiftID='5001';

2.

lockingtable in write lock mode:

lock table workingshift in exclusive mode;

3.

If locking  done to row level may cause deadlock when two or more sessions are used

session1:

update workingshift set dutytype='security' where workingshiftID='5001';

session2:

update workingshift set dutytype='managers' where workingshiftID='5001';

session 1:

update workingshift set dutytype='managers' where workingshiftID='5001';

session2:

update workingshift set dutytype='security' where workingshiftID='5001';

here in session1 it shows that deadlock is detected,because of locks, when used commit then locks will be released.

  

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---