MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
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.
Prior to MySQL 8.0.20, InnoDB also uses a
First In First Out (FIFO) algorithm to schedule transactions,
and the CATS algorithm is used under heavy lock contention only.
CATS algorithm enhancements in MySQL 8.0.20 rendered the FIFO
algorithm redundant, permitting its removal. Transaction
scheduling previously performed by the FIFO algorithm is
performed by the CATS algorithm as of MySQL 8.0.20. In some
cases, this change may affect the order in which transactions
are granted locks.
You can view transaction scheduling weights by querying the
TRX_SCHEDULE_WEIGHT column in the Information
Schema 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
TRX_SCHEDULE_WEIGHT value.
INNODB_METRICS counters are provided
for monitoring of code-level transaction scheduling events. For
information about using
INNODB_METRICS counters, see
Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.
lock_rec_release_attempts
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.
lock_rec_grant_attempts
The number of attempts to grant record locks. A single attempt may result in zero or more record locks being granted.
lock_schedule_refreshes
The number of times the wait-for graph was analyzed to update the scheduled transaction weights.