分布式mysql 怎么防止死鎖的

數(shù)據(jù)庫系統(tǒng)實現(xiàn)了各種死鎖檢測和死鎖超時機制，越復(fù)雜的系統(tǒng)，比如InnoDB存儲引擎，越能檢測到死鎖的循環(huán)依賴，并立即返回一個錯誤。

創(chuàng)新互聯(lián)公司基于分布式IDC數(shù)據(jù)中心構(gòu)建的平臺為眾多戶提供成都西云數(shù)據(jù)中心 四川大帶寬租用 成都機柜租用 成都服務(wù)器租用。

解決一次mysql死鎖問題

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

默認隔離級別：Repeatable Read

只有hotel_id=2和hotel_id=11111的數(shù)據(jù)

邏輯刪除原有數(shù)據(jù)

插入新的數(shù)據(jù)

根據(jù)現(xiàn)有數(shù)據(jù)情況，update的時候沒有數(shù)據(jù)被更新

報了非常多一樣的錯

發(fā)現(xiàn)居然有死鎖。

根據(jù)常識考慮，我每個線程（事務(wù)）更新的數(shù)據(jù)都不沖突，為什么會產(chǎn)生死鎖？

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

show engine innodb status

顯示如下

發(fā)現(xiàn)事務(wù)1在等待一個鎖

事務(wù)2也在等待一個鎖

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

關(guān)于鎖的描述，出現(xiàn)了 lock_mode ， gap before rec ， insert intention 等字眼，看不懂說明了什么？說明我關(guān)于mysql的鎖相關(guān)的知識儲備還不夠。那就開始調(diào)查mysql的鎖相關(guān)知識。

通過搜索引擎，

鎖的持有兼容程度如下表

那么再回到死鎖日志，可以知道 :

事務(wù)1正在獲取插入意向鎖

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

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

那么就可以推斷出： 事務(wù)1持有g(shù)ap lock，等待事務(wù)2的insert intention lock釋放；事務(wù)2持有g(shù)ap lock，等待事務(wù)1的insert intention lock釋放，從而導(dǎo)致死鎖。

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

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

間隙鎖，鎖定一個范圍，但不包括記錄本身。GAP鎖的目的，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況

那為什么是gap lock，gap lock到底是基于什么邏輯鎖的記錄？發(fā)現(xiàn)自己相關(guān)的知識儲備還不夠。那就開始調(diào)查。

調(diào)查后發(fā)現(xiàn)，當(dāng)當(dāng)前索引是一個 普通索引 的時候，會加一個gap lock來防止幻讀， 此gap lock 會鎖住一個左開右閉的區(qū)間。 假設(shè)索引為xx_idx（xx_id），數(shù)據(jù)分布為1,4,6,8,12，當(dāng)更新xx_id=9的時候，這個時候gap lock的鎖定記錄區(qū)間就是(8，12]，也就是鎖住了xxid in (9,10,11,12)的數(shù)據(jù)，當(dāng)有其他事務(wù)要插入xxid in (9,10,11,12)的數(shù)據(jù)時，就會處于等待獲取鎖的狀態(tài)。

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

MySQL 加鎖處理分析

回到我自己的案例中，重新屢一下事務(wù)1的執(zhí)行過程：

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

的關(guān)系 這段sql會獲取一個gap lock，范圍(2,11111]

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

再看事務(wù)2的執(zhí)行過程

因為普通索引

KEY hotel_date_idx ( hotel_id , rate_date )

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

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

看到這里，基本也就破案了。因為普通索引的關(guān)系，事務(wù)1和事務(wù)2的gap lock的覆蓋范圍太廣，導(dǎo)致其他事務(wù)無法插入數(shù)據(jù)。

重新梳理一下：

所以從結(jié)果來看，一堆事務(wù)被回滾，只有10007數(shù)據(jù)被更新成功

gap lock 導(dǎo)致了并發(fā)處理的死鎖

在mysql默認的事務(wù)隔離級別（repeatable read）下，無法避免這種情況。只能把并發(fā)處理改成同步處理?；蛘邚臉I(yè)務(wù)層面做處理。

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

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

show engine innodb status

MySQL數(shù)據(jù)庫表鎖定的幾種方法實現(xiàn)

如果兩個程序都向表中寫數(shù)據(jù)顯然會造成很大的麻煩，甚至?xí)幸馔馇闆r發(fā)生。如果表正由一個程序?qū)懭?，同時進行讀取的另一個程序也會產(chǎn)生混亂的結(jié)果。

鎖定表的方法

防止客戶機的請求互相干擾或者服務(wù)器與維護程序相互干擾的方法主要有多種。如果你關(guān)閉數(shù)據(jù)庫，就可以保證服務(wù)器

和myisamchk和isamchk之間沒有交互作用。但是停止服務(wù)器的運行并不是一個好注意，因為這樣做會使得沒有故障的數(shù)據(jù)庫和表也不可用。本節(jié)主

要討論的過程，是避免服務(wù)器和myisamchk或isamchk之間的交互作用。實現(xiàn)這種功能的方法是對表進行鎖定。

服務(wù)器由兩種表的鎖定方法：

1.內(nèi)部鎖定

內(nèi)部鎖定可以避免客戶機的請求相互干擾——例如，避免客戶機的SELECT查詢被另一個客戶機的UPDATE查詢所干擾。也可以利用內(nèi)部鎖定機制防止服務(wù)器在利用myisamchk或isamchk檢查或修復(fù)表時對表的訪問。

語法：鎖定表：LOCK TABLES

tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解鎖表：UNLOCKTABLESLOCKTABLES為當(dāng)前線程鎖定表。UNLOCK TABLES釋放被當(dāng)前線程持有的任何鎖。當(dāng)線程發(fā)出另外一個LOCK

TABLES時，或當(dāng)服務(wù)器的連接被關(guān)閉時，當(dāng)前線程鎖定的所有表自動被解鎖。

如果一個線程獲得在一個表上的一個READ鎖，該線程(和所有其他線程)只能從表中讀。如果一個線程獲得一個表上的一個WRITE鎖，那么只有持鎖的線程READ或WRITE表，其他線程被阻止。

每個線程等待(沒有超時)直到它獲得它請求的所有鎖。

WRITE鎖通常比READ鎖有更高的優(yōu)先級，以確保更改盡快被處理。這意味著，如果一個線程獲得READ鎖，并且然后另外一個線程請求一個WRITE鎖,

隨后的READ鎖請求將等待直到WRITE線程得到了鎖并且釋放了它。

顯然對于檢查，你只需要獲得讀鎖。再者鐘情跨下，只能讀取表，但不能修改它，因此他也允許其它客戶機讀取表。對于修復(fù)，你必須獲得些所以防止任何客戶機在你對表進行操作時修改它。

2.外部鎖定

服務(wù)器還可以使用外部鎖定(文件級鎖)來防止其它程序在服務(wù)器使用表時修改文件。通常，在表的檢查操作中服務(wù)器

將外部鎖定與myisamchk或isamchk作合使用。但是，外部鎖定在某些系統(tǒng)中是禁用的，因為他不能可靠的進行工作。對運行myisamchk或

isamchk所選擇的過程取決于服務(wù)器是否能使用外部鎖定。如果不使用，則必修使用內(nèi)部鎖定協(xié)議。

如果服務(wù)器用--skip-locking選項運行，則外部鎖定禁用。該選項在某些系統(tǒng)中是缺省的，如Linux?？梢酝ㄟ^運行mysqladmin

variables命令確定服務(wù)器是否能夠使用外部鎖定。檢查skip_locking變量的值并按以下方法進行：

◆

如果skip_locking為off，則外部鎖定有效您可以繼續(xù)并運行人和一個實用程序來檢查表。服務(wù)器和實用程序?qū)⒑献鲗Ρ磉M行訪問。但是，運行任何

一個實用程序之前，應(yīng)該使用mysqladmin flush-tables。為了修復(fù)表，應(yīng)該使用表的修復(fù)鎖定協(xié)議。

◆

如果skip_locaking為on，則禁用外部鎖定，所以在myisamchk或isamchk檢查修復(fù)表示服務(wù)器并不知道，最好關(guān)閉服務(wù)器。如果堅

持是服務(wù)器保持開啟狀態(tài)，月確保在您使用此表示沒有客戶機來訪問它。

mysql怎么避免表鎖

鎖表一般是長時間占用表導(dǎo)致的，

試著使SELECT語句運行得更快；你可能必須創(chuàng)建一些摘要(summary)表做到這點。

用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優(yōu)先級。在這種情況下，在先前情形的最后的SELECT語句將在INSERT語句前執(zhí)行。

你可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優(yōu)先級。

為max_write_lock_count指定一個低值來啟動mysqld使得在一定數(shù)量的WRITE鎖定后給出READ鎖定。

通過使用SQL命令：SET SQL_LOW_PRIORITY_UPDATES=1，你可從一個特定線程指定所有的更改應(yīng)該由用低優(yōu)先級完成

MySQL從入門到精通(九) MySQL鎖，各種鎖

鎖是計算機協(xié)調(diào)多個進程或線程并發(fā)訪問某一資源的機制，在數(shù)據(jù)庫中，除傳統(tǒng)的計算資源（CPU、RAM、I/O）爭用外，數(shù)據(jù)也是一種供許多用戶共享的資源，如何保證數(shù)據(jù)并發(fā)訪問的一致性，有效性是所有數(shù)據(jù)庫必須解決的一個問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素，從這個角度來說，鎖對數(shù)據(jù)庫而言是尤其重要，也更加復(fù)雜。MySQL中的鎖，按照鎖的粒度分為:1、全局鎖，就鎖定數(shù)據(jù)庫中的所有表。2、表級鎖，每次操作鎖住整張表。3、行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)。

全局鎖就是對整個數(shù)據(jù)庫實例加鎖，加鎖后整個實例就處于只讀狀態(tài)，后續(xù)的DML的寫語句，DDL語句，已經(jīng)更新操作的事務(wù)提交語句都將阻塞。其典型的使用場景就是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。但是對數(shù)據(jù)庫加全局鎖是有弊端的，如在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)會受影響，第二如果是在從庫上備份，那么在備份期間從庫不能執(zhí)行主庫同步過來的二進制日志，會導(dǎo)致主從延遲。

解決辦法是在innodb引擎中，備份時加上--single-transaction參數(shù)來完成不加鎖的一致性數(shù)據(jù)備份。

添加全局鎖: flush tables with read lock; 解鎖 unlock tables。

表級鎖,每次操作會鎖住整張表.鎖定粒度大,發(fā)送鎖沖突的概率最高,并發(fā)讀最低,應(yīng)用在myisam、innodb、BOB等存儲引擎中。表級鎖分為: 表鎖、元數(shù)據(jù)鎖(meta data lock, MDL)和意向鎖。

表鎖又分為: 表共享讀鎖 read lock、表獨占寫鎖write lock

語法: 1、加鎖 lock tables 表名 ... read/write

2、釋放鎖 unlock tables 或者關(guān)閉客戶端連接

注意: 讀鎖不會阻塞其它客戶端的讀，但是會阻塞其它客戶端的寫，寫鎖既會阻塞其它客戶端的讀，又會阻塞其它客戶端的寫。大家可以拿一張表來測試看看。

元數(shù)據(jù)鎖,在加鎖過程中是系統(tǒng)自動控制的，無需顯示使用，在訪問一張表的時候會自動加上，MDL鎖主要作用是維護表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動事務(wù)的時候，不可以對元數(shù)據(jù)進行寫入操作。為了避免DML和DDL沖突，保證讀寫的正確性。

在MySQL5.5中引入了MDL，當(dāng)對一張表進行增刪改查的時候，加MDL讀鎖(共享)；當(dāng)對表結(jié)構(gòu)進行變更操作時，加MDL寫鎖(排他).

查看元數(shù)據(jù)鎖:

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema_metadata_locks;

意向鎖，為了避免DML在執(zhí)行時，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來減少表鎖的檢查。意向鎖分為,意向共享鎖is由語句select ... lock in share mode添加。意向排他鎖ix,由insert，update，delete，select。。。for update 添加。

select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_lock;

行級鎖，每次操作鎖住對應(yīng)的行數(shù)據(jù)，鎖定粒度最小，發(fā)生鎖沖突的概率最高，并發(fā)讀最高，應(yīng)用在innodb存儲引擎中。

innodb的數(shù)據(jù)是基于索引組織的，行鎖是通過對索引上的索引項加鎖來實現(xiàn)的，而不是對記錄加的鎖，對于行級鎖，主要分為以下三類:

1、行鎖或者叫record lock記錄鎖，鎖定單個行記錄的鎖，防止其他事物對次行進行update和delete操作，在RC,RR隔離級別下都支持。

2、間隙鎖Gap lock,鎖定索引記錄間隙(不含該記錄)，確保索引記錄間隙不變，防止其他事物在這個間隙進行insert操作，產(chǎn)生幻讀，在RR隔離級別下都支持。

3、臨鍵鎖Next-key-lock,行鎖和間隙鎖組合，同時鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙Gap,在RR隔離級別下支持。

innodb實現(xiàn)了以下兩種類型的行鎖

1、共享鎖 S: 允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。

2、排他鎖 X: 允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。

insert 語句 排他鎖 自動添加的

update語句 排他鎖 自動添加

delete 語句 排他鎖 自動添加

select 正常查詢語句 不加鎖 。。。

select 。。。lock in share mode 共享鎖 需要手動在select 之后加lock in share mode

select 。。。for update 排他鎖 需要手動在select之后添加for update

默認情況下，innodb在repeatable read事務(wù)隔離級別運行，innodb使用next-key鎖進行搜索和索引掃描，以防止幻讀。

間隙鎖唯一目的是防止其它事務(wù)插入間隙，間隙鎖可以共存，一個事務(wù)采用的間隙鎖不會阻止另一個事務(wù)在同一間隙上采用的間隙鎖。

MySQL如何設(shè)置避免活鎖的先來先服務(wù)策略？

一、活鎖

如果事務(wù)T1封鎖了數(shù)據(jù)R，事務(wù)T2又請求封鎖R，于是T2等待。T3也請求封鎖R，當(dāng)T1釋放了R上的封鎖之后系統(tǒng)首先批準(zhǔn)了T3的請求，T2仍然等待。然后T4又請求封鎖R，當(dāng)T3釋放了R上的封鎖之后系統(tǒng)又批準(zhǔn)了T4的請求，...，T2有可能永遠等待，這就是活鎖的情形，如圖8.4(a)所示。

避免活鎖的簡單方法是采用先來先服務(wù)的策略。

二、死鎖

如果事務(wù)T1封鎖了數(shù)據(jù)R1，T2封鎖了數(shù)據(jù)R2，然后T1又請求封鎖R2，因T2已封鎖了R2，于是T1等待T2釋放R2上的鎖。接著T2又申請封鎖R1，因T1已封鎖了R1，T2也只能等待T1釋放R1上的鎖。這樣就出現(xiàn)了T1在等待T2，而T2又在等待T1的局面，T1和T2兩個事務(wù)永遠不能結(jié)束，形成死鎖。

1. 死鎖的預(yù)防

在數(shù)據(jù)庫中，產(chǎn)生死鎖的原因是兩個或多個事務(wù)都已封鎖了一些數(shù)據(jù)對象，然后又都請求對已為其他事務(wù)封鎖的數(shù)據(jù)對象加鎖，從而出現(xiàn)死等待。防止死鎖的發(fā)生其實就是要破壞產(chǎn)生死鎖的條件。預(yù)防死鎖通常有兩種方法：

① 一次封鎖法

一次封鎖法要求每個事務(wù)必須一次將所有要使用的數(shù)據(jù)全部加鎖，否則就不能繼續(xù)執(zhí)行。

一次封鎖法雖然可以有效地防止死鎖的發(fā)生，但也存在問題，一次就將以后要用到的全部數(shù)據(jù)加鎖，勢必擴大了封鎖的范圍，從而降低了系統(tǒng)的并發(fā)度。

② 順序封鎖法

順序封鎖法是預(yù)先對數(shù)據(jù)對象規(guī)定一個封鎖順序，所有事務(wù)都按這個順序?qū)嵭蟹怄i。

順序封鎖法可以有效地防止死鎖，但也同樣存在問題。事務(wù)的封鎖請求可以隨著事務(wù)的執(zhí)行而動態(tài)地決定，很難事先確定每一個事務(wù)要封鎖哪些對象，因此也就很難按規(guī)定的順序去施加封鎖。

可見，在操作系統(tǒng)中廣為采用的預(yù)防死鎖的策略并不很適合數(shù)據(jù)庫的特點，因此DBMS在解決死鎖的問題上普遍采用的是診斷并解除死鎖的方法。

2. 死鎖的診斷與解除

① 超時法

如果一個事務(wù)的等待時間超過了規(guī)定的時限，就認為發(fā)生了死鎖。超時法實現(xiàn)簡單，但其不足也很明顯。一是有可能誤判死鎖，事務(wù)因為其他原因使等待時間超過時限，系統(tǒng)會誤認為發(fā)生了死鎖。二是時限若設(shè)置得太長，死鎖發(fā)生后不能及時發(fā)現(xiàn)。

② 等待圖法

事務(wù)等待圖是一個有向圖G=(T,U)。 T為結(jié)點的集合，每個結(jié)點表示正運行的事務(wù)；U為邊的集合，每條邊表示事務(wù)等待的情況。若T1等待T2,則T1、T2之間劃一條有向邊，從T1指向T2。事務(wù)等待圖動態(tài)地反映了所有事務(wù)的等待情況。并發(fā)控制子系統(tǒng)周期性地（比如每隔1分鐘）檢測事務(wù)等待圖，如果發(fā)現(xiàn)圖中存在回路，則表示系統(tǒng)中出現(xiàn)了死鎖。

DBMS的并發(fā)控制子系統(tǒng)一旦檢測到系統(tǒng)中存在死鎖，就要設(shè)法解除。通常采用的方法是選擇一個處理死鎖代價最小的事務(wù)，將其撤消，釋放此事務(wù)持有的所有的鎖，使其它事務(wù)得以繼續(xù)運行下去。當(dāng)然，對撤消的事務(wù)所執(zhí)行的數(shù)據(jù)修改操作必須加以恢復(fù)。

當(dāng)前文章：mysql怎么防止鎖定 mysql死鎖的處理方法
網(wǎng)站地址：http://m.rwnh.cn/article20/doopcco.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站設(shè)計、微信小程序、服務(wù)器托管、網(wǎng)站排名、手機網(wǎng)站建設(shè)、營銷型網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)