Functions and improvements of innodb_thread_concurrency in mysql

Innodb_thread_concurrency
If the parameter value is greater than 0, the check mechanism is enabled. The number of threads allowed to enter is the value of the parameter.
Before the new MySQL thread calls the Innodb interface, Innodb checks the number of accepted request threads;
If innodb_thread_concurrency is exceeded, the request thread waits for innodb_thread_sleep_delay microseconds and then tries to request again. If the second request still cannot be obtained, the thread goes to the FIFO queue to sleep. The mechanism of two retries is to reduce the number of context switches of the CPU and reduce CPU consumption.
If the request is accepted by Innodb, a pass with the number of innodb_concurrency_tickets (500 by default) is obtained. Before the number of requests is used up, the thread does not need to re-request the request.
-- The code structure is as follows:
If (thread-> n_tickets_to_enter_innodb> 0)
  {
Thread-> n_tickets_to_enter_innodb --;
ENTER;
  }
Retry:
If (entered_thread <innodb_thread_concurrency)
  {
Entered_threads ++;
Thread-> n_tickets_to_enter_innodb = innodb_concurrency_tickets;
ENTER;
  }
If (innodb_thread_sleep_delay> 0)
  {
Thread_sleep (innodb_thread_sleep_delay );
  }
Goto retry; // (only once)
Wait_in_1_o_queue;
Thread-> n_tickets_to_enter_innodb = innodb_concurrency_tickets;
ENTER;
The source file is srv_conc_enter_innodb (innobase/srv/srv0srv ).
If it is a thread that already has a lock, you can ignore this check by calling the srv_conc_force_enter_innodb function. This is to prevent the thread from holding the lock for a long time and may increase the chance of deadlock. In addition, the slave thread has the permission to ignore the direct access check;
Disadvantages: OS _event_t is used to manage the teams and queues. Although it can prevent hunger and waiting, the management overhead is relatively large ,;
Improvement: busy polling is used to obtain slots. If slots are not obtained, sleep waits. To reduce queuing overhead, hunger waits may be caused;
Add parameters
Innodb_adaptive_sleep_delay: Boolean type, used to activate or disable adaptive sleep delay
Innodb_adaptive_max_sleep_delay: defines the maximum sleep delay in microseconds.
The adaptive algorithm is:
If you still cannot enter the sleep after the current value, set the sleep time to + 1.
If there are idle threads in sleep, the sleep time of the current value will be halved.
Innodb_thread_concurrency limits the row concurrency, but the innodb structure and lock contention are very serious during the commit phase. Innodb_commit_concurrency introduced by MySQL from 5.0. This parameter sets the number of threads that can be simultaneously committed at the same time. The default value is 0, which is unlimited. The value range is 0-1000 and cannot be modified dynamically.