---

Archtecture of the lock table (and how Part 2 of scheduler uses it)

---

• Structure of a lock table
  • Structure of a lock table:

    Explanation:

    The lock table is usually implemented as: Hash table in main memory: The DB elements that does not have been locked are not stored in the Hash table !!! Each lock table entry contains the follwoing fields: Meaning of the fields: Group mode = the most stringent lock mode granted to a transaction Waiting ?: Yes if some transaction is waiting on a lock request for this DB element No if no transaction is waiting on a lock request for this DB element List of transactions = the list of transactions that has made a lock request for the DB element Some of these requests may have been granted Some of these requests may have been denied --- then the transaction is waiting for the request The structure of the element in the list of transaction is explained next Structure of a request made by a transaction: Meaning of the fields: Transaction ID = the identifier of the transaction that made the lock request Lock mode = the mode of the lock requested (shared, update, exclusive, etc.) Wait? : Yes means: the transaction is waiting for the request to be granted No means: the transaction is holding a lock in the requested mode !!! Tnext = a linking field used to chain all the lock requests made by the same transaction Example: Next = a linking field used to chain all the lock requests on the same DB element

    ---

    ---

  • Note:

    To keep the figures simple, I (and the text book) have omitted the Tnext links Keep in mind that one transaction can make multiple lock requests All lock requests made by the same transaction are linked together: This allows the DBMS to unlock all the locks held by a transaction when it commits/aborts

  ---

  ---

  ---

  ---

• Handling lock and unlock requests
  • Part 2 of the scheduler is responsible for processing lock requests:

    Each lock request will result in: Inserting a new transaction lock request record into the corresponding lock table entry for the DB element Updating the lock state of the DB element if needed Each unlock request will result in: Deleting an existing transaction lock request record into the corresponding lock table entry for the DB element Check if some lock request can now be granted Updating the lock state of the DB element if needed

    ---

    ---

  • We will now go through a number of scenarios....

  ---

  ---

  ---

  ---

• Handling lock requests
  • Case 1: DB element is not locked

    Before: After:

    ---

    ---

    ---

  • Case 2: transaction (T2) makes a lock request that is granted

    Before: After:

    ---

    ---

    ---

  • Case 3: transaction (T3) makes a lock request that is denied

    Before: After:

  ---

  ---

  ---

  ---

• Handling unlock requests
  • Case 1: does not grant a lock to a waiting transaction

    Before: After:

    ---

    ---

    ---

  • Case 2: grants a lock to a waiting transaction

    Before: After:

  ---

  ---

  ---

  ---

• Multiple waiting requests: grant policy
  • Suppose there are multiple lock requests waiting:

    ---

    ---

  • Commonly used policies:

    First Come, First Serve: Strength: fair Weakness: does not use priority for important transactions Shared lock first Strength: maximize parallelism (all shared requests are granted) Weakness: can starve update transactions Update lock first Strength: get the updates processed as soon as possible Weakness: lower performance --- longer delay because less parallellism

    Policy decision often depends on how the database is used.....