TXSQL supports the Contention-Aware Transaction Scheduling (CATS) algorithm. This new algorithm automatically detects lock contention between transactions and schedules them based on their scheduling weights.
MySQL supports another transaction scheduling algorithm, aka First In First Out (FIFO), which was introduced earlier than CATS. When multiple transactions are waiting for the same lock, CATS prioritizes them by assigning a scheduling weight which is computed based on the number of transactions that a transaction blocks. The transaction with a higher scheduling weight will be executed sooner. Thus, transaction throughput is improved.
This feature is suitable for use cases under high concurrency and heavy lock contention.
TPS is improved by more than 50% under high concurrency and heavy lock contention.
|Thread Count||FCFS (FIFO)||CATS||Performance Improvement|
In MySQL 5.7, you can use the global parameter
innodb_trx_schedule_algorithm to specify the transaction scheduling algorithm. The default value is
|Parameter||Effective Immediately||Type||Default Value||Valid Values/Value Range||Description|
|innodb_trx_schedule_algorithm||Yes||string||auto||[auto,fcfs,cats]||Specify the transaction scheduling algorithm|
Currently, you cannot directly modify the values of the above parameters. If needed, submit a ticket for assistance.
In MySQL 8.0,
auto is the only valid value.