Exposing Locktree State

The locktree is an in-memory data structure that represents the row locks that are held and waited-on by live transactions. When a client fails to acquire a row lock (because of a conflicting row lock owned by some other transaction), there is little information reported back to the caller. As a result, it is difficult to debug applications that are experiencing lock wait timeout. We want to make it easier to debug such applications by exposing locktree state information to the user.

What is stored in the locktree?

Row locks, each containing:

• The key range that is locked
• The transaction id that owns the lock

Pending lock requests, each containing:

• The key range that is requested
• The transaction id that is blocked

What information could be useful to users?

• The current state of the locktree and all lock requests.
  • Could include which transactions/ranges each request is waiting on
  • Example (in json):

    {
        locks: [
            { txnid: 5, ranges: [ { left, right } ] },
            { txnid: 7, ranges: [ { left, right }, { left, right } ] }
        ]
        requests: [
            { txnid: 15, range: { left, right }, conflicts: [ { txnid: 7, ranges: [ { left, right } ] } ] }
        ]
    }

• Given a txnid, its state in the locktree and set of lock requests.
  • Example (in json): Transaction owns two locks and is blocked by two other transactions.

    {
        txnid: 17,
        ranges: [
            { left, right },
            { left, right }
        ]
        status: "blocked"
        requested_range: { left, right }
        conflicts: [
            { txnid: 5, ranges: [ { left, right } ] }
            { txnid: 3, ranges: [ { left, right } ] }
        ]
    }

* Example (in json): Transaction owns a single lock and is not blocked.

    {
        txnid: 14
        ranges: [
            { left, right }
        ]
        status: "active"
    }

What info does InnoDB provide in the information schema?

• The state of pending requests and the transactions that block them.
  • Example (in json):

    {
        lock_id: "lock123",
        lock_trx_id: "txn123",
        lock_mode: "X", (exclusive)
        lock_type: "RECORD", (row lock, as opposed to table lock)
        lock_table: "test.table123",
        lock_index: "test.table123.column123"
        lock_space: "tablespace123",
        lock_page: 123,
        lock_rec: 456,
        lock_data: "0x2000"
    }

• Does not expose the entire state of row locks in the system.
  • Sometimes, this can be too much information.
• But it does expose some state that isn't easy to get from the locktree:
  • lock_space (tablespace sounds like a storage thing)
  • lock_rec (fractal tree leaf entry?)
  • lock_page (cachetable pair lock?)

What should we do?

• Initially, we should provide the same information InnoDB provides, minus lock_space/rec/page. This should cover most use cases.
• We should wait for user feedback before providing more than this.