溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

MySQL死鎖舉例分析

發(fā)布時間：2021-11-18 12:30:22 來源：億速云 閱讀：169 作者：iii 欄目：MySQL數(shù)據(jù)庫

這篇文章主要介紹“MySQL死鎖舉例分析”，在日常操作中，相信很多人在MySQL死鎖舉例分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL死鎖舉例分析”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！

一 前言
  死鎖，其實是一個很有意思，也很有挑戰(zhàn)的技術(shù)問題，大概每個DBA和部分開發(fā)同學(xué)都會在工作過程中遇見過 。關(guān)于死鎖我會持續(xù)寫一個系列的案例分析，希望能夠?qū)ο肓私馑梨i的朋友有所幫助。本文介紹一例三個并發(fā)insert 導(dǎo)致的死鎖，根本原因還是在于insert 唯一鍵申請插入意向鎖這個特殊的GAP鎖。其實稱呼插入意向鎖 為 Insert Intention Gap Lock 更為合理。
二 案例分析
2.1 環(huán)境準備 
Percona server 5.6 RR模式

sess1

sess2

sess3


begin;



insert into t6(id,a) values(6,15);

begin;



insert into t6(id,a) values(7,15);

begin;



insert into t6(id,a) values(8,15);

rollback;


ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction



2.2 死鎖日志

  1. ------------------------

  2. LATEST DETECTED DEADLOCK

  3. ------------------------

  4. 2017-09-18 10:03:50 7f78eae30700

  5. *** (1) TRANSACTION:

  6. TRANSACTION 462308725, ACTIVE 18 sec inserting, thread declared inside InnoDB 1

  7. mysql tables in use 1, locked 1

  8. LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1

  9. MySQL thread id 3825465, OS thread handle 0x7f78eaef4700, query id 781148519 localhost root update

  10. insert into t6(id,a) values(7,15)

  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

  12. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308725 lock_mode X insert intention waiting

  13. *** (2) TRANSACTION:

  14. TRANSACTION 462308726, ACTIVE 10 sec inserting, thread declared inside InnoDB 1

  15. mysql tables in use 1, locked 1

  16. 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1

  17. MySQL thread id 3825581, OS thread handle 0x7f78eae30700, query id 781148528 localhost root update

  18. insert into t6(id,a) values(8,15)

  19. *** (2) HOLDS THE LOCK(S):

  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock mode S

  21. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

  22. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308726 lock_mode X insert intention waiting

  23. *** WE ROLL BACK TRANSACTION (2)

2.3 死鎖分析
首先依然要再次強調(diào)insert 插入操作的加鎖邏輯。
第一階段: 唯一性約束檢查，先申請LOCK_S + LOCK_ORDINARY
第二階段: 獲取階段一的鎖并且insert成功之后,插入的位置有Gap鎖:LOCK_INSERT_INTENTION,為了防止其他insert唯一鍵沖突。
               新數(shù)據(jù)插入:LOCK_X + LOCK_REC_NOT_GAP
對于insert操作來說，若發(fā)生唯一約束沖突，則需要對沖突的唯一索引加上S Next-key Lock。從這里會發(fā)現(xiàn)，即使是RC事務(wù)隔離級別，也同樣會存在Next-Key Lock鎖，從而阻塞并發(fā)。然而，文檔沒有說明的是，對于檢測到?jīng)_突的唯一索引，等待線程在獲得S Lock之后，還需要對下一個記錄進行加鎖，在源碼中由函數(shù)row_ins_scan_sec_index_for_duplicate進行判斷.
其次 我們需要了解 鎖的兼容性矩陣。
MySQL死鎖舉例分析
從兼容性矩陣我們可以得到如下結(jié)論:

  1. INSERT操作之間不會有沖突。

  2. GAP,Next-Key會阻止Insert。

  3. GAP和Record,Next-Key不會沖突

  4. Record和Record、Next-Key之間相互沖突。

  5. 已有的Insert鎖不阻止任何準備加的鎖。

這個案例是三個會話并發(fā)執(zhí)行的，我打算一步一步來分析每個步驟執(zhí)行完之后的事務(wù)日志。
第一步 sess1 執(zhí)行插入操作
insert into t6(id,a) values(6,15);

  1. ---TRANSACTION 462308737, ACTIVE 5 sec

  2. 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1

  3. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149440 localhost root init

  4. show engine innodb status

  5. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX

因為第一個插入的語句，所以唯一性沖突檢查通過，成功插入(6,15). 此時sess1 會話持有(6,15)的LOCK_X|LOCK_REC_NOT_GAP鎖。參考"INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row."

第二步 sess2 執(zhí)行插入操作
insert into t6(id,a) values(7,15);

  1. ---TRANSACTION 462308738, ACTIVE 4 sec inserting

  2. mysql tables in use 1, locked 1

  3. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

  4. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update

  5. insert into t6(id,a) values(7,15)

  6. ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:

  7. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting

  8. ------------------

  9. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX

  10. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting

  11. ---TRANSACTION 462308737, ACTIVE 66 sec

  12. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

  13. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149526 localhost root init

  14. show engine innodb status

  15. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX

  16. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap

首先sess2的insert 申請了IX鎖，因為sess1 會話已經(jīng)插入成功并且持有唯一鍵 a=15的X 行鎖 ，故而sess2 insert 進行唯一性檢查，先申請LOCK_S + LOCK_ORDINARY ,事務(wù)日志列表中提示lock mode S waiting
第三部 sess3 執(zhí)行插入操作
insert into t6(id,a) values(8,15);

  1. ---TRANSACTION 462308739, ACTIVE 3 sec inserting

  2. mysql tables in use 1, locked 1

  3. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

  4. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root update

  5. insert into t6(id,a) values(8,15)

  6. ------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

  7. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308739 lock mode S waiting

  8. ------------------

  9. TABLE LOCK table `test`.`t6` trx id 462308739 lock mode IX

  10. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308739 lock mode S waiting

  11. ---TRANSACTION 462308738, ACTIVE 35 sec inserting

  12. mysql tables in use 1, locked 1

  13. LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

  14. MySQL thread id 3825768, OS thread handle 0x7f78ea9c9700, query id 781149521 localhost root update

  15. insert into t6(id,a) values(7,15)

  16. ------- TRX HAS BEEN WAITING 35 SEC FOR THIS LOCK TO BE GRANTED:

  17. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting

  18. ------------------

  19. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX

  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S waiting

  21. ---TRANSACTION 462308737, ACTIVE 97 sec

  22. 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1

  23. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149560 localhost root init

  24. show engine innodb status

  25. TABLE LOCK table `test`.`t6` trx id 462308737 lock mode IX

  26. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308737 lock_mode X locks rec but not gap

與會話sess2 的加鎖申請流程一致，都在等待sess1釋放鎖資源。
第四步 sess1 執(zhí)行回滾操作，sess2 不提交
sess1 rollback;
此時sess2 插入成功，sess3出現(xiàn)死鎖，此時sess2 insert插入成功,還未提交,事務(wù)列表如下:

  1. ------------

  2. TRANSACTIONS

  3. ------------

  4. Trx id counter 462308744

  5. Purge done for trx s n:o < 462308744 undo n:o < 0 state: running but idle

  6. History list length 1866

  7. LIST OF TRANSACTIONS FOR EACH SESSION:

  8. ---TRANSACTION 462308737, not started

  9. MySQL thread id 3825779, OS thread handle 0x7f78eacd9700, query id 781149626 localhost root init

  10. show engine innodb status

  11. ---TRANSACTION 462308739, not started

  12. MySQL thread id 3825764, OS thread handle 0x7f78ea593700, query id 781149555 localhost root cleaning up

  13. ---TRANSACTION 462308738, ACTIVE 75 sec

  14. 5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1

  15. MySQL thread id 3825768, OS thread handle 0x7f78eadce700, query id 781149608 localhost root cleaning up

  16. TABLE LOCK table `test`.`t6` trx id 462308738 lock mode IX

  17. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S

  18. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S

  19. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock_mode X insert intention

  20. RECORD LOCKS space id 227 page no 4 n bits 80 index `idx_a` of table `test`.`t6` trx id 462308738 lock mode S locks gap before rec

死鎖的原因
 sess1 insert成功并針對a=15的唯一鍵加上X鎖。
 sess2 執(zhí)行insert 插入(6,15), 在插入之前進行唯一性檢查發(fā)現(xiàn)和sess1的已經(jīng)插入的記錄重復(fù)鍵需要申請LOCK_S|LOCK_ORDINARY, 但與sess1 的(LOCK_X | LOCK_REC_NOT_GAP)沖突,加入等待隊列,等待sess1 釋放鎖。
 sess3 執(zhí)行insert 插入(7,15), 在插入之前進行唯一性檢查發(fā)現(xiàn)和sess1的已經(jīng)插入的記錄重復(fù)鍵需要申請LOCK_S|LOCK_ORDINARY, 但與sess1 的(LOCK_X | LOCK_REC_NOT_GAP)沖突,加入等待隊列,等待sess1 釋放鎖。
 sess1 執(zhí)行rollback, sess1 釋放索引a=15 上的排他記錄鎖(LOCK_X | LOCK_REC_NOT_GAP),此后 sess2和sess3 獲得S鎖(LOCK_S|LOCK_ORDINARY)成功,sess2和sess3都要請求索引a=15上的排他記錄鎖(LOCK_X | LOCK_REC_NOT_GAP),日志中提示 lock_mode X insert intention。由于X鎖與S鎖互斥，sess2和sess3都等待對方釋放S鎖，于是出現(xiàn)死鎖，MySQL 選擇回滾其中之一。

到此，關(guān)于“MySQL死鎖舉例分析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注億速云網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>

向AI問一下細節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手