mysql不同數據庫庫之間死鎖的簡單介紹

本文目錄一覽：

1、詳解MySQL（InnoDB）如何處理死鎖
2、mysql 發生死鎖問題請求幫助
3、用sql語句，怎麼解決mysql數據庫死鎖
4、mysql 死鎖排查
5、解決一次mysql死鎖問題

詳解MySQL（InnoDB）如何處理死鎖

鎖是需要事務結束後才釋放的。

一個是 MVCC，一個是兩階段鎖協議。

為什麼要並發控制呢？是因為多個用戶同時操作 MySQL 的時候，為了提高並發性能並且要求如同多個用戶的請求過來之後如同串行執行的一樣（為了解決臟讀、不可重複讀、幻讀）

官方定義：

兩階段鎖協議是指所有事務必須分兩個階段對數據加鎖和解鎖，在對任何數據進行讀、寫操作之前，事務首先要獲得對該數據的封鎖；在釋放一個封鎖之後，事務不再申請和獲得任何其他封鎖。

對應到 MySQL 上分為兩個階段：

但是兩階段鎖協議不要求事務必須一次將所有需要使用的數據加鎖（innodb在需要的索引列數據才鎖行），並且在加鎖階段沒有順序要求，所以這種並發控制方式會形成死鎖。

MySQL有兩種死鎖處理方式：

死鎖檢測（默認開啟）

死鎖檢測的原理是構建一個以事務為頂點、鎖為邊的有向圖，判斷有向圖是否存在環，存在即有死鎖。

回滾

檢測到死鎖之後，選擇插入更新或者刪除的行數最少的事務回滾，基於 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段來判斷。

收集死鎖信息：

減少死鎖：

死鎖解決：

mysql 發生死鎖問題請求幫助

引起死鎖肯定是由於資源共享衝突，事務是保證一個操作單元能執行順利或失敗，保證數據的完整性的。

對於資源衝突肯定是需要鎖來控制，也就是使用數據的隔離機制和同步鎖來控制的，數據庫的隔離機制修改影響會比較大，所以建議在dao層使用同步或者加鎖來防止deadlock

但是可能會影響部分性能

用sql語句，怎麼解決mysql數據庫死鎖

MySQL死鎖問題的相關知識是本文我們主要要介紹的內容，接下來我們就來一一介紹這部分內容，希望能夠對您有所幫助。

1、MySQL常用存儲引擎的鎖機制

MyISAM和MEMORY採用表級鎖(table-level locking)

BDB採用頁面鎖(page-level locking)或表級鎖，默認為頁面鎖

InnoDB支持行級鎖(row-level locking)和表級鎖，默認為行級鎖

2、各種鎖特點

表級鎖：開銷小，加鎖快;不會出現死鎖;鎖定粒度大，發生鎖衝突的概率最高，並發度最低

行級鎖：開銷大，加鎖慢;會出現死鎖;鎖定粒度最小，發生鎖衝突的概率最低，並發度也最高

頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間;會出現死鎖;鎖定粒度界於表鎖和行鎖之間，並發度一般

3、各種鎖的適用場景

表級鎖更適合於以查詢為主，只有少量按索引條件更新數據的應用，如Web應用

行級鎖則更適合於有大量按索引條件並發更新數據，同時又有並發查詢的應用，如一些在線事務處理系統

4、死鎖

是指兩個或兩個以上的進程在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，它們都將無法推進下去。

表級鎖不會產生死鎖。所以解決死鎖主要還是針對於最常用的InnoDB。

5、死鎖舉例分析

在MySQL中，行級鎖並不是直接鎖記錄，而是鎖索引。索引分為主鍵索引和非主鍵索引兩種，如果一條sql語句操作了主鍵索引，MySQL就會鎖定這條主鍵索引;如果一條語句操作了非主鍵索引，MySQL會先鎖定該非主鍵索引，再鎖定相關的主鍵索引。

在UPDATE、DELETE操作時，MySQL不僅鎖定WHERE條件掃描過的所有索引記錄，而且會鎖定相鄰的鍵值，即所謂的next-key locking。

例如，一個表db。tab_test，結構如下：

id：主鍵;

state：狀態;

time：時間;

索引：idx_1(state，time)

出現死鎖日誌如下：

?***(1) TRANSACTION:

?TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OSthread id 278546 starting index read

?mysql tables in use 1, locked 1

?LOCK WAIT 3 lock struct(s), heap size 320

?MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update

?update tab_test set state=1064,time=now() where state=1061 and time date_sub(now(), INTERVAL 30 minute) (任務1的sql語句)

?***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任務1等待的索引記錄)

?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting

?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

?*** (2) TRANSACTION:

?TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499

?mysql tables in use 1, locked 1

?3 lock struct(s), heap size 320, undo log entries 1

?MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任務2的sql語句)

?*** (2) HOLDS THE LOCK(S): (任務2已獲得的鎖)

?RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap

?Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

?0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;;

?*** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任務2等待的鎖)

?RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting

?Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

?0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;;

?*** WE ROLL BACK TRANSACTION (1)

?(回滾了任務1，以解除死鎖)

原因分析：

當“update tab_test set state=1064,time=now() where state=1061 and time date_sub(now(), INTERVAL 30 minute)”執行時，MySQL會使用idx_1索引，因此首先鎖定相關的索引記錄，因為idx_1是非主鍵索引，為執行該語句，MySQL還會鎖定主鍵索引。

假設“update tab_test set state=1067,time=now () where id in (9921180)”幾乎同時執行時，本語句首先鎖定主鍵索引，由於需要更新state的值，所以還需要鎖定idx_1的某些索引記錄。

這樣第一條語句鎖定了idx_1的記錄，等待主鍵索引，而第二條語句則鎖定了主鍵索引記錄，而等待idx_1的記錄，這樣死鎖就產生了。

6、解決辦法

拆分第一條sql，先查出符合條件的主鍵值，再按照主鍵更新記錄：

?select id from tab_test where state=1061 and time date_sub(now(), INTERVAL 30 minute);

?update tab_test state=1064,time=now() where id in(……);

mysql 死鎖排查

一、show ENGINE INNODB status

查看死鎖位置，分析。

二、

首先解決死鎖可以從死鎖發生的條件入手，最容易解決的就是更改獲取資源的順序；

其次是避免長事務，讓事務執行的時間儘可能少，讓事務的覆蓋範圍儘可能小，長事務會導致並發度降低，且會有更多的SQL查詢延遲；

給整個方法加事務是否是必須的？可以不加事務的盡量不加。

解決一次mysql死鎖問題

多線程開啟事務處理。每個事務有多個update操作和一個insert操作（都在同一張表）。

默認隔離級別：Repeatable Read

只有hotel_id=2和hotel_id=11111的數據

邏輯刪除原有數據

插入新的數據

根據現有數據情況，update的時候沒有數據被更新

報了非常多一樣的錯

發現居然有死鎖。

根據常識考慮，我每個線程（事務）更新的數據都不衝突，為什麼會產生死鎖？

帶着這個問題，打印mysql最近一次的死鎖信息

show engine innodb status

顯示如下

發現事務1在等待一個鎖

事務2也在等待一個鎖

而且事物2持有了事物1需要的鎖

關於鎖的描述，出現了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂說明了什麼？說明我關於mysql的鎖相關的知識儲備還不夠。那就開始調查mysql的鎖相關知識。

通過搜索引擎，

鎖的持有兼容程度如下表

那麼再回到死鎖日誌，可以知道 :

事務1正在獲取插入意向鎖

事務2正在獲取插入意向鎖，持有排他gap鎖

再看我們上面的鎖兼容表格，可以知道， gap lock和insert intention lock是不兼容的

那麼就可以推斷出：事務1持有gap lock，等待事務2的insert intention lock釋放；事務2持有gap lock，等待事務1的insert intention lock釋放，從而導致死鎖。

那麼新的問題就來了，事務1的intention lock 為什麼會和事務2的gap lock 有交集，或者說，事務1要插入的數據的位置為什麼會被事務2給鎖住？

讓我回顧一下gap lock的定義：

間隙鎖，鎖定一個範圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況

那為什麼是gap lock，gap lock到底是基於什麼邏輯鎖的記錄？發現自己相關的知識儲備還不夠。那就開始調查。

調查後發現，噹噹前索引是一個普通索引的時候，會加一個gap lock來防止幻讀，此gap lock 會鎖住一個左開右閉的區間。假設索引為xx_idx（xx_id），數據分布為1,4,6,8,12，當更新xx_id=9的時候，這個時候gap lock的鎖定記錄區間就是(8，12]，也就是鎖住了xxid in (9,10,11,12)的數據，當有其他事務要插入xxid in (9,10,11,12)的數據時，就會處於等待獲取鎖的狀態。

ps：當前索引不是普通索引，而且是唯一索引等其他情況，請參考下面資料

MySQL 加鎖處理分析

回到我自己的案例中，重新屢一下事務1的執行過程：

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關係這段sql會獲取一個gap lock，範圍(2,11111]

這段sql會獲取一個insert intention lock （waiting）

再看事務2的執行過程

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關係這段sql也會獲取一個gap lock，範圍也是(2,11111]（根據前面的知識，gap lock之間會互相兼容，可以一起持有鎖的）

這段sql也會獲取一個insert intention lock （waiting）

看到這裡，基本也就破案了。因為普通索引的關係，事務1和事務2的gap lock的覆蓋範圍太廣，導致其他事務無法插入數據。

重新梳理一下：

所以從結果來看，一堆事務被回滾，只有10007數據被更新成功

gap lock 導致了並發處理的死鎖

在mysql默認的事務隔離級別（repeatable read）下，無法避免這種情況。只能把並發處理改成同步處理。或者從業務層面做處理。

共享鎖、排他鎖、意向共享、意向排他

record lock、gap lock、next key lock、insert intention lock