limit the maximum number of cached txns in mysql worker

[CharlesCheung96]

Is your feature request related to a problem?

ref sink to mysql (cdc) workload skew issue

PR #10376 tries to fix the skew problem by sending a transaction to a random worker after the depended transactions are executed. For conflicting transactions, only one transaction can be executed among all workers at a time, which can also be called serial execution or one by one. During synchronous real-time streaming, conflicting transactions are executed serially in the upstream cluster, so it is a reasonable choice for TiCDC to execute these transactions serially.

Txn N(row1)... ------> Txn C(row1)------> Txn B(row1)------> Txn A(row1) 
                                                                    |
                                                                    |-----> worker 1

However, for other common scenarios, this approach can be problematic:

1. Incremental Scan: during the synchronization of historical data, if conflicting transactions are consistently executed in the upstream cluster. Then the MySQL sink needs at least twice the serial throughput to catch up with the latency. However, the serial execution never satisfies this condition.

Incremental Scan: Txn N(row1)... ------> Txn C(row1)------> Txn B(row1)------> Txn A(row1) 
                                                                                       |
                                                                                       |-----> worker 1
                                                                                       | 
Real-time streaming: Txn N(row1)... ------> Txn C(row1)------> Txn B(row1)------> Txn A(row1) 

2. Cross regional replication: in this scenario, the throughput of a single MySQL worker is limited by network latency, so the throughput of executing transactions one by one may be much smaller than that of the upstream cluster.

New Proposal

It is better to use a compromise optimization that replaces one by one with batch by batch:

1. The batch mechanism can effectively improve the throughput of a single worker, so it is necessary to preserve the fast dependencies resolving optimization in conflict detector.

2. At the same time, to avoid workload skew problems, we could limit the maximum number of cached txns in single worker. When the limit is exceeded, the conflict detector should wait for all transactions cached in the worker to complete before sending a new event to it.