Lab 7: Locking

Locking basics

Assume that a DBMS is using regular two-phase locking with three types of locks: shared, update and exclusive.

Consider the following partial schedule of three transactions:

Given the locks that the transactions would need to acquire, which of the following operations could happen next in the schedule? Explain each answer briefly.

1. r1(F)

2. w2(A)

3. r2(E)

4. w3(D)

5. w2(C)

6. r2(D) (followed sometime later by w2(D))

7. r3(C) (followed sometime later by w3(C))

Locking and deadlock detection

Assume that:

• A DBMS is using rigorous two-phase locking.
• An exclusive lock for an item is requested just before writing that item.
• A shared lock for an item is requested (if needed) just before reading that item.
• Upgrades of shared locks are allowed.
• Update locks are not being used.
• The system performs deadlock detection using a waits-for graph.
• A transaction commits immediately after completing its final read or write.

Consider the following schedule involving the transactions T1, T2, and T3:

r1(X); r1(Z); w2(Y); w2(X); r3(Z); w3(Y); w1(Z)

1. Determine if this schedule would lead to deadlock.

   If deadlock occurs, show the partial schedule in table form (including lock operations and any commits) up to the point of deadlock, along with the waits-for graph at the point of deadlock.

   If deadlock does not occur, show the full schedule, including lock operations and commits. In that case, you do not need to include the waits-for graph.

   In either case, your schedule should include any changes to the sequence of operations that occur because a transaction is forced to wait for a lock.

   If two transactions wait for a lock held by the same other transaction, and that other transaction subsequently commits, you should assume that the waiting transaction with the smaller transaction number is granted its request first.

2. Now imagine that we change the final action in the schedule to w1(X):

   r1(X); r1(Z); w2(Y); w2(X); r3(Z); w3(Y); w1(X)
   

   How would that change your answer?

Last updated on October 30, 2024.