Consider the relational schema above. The following SQL statement updates the du
ID: 3607661 • Letter: C
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: 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.
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 INTExplanation / Answer
When we run the above query in parallel with the above update statement it will get conflict over the table. when we execute the above queries concurrently one after another the statements will executes successfully
In the first case we will get the conflit on the table because both the statements trying to update the same attribute then it that the tuple will be in inconsistent state.
The locking tuples will solve the problems because when one query is executing the corresponding tuple will get locked so no other query will be able to update at that time it needs to wait for the release of the lock. When we execute the both queries one query will acquire the lock and the other will be wait for the release of the lock. So no inconsistencies will occur.