mysql簡單排查&終止死鎖線程 2022-03-29

mysql簡單排查&終止死鎖線程

INFORMATION_SCHEMA

MySQL把INFORMATION_SCHEMA看作是一個【數(shù)據(jù)庫】
在INFORMATION_SCHEMA中，有若干個只讀表。這些只讀表實際上是視圖而不是基本表，因此，無法看到與之相關(guān)的任何文件

在INFORMATION_SCHEMA的這些表中，有3個表可以幫助我們排查死鎖

  • INNODB_LOCKS:現(xiàn)在獲取的鎖，但是不含沒有獲取的鎖，而且只是針對INNODB的。
  • INNODB_LOCK_WAITS：系統(tǒng)鎖等待相關(guān)信息，包含了阻塞的一行或者多行的記錄，包含鎖請求和被阻塞鎖請求的鎖信息等。
  • INNODB_TRX：包含了所有正在執(zhí)行的事務(wù)相關(guān)信息（INNODB），而且包含了事務(wù)是否被阻塞或者請求鎖。

1：查看當(dāng)前活躍的事務(wù)

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看當(dāng)前已經(jīng)獲取鎖的事務(wù)

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看當(dāng)前等待鎖的事務(wù)

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

or

SELECT
  waiting_trx_id,
  waiting_pid,
  waiting_query,
  blocking_trx_id,
  blocking_pid,
  blocking_query
FROM sys.innodb_lock_waits;

找到導(dǎo)致死鎖的事務(wù)后，直接kill related_mysql_thread_id結(jié)束事務(wù)

死鎖解決方式

  • 預(yù)防死鎖。例如固定持有鎖的先后順序（該方法不具備普適性），縮小鎖的影響范圍

To reduce the possibility of deadlocks,

  • use transactions rather than LOCK TABLES statements; (縮小鎖的影響范圍)
  • keep transactions that insert or update data small enough that they do not stay open for long periods of time; (縮小鎖的影響范圍)
  • when different transactions update multiple tables or large ranges of rows, use the same order of operations (such as SELECT ... FOR UPDATE) in each transaction; (固定持有鎖的先后順序)
  • create indexes on the columns used in SELECT ... FOR UPDATE and UPDATE ... WHERE statements. (縮小鎖的影響范圍)

The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations.

  • 處理死鎖。必然需要有一方放棄資源占用，可以依據(jù)優(yōu)先級、等待時間等條件選擇放棄資源的一方

更多詳見官網(wǎng)文檔
https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-examples.html

解讀死鎖日志

InnoDB monitor output里關(guān)于鎖的描述釋義如下

記錄鎖（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap
間隙鎖（LOCK_GAP）: lock_mode X locks gap before rec
Next-key 鎖（LOCK_ORNIDARY）: lock_mode X
插入意向鎖（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention

example1

> show engine innodb status; // InnoDB monitor output reads as following

------------------------
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION: #事務(wù)1
TRANSACTION 462308399, ACTIVE 33 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 3525577, OS thread handle 0x7f896cc4b700, query id 780039657 localhost root updating
delete from ty where a=5
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308399 lock_mode X waiting


*** (2) TRANSACTION: #事務(wù)2
TRANSACTION 462308398, ACTIVE 61 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 3525490, OS thread handle 0x7f78eab82700, query id 780039714 localhost root update
insert into ty (a,b) values(2,10)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 219 page no 4 n bits 72 index `idxa` of table `test`.`ty` trx id 462308398 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

日志分析

*** (1) TRANSACTION: #事務(wù)1
TRANSACTION 462308399, ACTIVE 33 sec starting index read
事務(wù)編號為 462308399 ，活躍33秒，starting index read 表示事務(wù)狀態(tài)為根據(jù)索引讀取數(shù)據(jù)。常見的其他狀態(tài):

fetching rows 表示事務(wù)狀態(tài)在row_search_for_mysql中被設(shè)置，正在查找記錄。
updating or deleting 表示事務(wù)已經(jīng)真正進入了Update/delete的函數(shù)邏輯（row_update_for_mysql）
thread declared inside InnoDB 說明事務(wù)已經(jīng)進入innodb層。通常而言 不在innodb層的事務(wù)大部分是會被回滾的

mysql tables in use 1, 說明當(dāng)前的事務(wù)使用一個表。locked 1 表示表上有一個表鎖，對應(yīng)DML語句為LOCK_IX
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
LOCK WAIT表示正在等待鎖, 2 lock struct(s) 表示trx->trx_locks(該事務(wù)持有的鎖的鏈表)的長度為2，每個鏈表節(jié)點代表該事務(wù)持有的一個鎖結(jié)構(gòu)，包括表鎖，記錄鎖以及auto_inc鎖等
heap size 360 表示事務(wù)分配的鎖堆內(nèi)存大小,一般沒有什么具體的用處
delete from ty where a=5 表示事務(wù)1當(dāng)前正在等待鎖的sql
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:【這里是重點】
RECORD LOCKS space id 219 page no 4 n bits 72 index idxa of table test.ty trx id 462308399 lock_mode X waiting
RECORD LOCKS 表示記錄鎖, space id為219, page號4 ，n bits 72表示這個聚集索引記錄鎖結(jié)構(gòu)上留有72個Bit位
表示事務(wù)1 正在等待表 ty 上的 索引idxa 的Next-Key lock

事務(wù)2的log類似，
*** (2) HOLDS THE LOCK(S):【這里是重點】
RECORD LOCKS space id 219 page no 4 n bits 72 index idxa of table test.ty trx id 462308398 lock_mode X
顯示了事務(wù)2已經(jīng)持有了一個Next-key鎖，這正是使得事務(wù)1日志里面出現(xiàn)lock_mode X waiting的原因
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:【這里是重點】
RECORD LOCKS space id 219 page no 4 n bits 72 index idxa of table test.ty trx id 462308398 lock_mode X locks gap before rec insert intention waiting
lock_mode X locks gap before rec insert intention waiting表示事務(wù)2的insert語句正在等待插入意向鎖

example2

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-04-22 18:48:20 0x7f52ef1a0700
*** (1) TRANSACTION:
TRANSACTION 3019177082, ACTIVE 916 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 30208 lock struct(s), heap size 3252432, 899952 row lock(s)
MySQL thread id 28804115, OS thread handle 139989662291712, query id 35898224955 11.111.xxx.xxx xxx_console Searching rows for update
UPDATE `host` SET `is_deleted` = 1, `gmt_modified` = '2022-04-22 18:33:04' WHERE (version < '1650620210' and module_id = 7269 and is_deleted = 0)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 29 page no 42059 n bits 1000 index IDX_host_module_id of table `db`.`host` trx id 3019177082 lock_mode X  // 【事務(wù)1嘗試更新表host的若干記錄，通過where條件在`db`.`host`的索引IDX_host_module_id持有module_id = 7269的next-key lock】
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 ...


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29 page no 30452 n bits 96 index PRIMARY of table `db`.`host` trx id 3019177082 lock_mode X locks rec but not gap waiting // 【事務(wù)1在更新時等待`db`.`host`的主鍵上的記錄鎖】
Record lock, heap no 26 PHYSICAL RECORD: n_fields 67; compact format; info bits 128
 ...


*** (2) TRANSACTION:
TRANSACTION 3018902995, ACTIVE 3490 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 28794268, OS thread handle 139980536006400, query id 35890389634 11.134.33.42 db_console updating
UPDATE `host` SET `module_id` = 7261 WHERE `host`.id = xxx.

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 29 page no 30452 n bits 96 index PRIMARY of table `db`.`host` trx id 3018902995 lock_mode X locks rec but not gap // 【事務(wù)2更新操作通過where條件獲取了主鍵上的一個記錄鎖，然后意圖修改module_id，這需要在IDX_host_module_id的特定位置上再加一個記錄鎖】
Record lock, heap no 26 PHYSICAL RECORD: n_fields 67; compact format; info bits 128
 ...


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 29 page no 42059 n bits 1000 index IDX_host_module_id of table `db`.`host` trx id 3018902995 lock_mode X locks rec but not gap waiting // 【事務(wù)1持有的IDX_host_module_id上的next-key lock與事務(wù)2想加的記錄鎖沖突】
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  ...

*** WE ROLL BACK TRANSACTION (2)

簡單來說就是：

事務(wù)1更新數(shù)據(jù)
hold module_id next-key lock
wait host.primary record lock

事務(wù)2更新數(shù)據(jù)
hold host.primary record lock
wait module_id record lock

各自hold一個索引的鎖，去等待另一個索引上的鎖

example3 update并發(fā)死鎖

表all_grade，id\grade\course
其中grade和course上建有二級索引

數(shù)學(xué)成績59分的每人加1分
update all_grade set grade = grade + 1 where grade = 59 and course = "math"

英語成績59分的每人加1分
update all_grade set grade = grade + 1 where grade = 59 and course = "english"

以上兩條update語句并發(fā)時，出現(xiàn)死鎖
但理論上，這兩條語句應(yīng)該命中的是不同的記錄行，不應(yīng)該發(fā)生鎖沖突才對。這的確是一個非常感性的認知

既然沖突了，看一下show engine innodb status
截取了一些關(guān)鍵信息

(1) TRANSACTION: TRANSACTION 5419486286, ACTIVE 1 sec starting index read(**開始掃描索引**) 
mysql tables in use 3, locked 3 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s) 
MySQL thread id 51677946, OS thread handle 139987348158208, query id 61483207592 11.134.xxx.xxx all_grade Searching rows for update 
UPDATE `all_grade` SET `grade` = `grade` + 1 WHERE (grade = 59 and course = "math")
(1) HOLDS THE LOCK(S): RECORD LOCKS space id 29 page no 32292 n bits 96 index PRIMARY of table `testdb`.`all_grade` 
trx id 5419486286 lock_mode X locks rec but not gap Record lock, heap no 6 PHYSICAL RECORD(**持有主鍵鎖**)
(1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 29 page no 31616 n bits 1192 index IDX_grade of table `testdb`.`all_grade` 
trx id 5419486286 lock_mode X waiting Record lock, heap no 2 PHYSICAL RECORD


(2) TRANSACTION: TRANSACTION 5419478123, ACTIVE 2 sec fetching rows 
mysql tables in use 3, locked 3 LOCK WAIT 34147 lock struct(s), heap size 3678416, 1001924 row lock(s) 
MySQL thread id 51678007, OS thread handle 139988848178944, query id 61483169608 11.134.xxx.xxx all_grade Searching rows for update 
UPDATE `all_grade` SET `grade` = `grade` + 1 WHERE (grade = 59 and course = "english")
(2) HOLDS THE LOCK(S): RECORD LOCKS space id 29 page no 31616 n bits 1192 index IDX_grade of table `testdb`.`all_grade` 
trx id 5419478123 lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1;
(2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 29 page no 32292 n bits 96 index PRIMARY of table `testdb`.`all_grade` 
trx id 5419478123 lock_mode X locks rec but not gap waiting Record lock, heap no 6 PHYSICAL RECORD

然后通過explain發(fā)現(xiàn)，對于update的where使用了兩個帶索引的字段，mysql使用了indexmerge。這是產(chǎn)生死鎖的關(guān)鍵

  • t1\t2都在并發(fā)掃描course和grade索引
  • a. t1掃描并鎖course=math索引記錄的時候，緊接著把主鍵索引記錄也一起鎖住了。而被鎖住的主鍵索引記錄中的一部分恰好指向grade=59的數(shù)據(jù)行
  • b. t2掃描并鎖grade=59索引記錄的時候，緊接著要去把主鍵索引記錄也一起鎖住了，但是部分主鍵索引記錄已經(jīng)被a中t1加鎖持有，于是等待PRIMARY
  • c. t1掃描并打算鎖grade=59索引記錄的時候，發(fā)現(xiàn)b中t2已經(jīng)上了鎖，于是等待IDX_grade

簡單來說就是，
t1掃描course索引并鎖定的記錄，有一部分是t2掃描grade需要鎖定的
t2掃描grade索引并鎖定的記錄，t1也要鎖定一摸一樣的記錄

使用了多個索引進行搜索的寫操作，【當(dāng)a事務(wù)掃描索引1需要鎖定的記錄 與 b事務(wù)掃描索引2需要鎖定的記錄存在交集】，并發(fā)時死鎖的概率大大提高

其他人遇到的update并發(fā)死鎖場景：
https://developer.aliyun.com/article/332485

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 本系列文章主要是本人在游戲服務(wù)端開發(fā)過程中，遇到的一些不那么為人熟知但我又覺得比較重要的MySQL知識的介紹。希望...
    higher2017閱讀 765評論 0贊 1
  • 本系列文章主要是本人在游戲服務(wù)端開發(fā)過程中，遇到的一些不那么為人熟知但我又覺得比較重要的MySQL知識的介紹。希望...
    higher2017閱讀 450評論 0贊 0
  • Mysql概述 數(shù)據(jù)庫是一個易于訪問和修改的信息集合。它允許使用事務(wù)來確保數(shù)據(jù)的安全性和一致性，并能快速處理百萬條...
    彥幀閱讀 13,963評論 10贊 460
  • MySQL 官方文檔 https://www.mysql.com/[https://www.mysql.com/]...
    智行孫閱讀 727評論 0贊 1
  • 1、簡介 相當(dāng)于Linux文件系統(tǒng)，只不過比文件系統(tǒng)強大 2、功能了解 數(shù)據(jù)讀寫數(shù)據(jù)安全和一致性提高性能熱備份自動...
    小一_d28d閱讀 286評論 0贊 0

友情鏈接更多精彩內(nèi)容