InnoDB uses the Contention-Aware Transaction
Scheduling (CATS) algorithm to prioritize transactions that are
waiting for locks. When multiple transactions are waiting for a
lock on the same object, the CATS algorithm determines which
transaction receives the lock first.
The CATS algorithm prioritizes waiting transactions by assigning a scheduling weight, which is computed based on the number of transactions that a transaction blocks. For example, if two transactions are waiting for a lock on the same object, the transaction that blocks the most transactions is assigned a greater scheduling weight. If weights are equal, priority is given to the longest waiting transaction.
You can view transaction scheduling weights by querying the
TRX_SCHEDULE_WEIGHT column in the Information
INNODB_TRX table. Weights are
computed for waiting transactions only. Waiting transactions are
those in a
LOCK WAIT transaction execution
state, as reported by the
TRX_STATE column. A
transaction that is not waiting for a lock reports a NULL
INNODB_METRICS counters are provided
for monitoring of code-level transaction scheduling events. For
information about using
INNODB_METRICS counters, see
Section 15.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.
The number of attempts to release record locks. A single attempt may lead to zero or more record locks being released, as there may be zero or more record locks in a single structure.
The number of attempts to grant record locks. A single attempt may result in zero or more record locks being granted.
The number of times the wait-for graph was analyzed to update the scheduled transaction weights.