---

Handling lock requests

---

• Handling lock and unlock requests
  • How to process a lock request on database element A:

    Insert a new transaction lock request record into the lock table entry for the DB element A Determine if lock request can be granted         Update the lock state of the lock table entry for the DB element A if needed

    ---

    ---

  • How to process a unlock request on database element A:

    Delete the transaction lock request record from the lock table entry for the DB element A Check if some (pending) lock request can now be granted          Update the lock state of the lock table entry for the DB element A if needed

    ---

    ---

  • We will illustrate the lock maintenance algorithm with a few examples below

  ---

  ---

  ---

  ---

• Handling lock requests
  • Case 1: transaction T₁ requests a shared lock while DB element is not locked

    Before processing the request: The lock request will be granted (according to the compatibility table) After processing the request:

    ---

    ---

    ---

  • Case 2: transaction T₂ requests a update lock while DB element is shared locked

    Before processing the request: The lock request will be granted (according to the compatibility table) After processing the request: Notice: The group mode is updated to update locked

    ---

    ---

    ---

  • Case 3: transaction T₃ requests an exclusive lock while DB element is update locked

    Before processing the request: The lock request will be denied (according to the compatibility table) After processing the request: Notice: The exclusive lock request is pending in the "queue" of lock requests !!!

  ---

  ---

  ---

  ---

• Handling unlock requests
  • Case 1: transaction T₂ unlocks its update lock

    Before processing the request: Transaction T1 will still be holding a shared lock on the database element !!!! After processing the request: Notice: The group mode of the lock has been changed to shared !!!

    ---

    ---

    ---

  • Case 2: transaction T₁ unlocks its shared lock

    Before processing the request: There will be no locks on the database element However: There is a pending lock request (which was conflicting with the old lock state) !!! After processing the request: Notice: The pending lock request has been granted !!!!

  ---

  ---

  ---

  ---

• Lock grant policy: what happens when there are multiple pending requests
  • Suppose there are multiple lock requests pending:

    $64,000 question:

    If the exclusive lock is unlocked: Which lock request will be granted next ???

    ---

    ---

  • Commonly used lock granting policies:

    First Come, First Serve: Strength: fair Weakness: does not give priority to more important transactions Shared lock first Strength: maximize parallelism (because multiple shared requests can be granted) Weakness: can starve the update transactions Update lock first Strength: allows the updates to be processed as soon as possible Weakness: lower performance (Processing time is longer because there is less parallellism)

    The policy decision used often depends on how the database is used.....