give an exmaple of a query or statement that would conflict with the above UPDAT

Question

give an exmaple 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.

explain how the level lock (field, record or page ) may contribute to the likelihood of deadlock occurring?

Publisher Branch BranchID INT BranchName VARCHAR(45) Em ployee PublisherID CHAR(3) EmployeeID CHAR(8) EmployeeName VAROAR(45) EmployeeType VARCHAR(20) Supervisor-EmployeeID CH PublisherName VARKAR(45) PublisherCity VARCHAR (30) Branch Address V ARCHAR… ONumberofEmployees INT PublisherContacto AR(10) Inventory DutyRoster EmployeeID CHAR(8) Branch ID INT Branch_BranchID INT Book_BookID INT AvailableQuantity INT Indexes tWorkingShifID INT Book BookID INT BookTitle VARCHAR(45) BookType VARCHAR(45) BookPrice DECIMAL (8,2) WorkingShift WorkingShifID INT WorkingShiftweekDay VARCHAR( 15) Author Writes WorkingShiftStartTime TIME(6) workingShi ftEndTim e TIME(6) dutyType VARCHAR(20) AuthoriD INT Author_AuthorID INT Book_BookID INT SequenceNum ber INT IsPaperback VARCHAR(3) AuthorName VARCHAR(45) Auth &Email; Address VARCHAR(45) Publisher PublisherID CHAR(3) Figure 1:Relational Data Model

Explanation / Answer

Query:

SELECT dutyType where WorkingShiftID =5001;

This Query will create a conflict when it is run concurrently with the given one.Due to the fact that,

Additionally If the WHERE clause evaluates to a set of data in a query then a page level lock is used.So the select query here makes a page level lock.

There are various levels of locks based on the type of DB,some are

Now concerning the deadlock if two database operations wait for each other to release a lock then a deadlock occurs.
When two users have a lock, each on a separate object, and, they want to acquire a lock on each other's object in this case also deadlock occurs.
When this happens, the first user has to wait for the second user to release the lock, but the second user will not release it until the lock on the first user's object is freed. At this point, both the users are at an impasse and cannot proceed with their business.In such a case,the DBMS detects the deadlock automatically and solves the problem by aborting one of the two transactions.

Row Level locking

If the WHERE clause evaluates to only one row in the table, a row level lock is used.

Deadlock contribution:

For instance if a user lock a particular entry rowwise and if the database administrator decides to a particular field in the table for all the user then for the locked row, the administrator cant make any updations and he need to wait till the lock is released.

Field Level:

A field level lock just means that some columns within a given row in a given table are locked

DeadLock Impact:

If a field got locked that particular field in every row cannot be modifield

Page Level Lock:

In a table if the WHERE clause evaluates to a set of data, a page level lock is used.

DeadLock Impact:

Set of records in a table can`t be modified if the lock in active and any new user need access to modify the table has to wait,

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.