Analyzing MySQL InnoDB Thread Concurrency Issues and Their Resolution

The author received a typical case from a friend: MySQL frequently failed to respond to requests, with many queries stuck in states such as update, updating, and sending data. The initial evidence included a truncated SHOW PROCESSLIST output showing numerous INSERT, UPDATE, and SELECT statements in those states.

System metrics from vmstat and iostat indicated low CPU usage and acceptable I/O, suggesting that the bottleneck was not caused by overall system load.

Using pstack, the author captured stack traces for INSERT, UPDATE, and SELECT threads. All traces shared a common entry point in the InnoDB function innobase_srv_conc_enter_innodb, which is governed by the parameters innodb_thread_concurrency and innodb_concurrency_tickets.

The analysis concluded that these two parameters control how many sessions may enter the InnoDB engine simultaneously and how many "tickets" each session consumes. Setting innodb_thread_concurrency too low forces sessions to sleep before entering InnoDB, while innodb_concurrency_tickets limits the number of operations a long‑running transaction can perform before yielding to shorter queries.

Official MySQL documentation recommends:

innodb_thread_concurrency=0          # when concurrent users < 64
innodb_thread_concurrency=128 ...   # then tune down until performance stabilises

In the simulated test, three transactions were run: two long‑running INSERT SELECT operations and one simple SELECT. The parameters were set to innodb_thread_concurrency=1 and innodb_concurrency_tickets=10. Observations from information_schema.innodb_trx and SHOW PROCESSLIST showed that only one transaction entered InnoDB at a time, while the others were in the "sleeping before entering InnoDB" state, confirming the intended throttling behavior.

The implementation flow inside MySQL is roughly:

if (innodb_thread_concurrency is set) {
    if (tickets > 0) {
        // consume a ticket and enter InnoDB
    } else {
        // call srv_conc_enter_innodb_with_atomics()
        // loop until active threads < innodb_thread_concurrency
        // then sleep and retry
    }
} else {
    // enter InnoDB directly
}

For INSERT SELECT statements, each row requires two entries into InnoDB (one for the SELECT, one for the INSERT), effectively consuming two tickets per row.

The article concludes with a recommendation to adjust innodb_thread_concurrency and innodb_concurrency_tickets carefully, as improper settings can cause performance regressions, and points readers to the author's book "Deep Understanding of MySQL Master‑Slave Principles" for further study.