MySQL死鎖舉例分析

134次閱讀

共計 7170 個字符，預計需要花費 18 分鐘才能閱讀完成。

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

一前言
死鎖，其實是一個很有意思，也很有挑戰的技術問題，大概每個 DBA 和部分開發同學都會在工作過程中遇見過。關于死鎖我會持續寫一個系列的案例分析，希望能夠對想了解死鎖的朋友有所幫助。本文介紹一例三個并發 insert 導致的死鎖，根本原因還是在于 insert 唯一鍵申請插入意向鎖這個特殊的 GAP 鎖。其實稱呼插入意向鎖為 Insert Intention Gap Lock 更為合理。
二案例分析
2.1 環境準備
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 死鎖日志

————————

LATEST DETECTED DEADLOCK

————————

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

*** (1) TRANSACTION:

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

mysql tables in use 1, locked 1

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

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

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

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

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

*** (2) TRANSACTION:

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

mysql tables in use 1, locked 1

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

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

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

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

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

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

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

*** WE ROLL BACK TRANSACTION (2)

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

從兼容性矩陣我們可以得到如下結論:

INSERT 操作之間不會有沖突。

GAP,Next-Key 會阻止 Insert。

GAP 和 Record,Next-Key 不會沖突

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

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

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

—TRANSACTION 462308737, ACTIVE 5 sec

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

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

show engine innodb status

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 執行插入操作
insert into t6(id,a) values(7,15);

—TRANSACTION 462308738, ACTIVE 4 sec inserting

mysql tables in use 1, locked 1

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

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

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

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

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

——————

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

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

—TRANSACTION 462308737, ACTIVE 66 sec

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

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

show engine innodb status

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

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 會話已經插入成功并且持有唯一鍵 a=15 的 X 行鎖，故而 sess2 insert 進行唯一性檢查，先申請 LOCK_S + LOCK_ORDINARY , 事務日志列表中提示 lock mode S waiting
第三部 sess3 執行插入操作
insert into t6(id,a) values(8,15);

—TRANSACTION 462308739, ACTIVE 3 sec inserting

mysql tables in use 1, locked 1

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

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

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

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

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

——————

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

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

—TRANSACTION 462308738, ACTIVE 35 sec inserting

mysql tables in use 1, locked 1

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

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

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

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

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

——————

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

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

—TRANSACTION 462308737, ACTIVE 97 sec

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

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

show engine innodb status

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

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 執行回滾操作，sess2 不提交
sess1 rollback;
此時 sess2 插入成功，sess3 出現死鎖，此時 sess2 insert 插入成功, 還未提交, 事務列表如下:

————

TRANSACTIONS

————

Trx id counter 462308744

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

History list length 1866

LIST OF TRANSACTIONS FOR EACH SESSION:

—TRANSACTION 462308737, not started

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

show engine innodb status

—TRANSACTION 462308739, not started

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

—TRANSACTION 462308738, ACTIVE 75 sec

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

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

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

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

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

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 執行 insert 插入(6,15), 在插入之前進行唯一性檢查發現和 sess1 的已經插入的記錄重復鍵需要申請 LOCK_S|LOCK_ORDINARY, 但與 sess1 的(LOCK_X | LOCK_REC_NOT_GAP) 沖突, 加入等待隊列, 等待 sess1 釋放鎖。
sess3 執行 insert 插入 (7,15), 在插入之前進行唯一性檢查發現和 sess1 的已經插入的記錄重復鍵需要申請 LOCK_S|LOCK_ORDINARY, 但與 sess1 的(LOCK_X | LOCK_REC_NOT_GAP) 沖突, 加入等待隊列, 等待 sess1 釋放鎖。
sess1 執行 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 鎖，于是出現死鎖，MySQL 選擇回滾其中之一。

到此，關于“MySQL 死鎖舉例分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注丸趣 TV 網站，丸趣 TV 小編會繼續努力為大家帶來更多實用的文章！

正文完