自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

有效解決 MySQL 行鎖等待超時(shí)問(wèn)題

作者：楊建榮的學(xué)習(xí)筆記
數(shù)據(jù)庫(kù) MySQL
本文僅介紹如何有效解決行鎖等待超時(shí)，因?yàn)榇蠖鄶?shù)項(xiàng)目都是此類錯(cuò)誤，元數(shù)據(jù)鎖等待超時(shí)則不涉及講解。

 [[323417]]

一、背景

 

  1. #### 20191219 10:10:10,234 | com.alibaba.druid.filter.logging.Log4jFilter.statementLogError(Log4jFilter.java:152) | ERROR |  {conn-10593, pstmt-38675} execute error. update operation_service set offlinemark = ? , resourcestatus = ?  where RowGuid = ? 
  2.  
  3. com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction 

上述這個(gè)錯(cuò)誤，接觸 MySQL 的同學(xué)或多或少應(yīng)該都遇到過(guò)，專業(yè)一點(diǎn)來(lái)說(shuō)，這個(gè)報(bào)錯(cuò)我們稱之為鎖等待超時(shí)。根據(jù)鎖的類型主要細(xì)分為：

  • 行鎖等待超時(shí)

當(dāng) SQL 因?yàn)榈却墟i而超時(shí)，那么就為行鎖等待超時(shí)，常在多并發(fā)事務(wù)場(chǎng)景下出現(xiàn)。

  • 元數(shù)據(jù)鎖等待超時(shí)

當(dāng) SQL 因?yàn)榈却獢?shù)據(jù)鎖而超時(shí)，那么就為元數(shù)據(jù)鎖等待超時(shí)，常在 DDL 操作期間出現(xiàn)。

本文僅介紹如何有效解決行鎖等待超時(shí)，因?yàn)榇蠖鄶?shù)項(xiàng)目都是此類錯(cuò)誤，元數(shù)據(jù)鎖等待超時(shí)則不涉及講解。

二、行鎖的等待

在介紹如何解決行鎖等待問(wèn)題前，先簡(jiǎn)單介紹下這類問(wèn)題產(chǎn)生的原因。產(chǎn)生原因簡(jiǎn)述：當(dāng)多個(gè)事務(wù)同時(shí)去操作(增刪改)某一行數(shù)據(jù)的時(shí)候，MySQL 為了維護(hù) ACID 特性，就會(huì)用鎖的形式來(lái)防止多個(gè)事務(wù)同時(shí)操作某一行數(shù)據(jù)，避免數(shù)據(jù)不一致。只有分配到行鎖的事務(wù)才有權(quán)力操作該數(shù)據(jù)行，直到該事務(wù)結(jié)束，才釋放行鎖，而其他沒(méi)有分配到行鎖的事務(wù)就會(huì)產(chǎn)生行鎖等待。如果等待時(shí)間超過(guò)了配置值(也就是 innodb_lock_wait_timeout 參數(shù)的值，個(gè)人習(xí)慣配置成 5s，MySQL 官方默認(rèn)為 50s)，則會(huì)拋出行鎖等待超時(shí)錯(cuò)誤。

 

如上圖所示，事務(wù) A 與事務(wù) B 同時(shí)會(huì)去 Insert 一條主鍵值為 1 的數(shù)據(jù)，由于事務(wù) A 首先獲取了主鍵值為 1 的行鎖，導(dǎo)致事務(wù) B 因無(wú)法獲取行鎖而產(chǎn)生等待，等到事務(wù) A 提交后，事務(wù) B 才獲取該行鎖，完成提交。這里強(qiáng)調(diào)的是行鎖的概念，雖然事務(wù) B 重復(fù)插入了主鍵，但是在獲取行鎖之前，事務(wù)一直是處于行鎖等待的狀態(tài)，只有獲取行鎖后，才會(huì)報(bào)主鍵沖突的錯(cuò)誤。當(dāng)然這種 Insert 行鎖沖突的問(wèn)題比較少見(jiàn)，只有在大量并發(fā)插入場(chǎng)景下才會(huì)出現(xiàn)，項(xiàng)目上真正常見(jiàn)的是 update&delete 之間行鎖等待，這里只是用于示例，原理都是相同的。

三、產(chǎn)生的原因

根據(jù)我之前接觸到的此類問(wèn)題，大致可以分為以下幾種原因：

1. 程序中非數(shù)據(jù)庫(kù)交互操作導(dǎo)致事務(wù)掛起

將接口調(diào)用或者文件操作等這一類非數(shù)據(jù)庫(kù)交互操作嵌入在 SQL 事務(wù)代碼之中，那么整個(gè)事務(wù)很有可能因此掛起(接口不通等待超時(shí)或是上傳下載大附件)。

2. 事務(wù)中包含性能較差的查詢SQL

事務(wù)中存在慢查詢，導(dǎo)致同一個(gè)事務(wù)中的其他 DML 無(wú)法及時(shí)釋放占用的行鎖，引起行鎖等待。

3. 單個(gè)事務(wù)中包含大量 SQL

通常是由于在事務(wù)代碼中加入 for 循環(huán)導(dǎo)致，雖然單個(gè) SQL 運(yùn)行很快，但是 SQL 數(shù)量一大，事務(wù)就會(huì)很慢。

4. 級(jí)聯(lián)更新 SQL 執(zhí)行時(shí)間較久

這類 SQL 容易讓人產(chǎn)生錯(cuò)覺(jué)，例如：update A set ... where ...in (select B) 這類級(jí)聯(lián)更新，不僅會(huì)占用 A 表上的行鎖，也會(huì)占用 B 表上的行鎖，當(dāng) SQL 執(zhí)行較久時(shí)，很容易引起 B 表上的行鎖等待。

5. 磁盤(pán)問(wèn)題導(dǎo)致的事務(wù)掛起

極少出現(xiàn)的情形，比如存儲(chǔ)突然離線，SQL 執(zhí)行會(huì)卡在內(nèi)核調(diào)用磁盤(pán)的步驟上，一直等待，事務(wù)無(wú)法提交。綜上可以看出，如果事務(wù)長(zhǎng)時(shí)間未提交，且事務(wù)中包含了 DML 操作，那么就有可能產(chǎn)生行鎖等待，引起報(bào)錯(cuò)。

四、定位難點(diǎn)當(dāng)

web 日志中出現(xiàn)行鎖超時(shí)錯(cuò)誤后，很多開(kāi)發(fā)都會(huì)找我來(lái)排查問(wèn)題，這里說(shuō)下問(wèn)題定位的難點(diǎn)!

1. MySQL 本身不會(huì)主動(dòng)記錄行鎖等待的相關(guān)信息，所以無(wú)法有效的進(jìn)行事后分析。

2. 鎖爭(zhēng)用原因有多種，很難在事后判斷到底是哪一類問(wèn)題場(chǎng)景，尤其是事后無(wú)法復(fù)現(xiàn)問(wèn)題的時(shí)候。

3. 找到問(wèn)題 SQL 后，開(kāi)發(fā)無(wú)法有效從代碼中挖掘出完整的事務(wù)，這也和公司框架-產(chǎn)品-項(xiàng)目的架構(gòu)有關(guān)，需要靠 DBA 事后采集完整的事務(wù) SQL 才可以進(jìn)行分析。

五、常用方法

先介紹下個(gè)人通常是如何解決此類問(wèn)題的， 這里問(wèn)題解決的前提是問(wèn)題可以復(fù)現(xiàn)，只要不是突然出現(xiàn)一次，之后再也不出現(xiàn)，一般都是可以找到問(wèn)題源頭的。

這里問(wèn)題復(fù)現(xiàn)分為兩種情景：

1. 手動(dòng)復(fù)現(xiàn)

只要按照一定的操作，就可以復(fù)現(xiàn)報(bào)錯(cuò)，這種場(chǎng)景較簡(jiǎn)單!

2. 隨機(jī)復(fù)現(xiàn)

不知道何時(shí)會(huì)突然報(bào)錯(cuò)，無(wú)法手動(dòng)復(fù)現(xiàn)，這種場(chǎng)景較難!

下面先寫(xiě)下統(tǒng)一的模擬場(chǎng)景，用于復(fù)現(xiàn)行鎖超時(shí)問(wèn)題，便于大家理解：

 

  1. --表結(jié)構(gòu) 
  2.  
  3. CREATE TABLE `emp` ( 
  4.  
  5.   `id` int(11) NOT NULL, 
  6.  
  7.   KEY `idx_id` (`id`) 
  8.  
  9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 
  10.  
  11.  
  12.  
  13. 從1~100w插入100w行記錄。 
  14.  
  15.  
  16.  
  17. --測(cè)試過(guò)程： 
  18.  
  19. 事務(wù)1： 
  20.  
  21. start transaction; 
  22.  
  23. delete from emp where id = 1; 
  24.  
  25. select * from emp where id in (select id from emp);   -->模擬慢查詢，執(zhí)行時(shí)間很久，事務(wù)因此一直不提交，行鎖也不釋放. 
  26.  
  27. commit; 
  28.  
  29.  
  30.  
  31. 事務(wù)2： 
  32.  
  33. start transaction; 
  34.  
  35. delete from emp where id < 10;   --> 處于等待id=1的行鎖狀態(tài)，當(dāng)達(dá)到行鎖超時(shí)時(shí)間（這里我配置了超時(shí)時(shí)間為 5s）后，返回行鎖超時(shí)報(bào)錯(cuò) 
  36.  
  37. rollback; 

5.1 手動(dòng)復(fù)現(xiàn)場(chǎng)景

這個(gè)場(chǎng)景通常只需要通過(guò) innodb 行鎖等待腳本就可以知道當(dāng)前 MySQL 的 innodb 行鎖等待情況，例如我們一邊模擬上述報(bào)錯(cuò)場(chǎng)景(模擬頁(yè)面操作)，另一邊使用腳本查詢(需要在超時(shí)之前查詢，否則超時(shí)報(bào)錯(cuò)后就看不到了)。

 

  1. /*innodb 行鎖等待腳本*/ 
  2.  
  3. SELECT r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query, 
  4.  
  5. concat(timestampdiff(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP()),'s') AS duration, 
  6.  
  7. b.trx_mysql_thread_id blocking_thread,t.processlist_command state,b.trx_query blocking_current_query,e.sql_text blocking_last_query 
  8.  
  9. FROM information_schema.innodb_lock_waits w 
  10.  
  11. JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id 
  12.  
  13. JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id 
  14.  
  15. JOIN performance_schema.threads t on t.processlist_id = b.trx_mysql_thread_id 
  16.  
  17. JOIN performance_schema.events_statements_current e USING(thread_id) 

 


如上我們可以看到事務(wù) 2 的線程 id 為 76，已經(jīng)被事務(wù) 1，也就是線程 id 為 75 的事務(wù)阻塞了 3s，并且可以看到事務(wù) 1 當(dāng)前執(zhí)行的 SQL 為一個(gè) SELECT。這里也解釋了很多開(kāi)發(fā)經(jīng)常問(wèn)我的，為什么 SELECT 也會(huì)阻塞其他會(huì)話?如果遇到這種情況，那么處理其實(shí)非常簡(jiǎn)單。需要優(yōu)化這個(gè) SELECT 就好了，實(shí)在優(yōu)化不了，把這個(gè)查詢?nèi)拥绞聞?wù)外就可以了，甚至都不需要挖掘出整個(gè)事務(wù)。上述這個(gè)問(wèn)題模擬，其實(shí)就是對(duì)應(yīng)第三節(jié)問(wèn)題產(chǎn)生原因中的第二點(diǎn)(事務(wù)中包含性能較差的查詢 SQL)，下面我們把第一點(diǎn)(程序中非數(shù)據(jù)庫(kù)交互操作導(dǎo)致事務(wù)掛起)也模擬下，對(duì)比下現(xiàn)象。我們只需要將事務(wù) 1 的過(guò)程改成如下即可。

 

 

 

  1. 事務(wù)1： 
  2.  
  3. start transaction; 
  4.  
  5. delete from emp where id = 1; 
  6.  
  7. select * from emp where id in (select id from emp); 
  8.  
  9. 等待60s(什么都不要做)             --> 模擬接口調(diào)用超時(shí)，事務(wù)夯住，隨后再執(zhí)行commit。 
  10.  
  11. commit; 

再次用腳本查看，可以看到現(xiàn)象是有所不同的，不同點(diǎn)在于，阻塞事務(wù)處于 sleep 狀態(tài)，即事務(wù)當(dāng)前并不在跑 SQL。從 DBA 的角度看，這類現(xiàn)象八成就可以斷定是代碼在事務(wù)中嵌入了其他的交互操作導(dǎo)致的事務(wù)掛起(另外也有可能是網(wǎng)絡(luò)問(wèn)題導(dǎo)致的事務(wù)僵死)，因?yàn)槌绦虿⒉幌袢?，它不?huì)偷懶，不會(huì)出現(xiàn)事務(wù)執(zhí)行到一半，休息一會(huì)再提交一說(shuō)。

 


如果是這類現(xiàn)象的問(wèn)題，因?yàn)楸举|(zhì)并不是由于 SQL 慢導(dǎo)致的事務(wù)掛起，所以必須要到代碼里去找到對(duì)應(yīng)的點(diǎn)，看下到底是在做什么交互操作卡住了。

 

這里就需要開(kāi)發(fā)去排查代碼才可以找到源頭，但是唯一可用的信息就是該事務(wù)最后執(zhí)行的一條 SQL，也就是上圖中最后一列，從我之前的經(jīng)驗(yàn)來(lái)看(絕大時(shí)候)，開(kāi)發(fā)很難單從這一條 SQL 就可以找到代碼里具體位置，尤其是當(dāng)這條 SQL 是一條很常見(jiàn)的 SQL，就更為困難!

當(dāng)面對(duì)這種情況，就需要 DBA 去挖掘出這個(gè)事務(wù)執(zhí)行過(guò)的所有 SQL，然后再讓開(kāi)發(fā)去排查代碼，這樣難度應(yīng)該就小多了。這里就需要用到 MySQL 的 general_log，該日志用于記錄 MySQL 中所有運(yùn)行過(guò)的 SQL。

 

 

  1. --查看general_log是否開(kāi)啟，及文件名 
  2.  
  3. mysql> show variables like '%general_log%'; 
  4.  
  5. +------------------+--------------------------------------+ 
  6.  
  7. | Variable_name    | Value                                | 
  8.  
  9. +------------------+--------------------------------------+ 
  10.  
  11. | general_log      | OFF                                  | 
  12.  
  13. | general_log_file | /data/mysql_data/192-168-188-155.log | 
  14.  
  15. +------------------+--------------------------------------+ 
  16.  
  17.  
  18.  
  19. --暫時(shí)開(kāi)啟general_log 
  20.  
  21. mysql> set global general_log = 1; 
  22.  
  23. Query OK, 0 rows affected (0.00 sec) 
  24.  
  25.  
  26.  
  27. --暫時(shí)關(guān)閉general_log 
  28.  
  29. mysql> set global general_log = 0; 
  30.  
  31. Query OK, 0 rows affected (0.00 sec) 

開(kāi)啟 general_log 后，手動(dòng)復(fù)現(xiàn)的時(shí)候通過(guò) innodb 行鎖等待腳本查詢結(jié)果中的線程 ID，去 general_log 找到對(duì)應(yīng)的事務(wù)分析即可，如下：


 

 

根據(jù)線程 ID 可以很輕易的從 general_log 中找到對(duì)應(yīng)時(shí)間點(diǎn)的事務(wù)操作(實(shí)際場(chǎng)景下可能需要通過(guò)管道命令過(guò)濾)。如上圖所示，事務(wù) 1 與事務(wù) 2 的全部 SQL 都可以找到，再通過(guò)這些 SQL 去代碼中找到對(duì)應(yīng)的位置即可，比如上圖中線程 ID 為 111 的事務(wù)，執(zhí)行 select * from emp where id in (select id from emp) 后到真正提交，過(guò)了 1min 左右，原因要么就是這條 SQL 查詢慢，要么就是代碼在執(zhí)行其他交互操作。

PS：general_log 由于會(huì)記錄所有 SQL，所以對(duì) MySQL 性能影響較大，且容易暴漲，所以只在問(wèn)題排查時(shí)暫時(shí)開(kāi)啟，問(wèn)題排查后，請(qǐng)及時(shí)關(guān)閉!

5.2 隨機(jī)復(fù)現(xiàn)場(chǎng)景

相較于手動(dòng)復(fù)現(xiàn)場(chǎng)景，這種場(chǎng)景因?yàn)榫哂须S機(jī)性，所以無(wú)法一邊模擬報(bào)錯(cuò)，一邊通過(guò)腳本查詢到具體的阻塞情況，因此需要通過(guò)其他方式來(lái)監(jiān)控 MySQL 的阻塞情況。我一般是通過(guò)在 Linux 上后臺(tái)跑監(jiān)控腳本(innodb_lock_monitor.sh)來(lái)記錄 MySQL 阻塞情況，腳本如下：

 

  1. #!/bin/bash 
  2.  
  3.  
  4.  
  5. #賬號(hào)、密碼、監(jiān)控日志 
  6.  
  7. user="root" 
  8.  
  9. password="Gepoint" 
  10.  
  11. logfile="/root/innodb_lock_monitor.log" 
  12.  
  13.  
  14.  
  15. while true 
  16.  
  17. do 
  18.  
  19.         num=`mysql -u${user} -p${password} -e "select count(*) from information_schema.innodb_lock_waits" |grep -v count` 
  20.  
  21.         if [[ $num -gt 0 ]];then 
  22.  
  23.             date  >> /root/innodb_lock_monitor.log 
  24.  
  25.             mysql -u${user} -p${password} -e  "SELECT r.trx_mysql_thread_id waiting_thread,r.trx_query waiting_query, \ 
  26.  
  27. concat(timestampdiff(SECOND,r.trx_wait_started,CURRENT_TIMESTAMP()),'s') AS duration,\ 
  28.  
  29. b.trx_mysql_thread_id blocking_thread,t.processlist_command state,b.trx_query blocking_query,e.sql_text \ 
  30.  
  31. FROM information_schema.innodb_lock_waits w \ 
  32.  
  33. JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id \ 
  34.  
  35. JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id \ 
  36.  
  37. JOIN performance_schema.threads t on t.processlist_id = b.trx_mysql_thread_id \ 
  38.  
  39. JOIN performance_schema.events_statements_current e USING(thread_id) \G " >> ${logfile} 
  40.  
  41.         fi 
  42.  
  43.         sleep 5 
  44.  
  45. done 

再次查看

 

  1. --使用 nohup 命令后臺(tái)運(yùn)行監(jiān)控腳本 
  2.  
  3. [root@192-168-188-155 ~]# nohup sh innodb_lock_monitor.sh  & 
  4.  
  5. [2] 31464 
  6.  
  7. nohup: ignoring input and appending output to ‘nohup.out’ 
  8.  
  9.  
  10.  
  11. --查看 nohup.out 是否出現(xiàn)報(bào)錯(cuò) 
  12.  
  13. [root@192-168-188-155 ~]# tail -f nohup.out 
  14.  
  15. mysql: [Warning] Using a password on the command line interface can be insecure. 
  16.  
  17. mysql: [Warning] Using a password on the command line interface can be insecure. 
  18.  
  19. mysql: [Warning] Using a password on the command line interface can be insecure. 
  20.  
  21.  
  22.  
  23. --定時(shí)查看監(jiān)控日志是否有輸出(沒(méi)有輸出的話，這個(gè)日志也不會(huì)生成哦！) 
  24.  
  25. [root@192-168-188-155 ~]# tail -f innodb_lock_monitor.log 
  26.  
  27. Wed Feb  5 11:30:11 CST 2020 
  28.  
  29. *************************** 1. row *************************** 
  30.  
  31.  waiting_thread: 112 
  32.  
  33.   waiting_query: delete from emp where id < 10 
  34.  
  35.        duration: 3s 
  36.  
  37. blocking_thread: 111 
  38.  
  39.           state: Sleep 
  40.  
  41.  blocking_query: NULL 
  42.  
  43.        sql_text: select * from emp where id in (select id from emp) 

當(dāng)監(jiān)控日志有輸出阻塞信息時(shí)，后續(xù)解決方案就和之前的手動(dòng)復(fù)現(xiàn)場(chǎng)景一致。

  • 如果是事務(wù)卡在慢 SQL，那么就需要優(yōu)化 SQL。
  • 如果是事務(wù)掛起，那么就通過(guò) general_log 分析事務(wù)，然后找到具體的代碼位置。

PS：?jiǎn)栴}排查完成后，請(qǐng)及時(shí)關(guān)閉后臺(tái)監(jiān)控進(jìn)程，通過(guò) kill+pid 的方式直接關(guān)閉即可!

六、Performance_Schema

之前的方法感覺(jué)不是很方便，因?yàn)?general_log 需要訪問(wèn)服務(wù)器，且過(guò)濾分析也較難，需要一定的 MySQL 基礎(chǔ)及 Linux 基礎(chǔ)才適用，因此想尋找一種更為簡(jiǎn)便的方法。

6.1 方法介紹

個(gè)人想法是利用 MySQL 5.5 開(kāi)始提供的 performance_schema 性能引擎來(lái)進(jìn)行分析，Performance_Schema 是 MySQL 提供的在系統(tǒng)底層監(jiān)視 MySQL 服務(wù)器性能的一個(gè)特性，其提供了大量監(jiān)控項(xiàng)，包括：鎖、IO、事務(wù)、內(nèi)存使用等。

介紹下主要原理：

1. 主要用的表有 2 張 events_transactions_history_long 和 events_statements_history_long。

2. transactions_history_long 會(huì)記錄歷史事務(wù)信息，events_statements_history_long 則記錄歷史 SQL。

3. 從 transactions_history_long 中得到回滾事務(wù)的線程 ID，再根據(jù)時(shí)間范圍去篩選出可疑的事務(wù)，最后從 events_statements_history_long 得到事務(wù)對(duì)應(yīng)的 SQL，從中排查哪個(gè)為源頭。

優(yōu)點(diǎn)：

1. 不需要通過(guò) general_log 來(lái)獲取事務(wù) SQL。

2. 不需要監(jiān)控腳本來(lái)獲取到行鎖等待情況。

3. 只需要訪問(wèn) MySQL 就可以實(shí)現(xiàn)，而不需要訪問(wèn)服務(wù)器。

4. 性能開(kāi)銷較小，且不會(huì)暴漲，因?yàn)槭茄h(huán)覆蓋寫(xiě)入的。

5. 可以知道每條 SQL 的運(yùn)行時(shí)長(zhǎng)。

缺點(diǎn)：

1. history_long 相關(guān)表默認(rèn)保留記錄有限，可能會(huì)把有用的數(shù)據(jù)刷掉，尤其是在 SQL 運(yùn)行較多的系統(tǒng)。

2. 如果要加大 history_long 相關(guān)表的最大保留行數(shù)，需要重啟 MySQL，無(wú)法在線修改參數(shù)。

3. history_long 相關(guān)表記錄中的時(shí)間均為相對(duì)時(shí)間，也就是距離 MySQL 啟動(dòng)的時(shí)長(zhǎng)，看起來(lái)不是很方便。

4. history_long 相關(guān)表不會(huì)主動(dòng)記錄行鎖等待的信息，所以只能通過(guò)先根據(jù)時(shí)間范圍刷選出可疑的事務(wù)，再進(jìn)一步分析，不如腳本監(jiān)控定位的準(zhǔn)。

 

  1. /*開(kāi)啟performance_schema相關(guān)監(jiān)控項(xiàng)，需要提前開(kāi)啟performance_schema*/ 
  2.  
  3. UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name = 'transaction'; 
  4.  
  5. UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' where name like '%events_transactions%'; 
  6.  
  7. UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' where name like '%events_statements%'; 
  8.  
  9.  
  10.  
  11.  
  12.  
  13. /*查看回滾事務(wù)SQL，確認(rèn)是否是日志里報(bào)錯(cuò)的事務(wù)*/ 
  14.  
  15. SELECT a.THREAD_ID 
  16.  
  17.     ,b.EVENT_ID 
  18.  
  19.     ,a.EVENT_NAME 
  20.  
  21.     ,CONCAT (b.TIMER_WAIT / 1000000000000,'s') AS trx_druation 
  22.  
  23.     ,CONCAT (a.TIMER_WAIT / 1000000000000,'s') sql_druation 
  24.  
  25.     ,a.SQL_TEXT,b.STATE,a.MESSAGE_TEXT 
  26.  
  27. FROM performance_schema.events_statements_history_long a 
  28.  
  29. JOIN performance_schema.events_transactions_history_long b ON a.THREAD_ID = b.THREAD_ID 
  30.  
  31.     AND (a.NESTING_EVENT_ID = b.EVENT_ID OR a.EVENT_ID = b.NESTING_EVENT_ID) 
  32.  
  33. WHERE b.autocommit = 'NO' AND a.SQL_TEXT IS NOT NULL AND b.STATE = 'ROLLED BACK' 
  34.  
  35.  
  36.  
  37. /*查看該時(shí)間段內(nèi)可疑事務(wù)即超過(guò)5s的事務(wù)SQL，這里默認(rèn)innodb_lock_wait_timeout為5s*/ 
  38.  
  39. SELECT a.THREAD_ID 
  40.  
  41.     ,b.EVENT_ID 
  42.  
  43.     ,a.EVENT_NAME 
  44.  
  45.     ,CONCAT (b.TIMER_WAIT / 1000000000000,'s') AS trx_druation 
  46.  
  47.     ,CONCAT (a.TIMER_WAIT / 1000000000000,'s') sql_druation 
  48.  
  49.     ,a.SQL_TEXT,b.STATE,a.MESSAGE_TEXT,a.ROWS_AFFECTED,a.ROWS_EXAMINED,a.ROWS_SENT 
  50.  
  51. FROM performance_schema.events_statements_history_long a 
  52.  
  53. JOIN performance_schema.events_transactions_history_long b ON a.THREAD_ID = b.THREAD_ID 
  54.  
  55.     AND (a.NESTING_EVENT_ID = b.EVENT_ID OR a.EVENT_ID = b.NESTING_EVENT_ID) 
  56.  
  57. WHERE b.autocommit = 'NO' AND SQL_TEXT IS NOT NULL AND b.STATE = 'COMMITTED' 
  58.  
  59.     AND b.TIMER_WAIT / 1000000000000  > 5 
  60.  
  61.     AND b.TIMER_START < (SELECT TIMER_START FROM performance_schema.events_transactions_history_long 
  62.  
  63.         WHERE THREAD_ID = 70402  /*上述SQL查詢結(jié)果中的線程ID*/ 
  64.  
  65.         AND EVENT_ID = 518)      /*上述SQL查詢結(jié)果中的事件ID*/ 
  66.  
  67.     AND b.TIMER_END > ( SELECT TIMER_END FROM performance_schema.events_transactions_history_long 
  68.  
  69.         WHERE THREAD_ID = 70402  /*上述SQL查詢結(jié)果中的線程ID*/ 
  70.  
  71.         AND EVENT_ID = 518)     /*上述SQL查詢結(jié)果中的事件ID*/ 
  72.  
  73. ORDER BY a.THREAD_ID 

6.2 測(cè)試模擬

如果是用這種方法的話，那么就不需要分手動(dòng)復(fù)現(xiàn)還是隨機(jī)復(fù)現(xiàn)了，操作方法都是一樣的，下面模擬下如何操作：

1. 首先通過(guò)上述方法開(kāi)啟 performance_schema 相關(guān)監(jiān)控項(xiàng)，會(huì)直接生效，無(wú)需重啟 MySQL。

2. 然后復(fù)現(xiàn)問(wèn)題，這里最好是手動(dòng)復(fù)現(xiàn)(因?yàn)閺?fù)現(xiàn)后如果沒(méi)有及時(shí)查看，監(jiān)控?cái)?shù)據(jù)可能就會(huì)被刷掉)，不行的話就只能等待隨機(jī)復(fù)現(xiàn)了。

3. 問(wèn)題復(fù)現(xiàn)后通過(guò)上述腳本查詢是否存在回滾事務(wù)(即因?yàn)樾墟i超時(shí)回滾的事務(wù))。

 

4. 然后根據(jù)回滾事務(wù)的線程 ID 和事件 ID，帶入到最后一個(gè)腳本中，查看可疑事務(wù)，進(jìn)行分析。

 

這里由于是測(cè)試環(huán)境模擬，所以結(jié)果非常了然，項(xiàng)目上實(shí)際輸出結(jié)果可能有很多，需要一一分析事務(wù)是否有問(wèn)題!

七、總結(jié)

實(shí)際測(cè)試后，發(fā)現(xiàn)通過(guò) performance_schema 來(lái)排查行鎖等待超時(shí)問(wèn)題限制其實(shí)也比較多，而且最后的分析也是一門(mén)技術(shù)活，并不如一開(kāi)始想象的那么簡(jiǎn)單，有點(diǎn)事與愿違了。

通過(guò) performance_schema 排查問(wèn)題最難處理的有 3 點(diǎn)：

1. 時(shí)間問(wèn)題，相對(duì)時(shí)間如何轉(zhuǎn)換為絕對(duì)時(shí)間，這個(gè)目前一直找不到好的方法。

2. 不會(huì)主動(dòng)記錄下行鎖等待的信息，所以只能通過(guò)時(shí)間節(jié)點(diǎn)刷選后進(jìn)一步分析。

3. 記錄被刷問(wèn)題，因?yàn)槭莾?nèi)存表，設(shè)置很大容易內(nèi)存溢出，設(shè)置很小就容易被很快刷掉。

責(zé)任編輯：華軒 來(lái)源： 楊建榮的學(xué)習(xí)筆記
MySQL數(shù)據(jù)庫(kù)SQL
相關(guān)推薦

2021-02-22 17:18:35

MySQLSQL行鎖

2021-03-26 10:40:16

MySQL鎖等待死鎖

2010-11-25 11:15:11

MySQL查詢超時(shí)

2022-12-07 10:56:23

線程池監(jiān)控執(zhí)行超時(shí)

2009-03-24 10:45:39

MySQL5連接超時(shí)數(shù)據(jù)庫(kù)

2009-06-10 08:45:31

LinuxWhile文件等待

2024-11-29 07:38:12

MySQL鎖數(shù)據(jù)庫(kù)

2025-02-10 09:58:48

2010-05-19 10:18:23

svn 403 for

2011-03-07 09:05:49

鎖競(jìng)爭(zhēng)MySQL等待時(shí)間

2011-03-15 15:47:04

MySQL鎖競(jìng)爭(zhēng)

2010-05-24 12:50:59

MySQL表級(jí)鎖

2020-10-20 13:50:47

MySQL數(shù)據(jù)庫(kù)鎖

2024-03-04 00:01:00

鎖表鎖行MySQL

2022-07-20 08:06:57

MySQL表鎖Innodb

2024-05-13 12:44:00

InnodbMySQL行級(jí)鎖

2010-10-14 09:15:20

MySQL查詢

2018-07-31 10:10:06

MySQLInnoDB死鎖

2022-10-24 00:33:59

MySQL全局鎖行級(jí)鎖

2010-11-22 14:42:13

MySQL行級(jí)鎖
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)