源碼版本：5.7.22

一做鹰、問(wèn)題來(lái)源

這是一個(gè)朋友問(wèn)我的典型案例僧鲁。整個(gè)故障現(xiàn)象表現(xiàn)為伤柄，MySQL數(shù)據(jù)庫(kù)頻繁的出現(xiàn)大量的請(qǐng)求不能響應(yīng)并扇。下面是一些他提供的證據(jù)：

1去团、show processlist

從狀態(tài)信息來(lái)看出現(xiàn)如下情況：

• insert操作：狀態(tài)為update
• update/delete操作：狀態(tài)為updating
• select操作：狀態(tài)為sending data

因此可以推斷應(yīng)該是語(yǔ)句執(zhí)行期間出現(xiàn)了問(wèn)題，由于篇幅原因只給出一部分拜马，并且我將語(yǔ)句部分也做了相應(yīng)截?cái)啵?/p>

show processlist----------------------------
......
11827639    root    dbmis   Execute 9   updating    UPDATE 
17224594    root    dbmis   Execute 8   Sending data    SELECT sum(exchange_coin) as exchange_coin FROM 
17224595    root    dbmis   Execute 8   update  INSERT INTO 
17224596    root    dg  Execute 8   update  INSERT INTO 
17224597    root    dbmis   Execute 8   update  INSERT INTO 
17224598    root    dbmis   Execute 7   update  INSERT INTO 
17224599    root    dbmis   Execute 7   Sending data    SELECT COUNT(*) AS tp_count FROM 
17224600    root    dg  Execute 7   update  INSERT INTO 
17224601    root    dbmis   Execute 6   update  INSERT INTO 
17224602    root    dbmis   Execute 6   Sending data    SELECT sum(exchange_coin) as exchange_coin FROM 
17224606    root    dbmis   Execute 5   update  INSERT INTO 
17224619    root    dbmis   Execute 2   update  INSERT INTO 
17224620    root    dbmis   Execute 2   update  INSERT INTO 
17224621    root    dbmis   Execute 2   Sending data    SELECT sum(exchange_coin) as exchange_coin 
17224622    root    dg  Execute 2   update  INSERT INTO 
17224623    root    dbmis   Execute 1   update  INSERT INTO 
17224624    root    dbmis   Execute 1   update  INSERT INTO 
17224625    root    dg  Execute 1   update  INSERT INTO 
17224626    root    dbmis   Execute 0   update  INSERT INTO 

2渗勘、系統(tǒng)IO/CPU

從vmstat來(lái)看，CPU使用不大俩莽，而IO也在可以接受的范圍內(nèi)（vmstat wa%不高且b列為0）如下：

vmstat--------------------------------------
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 2  0 927300 3057100      0 53487316    0    0     5   192    0    0  3  1 96  0  0
iostat--------------------------------------
Linux 3.10.0-693.el7.x86_64 (fang-data1)    09/23/2019  _x86_64_    (32 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           2.72    0.00    0.52    0.45    0.00   96.31

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               9.73    11.28    3.93  264.54   415.23  2624.20    22.64     0.25    0.93    3.25    0.90   0.80  21.61
sda              10.13    11.59    6.34  264.22   450.68  2624.20    22.73     0.01    0.05    2.55    1.00   0.93  25.19
sdc              11.60    11.36    5.03  263.12   453.02  2592.44    22.71     0.17    0.62    5.08    0.53   0.81  21.60
sde               0.01     0.10    0.11  160.45     6.69   920.23    11.55     0.16    1.01    1.80    1.01   0.83  13.32
sdd              11.26    11.30    2.23  263.18   412.90  2592.44    22.65     0.17    0.65   10.37    0.56   0.82  21.78
md126             0.00     0.00   11.30  468.80   164.79  5216.64    22.42     0.00    0.00    0.00    0.00   0.00   0.00
dm-0              0.00     0.00    0.11   58.80     6.69   920.23    31.47     0.15    2.56    1.96    2.56   2.16  12.74
dm-1              0.00     0.00    0.06    0.08     0.24     0.31     8.00     0.01   41.80    1.20   72.78   0.83   0.01
dm-2              0.00     0.00   11.24  408.66   164.55  5216.33    25.63     0.14    0.32    1.02    0.30   0.46  19.29

這就比較奇怪了旺坠，一般來(lái)說(shuō)數(shù)據(jù)庫(kù)不能及時(shí)響應(yīng)請(qǐng)求很大可能是由于系統(tǒng)負(fù)載過(guò)高。如果說(shuō)DML還可能是Innodb鎖造成的堵塞扮超，但是大量sending data狀態(tài)下的select操作一般可能都和系統(tǒng)負(fù)載過(guò)高有聯(lián)系取刃，但是這里系統(tǒng)負(fù)載還在可以接受的范圍內(nèi)。

二出刷、pstack分析

借助pstack查看線程的棧幀璧疗，查看pstack發(fā)現(xiàn)如下（由于篇幅限制只給出部分說(shuō)明問(wèn)題的部分）：

1、insert 線程：

Thread 85 (Thread 0x7fbb0d42b700 (LWP 20174)):
#0  0x00007fbfae164c73 in select () from /lib64/libc.so.6
#1  0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287
#2  0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fba4802f9c8) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:276
#3  srv_conc_enter_innodb (trx=trx@entry=0x7fba4802f9c8) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511
#4  0x000000000093b948 in innobase_srv_conc_enter_innodb (trx=0x7fba4802f9c8) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280
#5  ha_innobase::write_row (this=0x7fb8440ab260, record=0x7fb8440ab650 "") at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:6793
#6  0x00000000005b440f in handler::ha_write_row (this=0x7fb8440ab260, buf=0x7fb8440ab650 "") at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:7351
#7  0x00000000006dd3a8 in write_record (thd=thd@entry=0x1d396c90, table=table@entry=0x7fb8440aa970, info=info@entry=0x7fbb0d429400, update=update@entry=0x7fbb0d429480) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_insert.cc:1667
#8  0x00000000006e2541 in mysql_insert (thd=thd@entry=0x1d396c90, table_list=<optimized out>, fields=..., values_list=..., update_fields=..., update_values=..., duplic=DUP_REPLACE, ignore=false) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_insert.cc:1072
#9  0x00000000006fa90a in mysql_execute_command (thd=thd@entry=0x1d396c90) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_parse.cc:3500

2馁龟、update線程

Thread 81 (Thread 0x7fbb24b67700 (LWP 27490)):
#0  0x00007fbfae164c73 in select () from /lib64/libc.so.6
#1  0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287
#2  0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fb94003c608) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:276
#3  srv_conc_enter_innodb (trx=trx@entry=0x7fb94003c608) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511
#4  0x000000000093ae4e in innobase_srv_conc_enter_innodb (trx=0x7fb94003c608) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280
#5  ha_innobase::index_read (this=0x7fb95c05b540, buf=0x7fb95c2ae4f0 "\377\377\377", key_ptr=<optimized out>, key_len=<optimized out>, find_flag=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:7675
#6  0x00000000005ab6e0 in ha_index_read_map (find_flag=HA_READ_KEY_EXACT, keypart_map=3, key=0x7fb940017048 "7\307\017e\257h", buf=<optimized out>, this=0x7fb95c05b540) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:2753
#7  handler::read_range_first (this=0x7fb95c05b540, start_key=<optimized out>, end_key=<optimized out>, eq_range_arg=<optimized out>, sorted=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:6717
#8  0x00000000005aa206 in handler::multi_range_read_next (this=0x7fb95c05b540, range_info=0x7fbb24b65240) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:5871
#9  0x0000000000804acb in QUICK_RANGE_SELECT::get_next (this=0x7fb94000f720) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/opt_range.cc:10644
#10 0x000000000082ae2d in rr_quick (info=0x7fbb24b65410) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/records.cc:369
#11 0x0000000000766e1b in mysql_update (thd=thd@entry=0x1d1f2250, table_list=<optimized out>, fields=..., values=..., conds=0x7fb9400009c8, order_num=<optimized out>, order=<optimized out>, limit=18446744073709551615, handle_duplicates=DUP_ERROR, ignore=false, found_return=found_return@entry=0x7fbb24b65800, updated_return=updated_return@entry=0x7fbb24b65d60) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_update.cc:744

3崩侠、select線程

Thread 66 (Thread 0x7fbb3c355700 (LWP 16028)):
#0  0x00007fbfae164c73 in select () from /lib64/libc.so.6
#1  0x0000000000987c0f in os_thread_sleep (tm=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/os/os0thread.cc:287
#2  0x00000000009e4dea in srv_conc_enter_innodb_with_atomics (trx=trx@entry=0x7fb988354858) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:276
#3  srv_conc_enter_innodb (trx=trx@entry=0x7fb988354858) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/srv/srv0conc.cc:511
#4  0x000000000093ae4e in innobase_srv_conc_enter_innodb (trx=0x7fb988354858) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:1280
#5  ha_innobase::index_read (this=0x7fb9880e33a0, buf=0x7fb988351b50 "\377\377\377\377", key_ptr=<optimized out>, key_len=<optimized out>, find_flag=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/storage/innobase/handler/ha_innodb.cc:7675
#6  0x00000000005ab6e0 in ha_index_read_map (find_flag=HA_READ_AFTER_KEY, keypart_map=7, key=0x7fb988134a48 "", buf=<optimized out>, this=0x7fb9880e33a0) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:2753
#7  handler::read_range_first (this=0x7fb9880e33a0, start_key=<optimized out>, end_key=<optimized out>, eq_range_arg=<optimized out>, sorted=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:6717
#8  0x00000000005aa206 in handler::multi_range_read_next (this=0x7fb9880e33a0, range_info=0x7fbb3c353400) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/handler.cc:5871
#9  0x0000000000804acb in QUICK_RANGE_SELECT::get_next (this=0x7fb988002050) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/opt_range.cc:10644
#10 0x000000000082ae2d in rr_quick (info=0x7fb98809c210) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/records.cc:369
#11 0x00000000006d44fd in sub_select (join=0x7fb98809a728, join_tab=0x7fb98809c180, end_of_records=<optimized out>) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:1259
#12 0x00000000006d2823 in do_select (join=0x7fb98809a728) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:936
#13 JOIN::exec (this=0x7fb98809a728) at /home/install/lnmp1.5/src/mysql-5.6.40/sql/sql_executor.cc:194

好了有了這些棧幀視乎發(fā)現(xiàn)一些共同點(diǎn)他們都處于innobase_srv_conc_enter_innodb函數(shù)下，本函數(shù)正是下面參數(shù)實(shí)現(xiàn)的方式：

• innodb_thread_concurrency
• innodb_concurrency_tickets

所以我隨即告訴他檢查這兩個(gè)參數(shù)坷檩，如果設(shè)置了可以嘗試取消却音。過(guò)后數(shù)據(jù)庫(kù)故障得到解決。

三矢炼、參數(shù)和相關(guān)說(shuō)明

實(shí)際上涉及到的參數(shù)主要是innodb_thread_concurrency和innodb_concurrency_tickets系瓢。將高壓力下線程之間搶占CPU而造成線程上下文切換的情況盡量阻塞在Innodb層之外，這就需要innodb_thread_concurrency參數(shù)了句灌。同時(shí)又要保證對(duì)于那些（長(zhǎng)時(shí)間處理線程）不會(huì)長(zhǎng)時(shí)間的堵塞（短時(shí)間處理線程）夷陋，比如某些select操作需要查詢很久，而某些select操作查詢量很小，如果等待（長(zhǎng)時(shí)間的select操作）結(jié)束后（短時(shí)間select操作）才執(zhí)行骗绕，那么顯然會(huì)出現(xiàn)（短時(shí)間select操作）饑餓問(wèn)題藐窄，換句話說(shuō)對(duì)（短時(shí)間select操作）是不公平的， 因此就引入了innodb_concurrency_tickets參數(shù)爹谭。

1枷邪、innodb_thread_concurrency

同一時(shí)刻能夠進(jìn)入Innodb層的會(huì)話（線程）數(shù)。如果在Innodb層干活的會(huì)話（線程）數(shù)量超過(guò)這個(gè)參數(shù)的設(shè)置诺凡，新會(huì)話（線程）將不能從MySQL層進(jìn)入到Innodb層东揣，它們將進(jìn)入一個(gè)短暫的睡眠狀態(tài)。休眠多久則通過(guò)參數(shù)innodb_thread_sleep_delay參數(shù)指定腹泌，如果還設(shè)置了參數(shù)innodb_adaptive_max_sleep_delay那么Innodb將會(huì)自動(dòng)調(diào)整休眠時(shí)間嘶卧，具體的算法實(shí)際上就在srv_conc_enter_innodb_with_atomics函數(shù)中，感興趣的可以執(zhí)行查看凉袱。
其次這種休眠實(shí)際上是一個(gè)定時(shí)醒來(lái)的時(shí)鐘芥吟，通過(guò)::nanosleep或者select（多路IO轉(zhuǎn)接函數(shù)）進(jìn)行實(shí)現(xiàn)，定時(shí)喚醒后會(huì)話（線程）重新判斷是否可以進(jìn)入Innodb層专甩。函數(shù)os_thread_sleep部分如下：

#elif defined(HAVE_NANOSLEEP)
    struct timespec t;

    t.tv_sec = tm / 1000000;
    t.tv_nsec = (tm % 1000000) * 1000;

    ::nanosleep(&t, NULL);
#else
    struct timeval  t;

    t.tv_sec = tm / 1000000;
    t.tv_usec = tm % 1000000;

    select(0, NULL, NULL, NULL, &t);

關(guān)于到底如何設(shè)置這個(gè)值钟鸵，官方文檔有如下建議：

Use the following guidelines to help find and maintain an appropriate setting:
- If the number of concurrent user threads for a workload is less than 64, set
innodb_thread_concurrency=0.
- If your workload is consistently heavy or occasionally spikes, start by setting
innodb_thread_concurrency=128 and then lowering the value to 96, 80, 64, and so on, until
you find the number of threads that provides the best performance. For example, suppose your
system typically has 40 to 50 users, but periodically the number increases to 60, 70, or even 200.
You find that performance is stable at 80 concurrent users but starts to show a regression above
this number. In this case, you would set innodb_thread_concurrency=80 to avoid impacting
performance.
- If you do not want InnoDB to use more than a certain number of virtual CPUs for user threads
(20 virtual CPUs, for example), set innodb_thread_concurrency to this number (or possibly
lower, depending on performance results). If your goal is to isolate MySQL from other applications,
you may consider binding the mysqld process exclusively to the virtual CPUs. Be aware,
however, that exclusive binding could result in non-optimal hardware usage if the mysqld process
is not consistently busy. In this case, you might bind the mysqld process to the virtual CPUs but
also allow other applications to use some or all of the virtual CPUs.
- innodb_thread_concurrency values that are too high can cause performance regression due
to increased contention on system internals and resources.
- In some cases, the optimal innodb_thread_concurrency setting can be smaller than the
number of virtual CPUs.
- Monitor and analyze your system regularly. Changes to workload, number of users, or computing
environment may require that you adjust the innodb_thread_concurrency setting

可以發(fā)現(xiàn)要合理的設(shè)置這個(gè)值并不那么容易并且要求較高。

2涤躲、innodb_concurrency_tickets

實(shí)際上這里的tickets可以理解為MySQL層和Innodb層交互的次數(shù)棺耍，比如一個(gè)select一條數(shù)據(jù)就是需要Innodb層返回一條數(shù)據(jù)然后MySQL層進(jìn)行where條件的過(guò)濾然后返回給客戶端，拋開(kāi)where條件過(guò)濾的情況种樱，如果我們一條語(yǔ)句需要查詢100條數(shù)據(jù)蒙袍，那么實(shí)際上需要進(jìn)入Innodb層100次，那么實(shí)際上消耗的tickets就是100嫩挤。當(dāng)然對(duì)于insert select這種操作害幅，需要的tickets是普通select的兩倍，因?yàn)椴樵冃枰M(jìn)入Innodb層一次岂昭，insert需要再次進(jìn)入Innodb層一次以现，后面我們就使用insert select的方式來(lái)模擬堵塞的情況，最后還會(huì)給出說(shuō)明约啊。

這樣我們也就理解為什么innodb_concurrency_tickets可以避免（長(zhǎng)時(shí)間處理線程）長(zhǎng)時(shí)間堵塞（短時(shí)間處理線程）的原因了邑遏。假設(shè)innodb_concurrency_tickets為5000（默認(rèn)值），有一個(gè)需要查詢100W行數(shù)據(jù)的大select操作和一個(gè)需要查詢100行數(shù)據(jù)的小select操作棍苹，大select操作先進(jìn)行无宿，但是當(dāng)查詢了5000行數(shù)據(jù)后將丟失CPU使用權(quán)茵汰，小select操作將會(huì)進(jìn)行并且一次性完成枢里。

最后關(guān)于這里涉及的參數(shù)可以繼續(xù)參考官方文檔中的說(shuō)明，我們線上并沒(méi)有設(shè)置這些參數(shù)，因?yàn)楦杏X(jué)很難設(shè)置合適栏豺，如果設(shè)置不當(dāng)反而會(huì)遇到問(wèn)題彬碱，就如本案例一樣。

3奥洼、事務(wù)操作狀態(tài)

實(shí)際上如果是處于這種堵塞情況巷疼，我們完全可以在information_schema.innodb_trx和show engine innodb status中看到如下：

---TRANSACTION 162307, ACTIVE 133 sec sleeping before entering InnoDB （這里）         
mysql tables in use 2, locked 2
767 lock struct(s), heap size 106968, 212591 row lock(s), undo log entries 15451
MySQL thread id 14, OS thread handle 140736751912704, query id 1077 localhost root Sending data
insert into testui select * from testui
---TRANSACTION 162302, ACTIVE 320 sec, thread declared inside InnoDB 1
mysql tables in use 2, locked 2
2477 lock struct(s), heap size 336344, 609049 row lock(s), undo log entries 83582
MySQL thread id 13, OS thread handle 140737153779456, query id 1050 localhost root Sending data
insert into testti3 select * from testti3


mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G
*************************** 1. row ***************************
                 trx_id: 84325
              trx_state: RUNNING
              trx_query: insert into  baguait4 select * from testgp
    trx_operation_state: sleeping before entering InnoDB（這里）
trx_concurrency_tickets: 0
*************************** 2. row ***************************
                 trx_id: 84319
              trx_state: RUNNING
              trx_query: insert into  baguait3 select * from testgp
    trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0

我們可以看到事務(wù)操作狀態(tài)被標(biāo)記為‘sleeping before entering InnoDB’。但是需要注意一點(diǎn)的是對(duì)于只讀事務(wù)比如select操作而言灵奖，show engine innodb status可能看不到嚼沿。但是遺憾的是案例中朋友并沒(méi)有采集trx_operation_state的值。

四瓷患、模擬測(cè)試

這里我們簡(jiǎn)單模擬骡尽，我們一共啟用3個(gè)事務(wù)，其中兩個(gè)insert select操作擅编，一個(gè)單純的select操作攀细，當(dāng)然這里的都是耗時(shí)操作，涉及的表每個(gè)表都有大概100W的數(shù)據(jù)爱态。

同時(shí)為了方便觀察我們需要設(shè)置參數(shù)：

• innodb_thread_concurrency=1
• innodb_concurrency_tickets=10

操作步驟如下：

如果多觀察幾次你可以看到如下的現(xiàn)象：

mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G show processlist;
*************************** 1. row ***************************
                 trx_id: 84529
              trx_state: RUNNING
              trx_query: insert into  baguait4 select * from testgp
    trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
*************************** 2. row ***************************
                 trx_id: 84524
              trx_state: RUNNING
              trx_query: insert into  baguait3 select * from testgp
    trx_operation_state: inserting
trx_concurrency_tickets: 1
*************************** 3. row ***************************
                 trx_id: 422211785606640
              trx_state: RUNNING
              trx_query: select * from baguait1
    trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
3 rows in set (0.00 sec)

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                       | Rows_sent | Rows_examined |
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
|  1 | event_scheduler | localhost | NULL    | Daemon  | 3173 | Waiting on empty queue | NULL                                       |         0 |             0 |
|  6 | root            | localhost | testmts | Query   |   70 | Sending data           | insert into  baguait3 select * from testgp |         0 |             0 |
|  7 | root            | localhost | testmts | Query   |   68 | Sending data           | insert into  baguait4 select * from testgp |         0 |             0 |
|  8 | root            | localhost | testmts | Query   |   66 | Sending data           | select * from baguait1                     |    120835 |             0 |
|  9 | root            | localhost | NULL    | Query   |    0 | starting               | show processlist                           |         0 |             0 |
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
5 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 
mysql> 
mysql> select trx_id,trx_state,trx_query,trx_operation_state,trx_concurrency_tickets from information_schema.innodb_trx \G show processlist;
*************************** 1. row ***************************
                 trx_id: 84529
              trx_state: RUNNING
              trx_query: insert into  baguait4 select * from testgp
    trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
*************************** 2. row ***************************
                 trx_id: 84524
              trx_state: RUNNING
              trx_query: insert into  baguait3 select * from testgp
    trx_operation_state: sleeping before entering InnoDB
trx_concurrency_tickets: 0
*************************** 3. row ***************************
                 trx_id: 422211785606640
              trx_state: RUNNING
              trx_query: select * from baguait1
    trx_operation_state: fetching rows
trx_concurrency_tickets: 3
3 rows in set (0.00 sec)

+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
| Id | User            | Host      | db      | Command | Time | State                  | Info                                       | Rows_sent | Rows_examined |
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
|  1 | event_scheduler | localhost | NULL    | Daemon  | 3177 | Waiting on empty queue | NULL                                       |         0 |             0 |
|  6 | root            | localhost | testmts | Query   |   74 | Sending data           | insert into  baguait3 select * from testgp |         0 |             0 |
|  7 | root            | localhost | testmts | Query   |   72 | Sending data           | insert into  baguait4 select * from testgp |         0 |             0 |
|  8 | root            | localhost | testmts | Query   |   70 | Sending data           | select * from baguait1                     |    128718 |             0 |
|  9 | root            | localhost | NULL    | Query   |    0 | starting               | show processlist                           |         0 |             0 |
+----+-----------------+-----------+---------+---------+------+------------------------+--------------------------------------------+-----------+---------------+
5 rows in set (0.00 sec)

我們可以觀察到trx_operation_state的狀態(tài)3個(gè)操作都在交替的變化谭贪，但是總有2個(gè)處于‘sleeping before entering InnoDB’狀態(tài)。并且我們可以觀察到trx_concurrency_tickets總是不會(huì)大于10的锦担。因此我們有理由相信在同一時(shí)刻只有一個(gè)操作進(jìn)入了Innodb層俭识。但是需要注意的是在show engine innodb status中觀察不到select的操作如下：

------------
TRANSACTIONS
------------
Trx id counter 84538
Purge done for trx's n:o < 84526 undo n:o < 0 state: running but idle
History list length 356
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422211785609424, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 422211785608032, not started
0 lock struct(s), heap size 1160, 0 row lock(s)
---TRANSACTION 84529, ACTIVE 103 sec inserting, thread declared inside InnoDB 6
mysql tables in use 2, locked 1
1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 111866
MySQL thread id 7, OS thread handle 140737158833920, query id 80 localhost root Sending data
insert into  baguait4 select * from testgp
Trx read view will not see trx with id >= 84529, sees < 84524
---TRANSACTION 84524, ACTIVE 105 sec sleeping before entering InnoDB
mysql tables in use 2, locked 1
1 lock struct(s), heap size 1160, 0 row lock(s), undo log entries 105605
MySQL thread id 6, OS thread handle 140737159034624, query id 79 localhost root Sending data
insert into  baguait3 select * from testgp
Trx read view will not see trx with id >= 84524, sees < 84524

但是我們還需要注意show engine innodb status有如下輸出第一行說(shuō)明了有2個(gè)會(huì)話（線程）堵塞在Innodb層以外。

--------------
ROW OPERATIONS
--------------
1 queries inside InnoDB, 2 queries in queue
3 read views open inside InnoDB
2 RW transactions active inside InnoDB

五吆豹、實(shí)現(xiàn)方法

前面我們已經(jīng)描述了每次MySQL層和Innodb層的交互都會(huì)進(jìn)行一次這樣的判斷鱼的，它用來(lái)決定會(huì)話（線程）是否能夠進(jìn)入Innodb層，下面就是大概的邏輯痘煤，由函數(shù)innobase_srv_conc_enter_innodb調(diào)入凑阶。

->是否設(shè)置了參數(shù)innodb_thread_concurrency
  ->是
     ->是否tickets大于0
        ->是、直接進(jìn)入Innodb層并且tickets減1
        ->否衷快、調(diào)入函數(shù)srv_conc_enter_innodb
           ->調(diào)入函數(shù)srv_conc_enter_innodb_with_atomics
              ->開(kāi)啟死循環(huán)
                 ->是否活躍線程數(shù)小于innodb_thread_concurrency設(shè)置
                    ->是宙橱、增加活躍線程數(shù)，并且自動(dòng)調(diào)整delay參數(shù)蘸拔，退出死循環(huán)师郑，滿tickets進(jìn)入Innodb層
                    ->否、自動(dòng)調(diào)整delay參數(shù)后設(shè)置事務(wù)操作狀態(tài)為"sleeping before entering InnoDB"调窍，然后進(jìn)入休眠狀態(tài)直到時(shí)間達(dá)到后重新醒來(lái)繼續(xù)循環(huán)
  ->否宝冕、直接進(jìn)入Innodb層

我們可以看到這個(gè)實(shí)現(xiàn)方式，在Inndob以外的會(huì)話（線程）會(huì)一直等待直到Inndob層內(nèi)活躍的線程數(shù)小于innodb_thread_concurrency為止邓萨，并且每次進(jìn)入Innodb層都會(huì)將tickets減1地梨。

其他：關(guān)于insert select操作消耗tickets的說(shuō)明

這里額外說(shuō)明一下菊卷，因?yàn)槲以跍y(cè)試的時(shí)候看了一下，對(duì)于一行數(shù)據(jù)而言首先需要select查詢出來(lái)然后再insert插入到表中宝剖，這里實(shí)際上一行數(shù)據(jù)涉及到進(jìn)入Innodb層兩次洁闰，那么就需要消耗2個(gè)tickets，下面留下兩個(gè)棧幀供自己后面參考：

1万细、insert select查詢數(shù)據(jù)進(jìn)入Innodb層

#0  innobase_srv_conc_enter_innodb (prebuilt=0x7ffedcb98d10) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:1740
#1  0x0000000001a53f7c in ha_innobase::general_fetch (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n", direction=1, match_mode=0)
    at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:9846
#2  0x0000000001a545ee in ha_innobase::rnd_next (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n")
    at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:10083
#3  0x0000000000f836d6 in handler::ha_rnd_next (this=0x7ffedcb9d760, buf=0x7ffedc9469b0 "\375\n") at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:3146
#4  0x00000000014e2a55 in rr_sequential (info=0x7ffedcb4f120) at /mysqldata/percona-server-locks-detail-5.7.22/sql/records.cc:521
#5  0x0000000001581277 in sub_select (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0, end_of_records=false)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1280
#6  0x0000000001580be6 in do_select (join=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950
#7  0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199
#8  0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc46680, added_options=1342177280, removed_options=0)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185
#9  0x000000000180466d in Sql_cmd_insert_select::execute (this=0x7ffedcc46608, thd=0x7ffedc012960)

2扑眉、insert select插入數(shù)據(jù)進(jìn)入Innodb層

#0  innobase_srv_conc_enter_innodb (prebuilt=0x7ffedcb9c6f0) at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:1740
#1  0x0000000001a50587 in ha_innobase::write_row (this=0x7ffedc946470, record=0x7ffedcb78d00 "\375\n")
    at /mysqldata/percona-server-locks-detail-5.7.22/storage/innobase/handler/ha_innodb.cc:8341
#2  0x0000000000f9041d in handler::ha_write_row (this=0x7ffedc946470, buf=0x7ffedcb78d00 "\375\n") at /mysqldata/percona-server-locks-detail-5.7.22/sql/handler.cc:8466
#3  0x00000000018004b9 in write_record (thd=0x7ffedc012960, table=0x7ffedcb8f940, info=0x7ffedcc466c8, update=0x7ffedcc46740)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:1881
#4  0x00000000018019b9 in Query_result_insert::send_data (this=0x7ffedcc46680, values=...) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:2279
#5  0x00000000015853a8 in end_send (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f248, end_of_records=false)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925
#6  0x0000000001581f71 in evaluate_join_record (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1645
#7  0x0000000001581372 in sub_select (join=0x7ffedcb4ea20, qep_tab=0x7ffedcb4f0d0, end_of_records=false)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297
#8  0x0000000001580be6 in do_select (join=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950
#9  0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4ea20) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199
#10 0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc46680, added_options=1342177280, removed_options=0)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185
#11 0x000000000180466d in Sql_cmd_insert_select::execute (this=0x7ffedcc46608, thd=0x7ffedc012960)

實(shí)際上插入數(shù)據(jù)正是在查詢完數(shù)據(jù)后調(diào)用函數(shù)evaluate_join_record的時(shí)候，通過(guò)回調(diào)了函數(shù)Query_result_insert::send_data來(lái)實(shí)現(xiàn)赖钞，這點(diǎn)和單純的select不一樣單純的select這里調(diào)入是函數(shù)Query_result_send::send_data如下：

#0  Query_result_send::send_data (this=0x7ffedcc465f8, items=...) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_class.cc:2915
#1  0x00000000015853a8 in end_send (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f4b0, end_of_records=false)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:2925
#2  0x0000000001581f71 in evaluate_join_record (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f338) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1645
#3  0x0000000001581372 in sub_select (join=0x7ffedcb4e930, qep_tab=0x7ffedcb4f338, end_of_records=false)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297
#4  0x0000000001580be6 in do_select (join=0x7ffedcb4e930) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950
#5  0x000000000157eaa2 in JOIN::exec (this=0x7ffedcb4e930) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199
#6  0x0000000001620327 in handle_query (thd=0x7ffedc012960, lex=0x7ffedc014f90, result=0x7ffedcc465f8, added_options=0, removed_options=0)
    at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185
#7  0x00000000015d1f77 in execute_sqlcom_select (thd=0x7ffedc012960, all_tables=0x7ffedcc45cf0) at /mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5445