MyIsam與InnoDB引擎的鎖實(shí)現以及避免死鎖產(chǎn)生的方

這篇文章主要講解了“MyIsam與InnoDB引擎的鎖實(shí)現以及避免死鎖產(chǎn)生的方法”，文中的講解內容簡(jiǎn)單清晰，易于學(xué)習與理解，下面請大家跟著(zhù)小編的思路慢慢深入，一起來(lái)研究和學(xué)習“MyIsam與InnoDB引擎的鎖實(shí)現以及避免死鎖產(chǎn)生的方法”吧！

三類(lèi)常見(jiàn)引擎：

MyIsam ：不支持事務(wù)，不支持外鍵，所以訪(fǎng)問(wèn)速度快。鎖機制是表鎖，支持全文索引

InnoDB ：支持事務(wù)、支持外鍵，所以對比MyISAM，InnoDB的處理效率差一些，并要占更多的磁盤(pán)空間保留數據和索引。鎖機制是行鎖，不支持全文索引

Memory：數據是存放在內存中的，默認哈希索引，非常適合存儲臨時(shí)數據，關(guān)閉后，數據會(huì )丟失掉。

如何選擇存儲引擎：

MyISAM：應用是以讀操作和插入操作為主，只有很少的更新和刪除操作，并且對事務(wù)的完整性、并發(fā)性要求不是很高。

InnoDB：用于事務(wù)處理應用程序，支持外鍵，如果應用對事務(wù)的完整性有比較高的要求，在并發(fā)條件下要求數據的一致性。更新刪除等頻繁（InnoDB可以有效的降低由于刪除和更新導致的鎖定），對于數據準確性要求比較高的，此引擎適合。

Memory：通常用于更新不太頻繁的小表，用以快速得到訪(fǎng)問(wèn)結果。

Mysql中的鎖

如果熟悉多線(xiàn)程，那么對鎖肯定是有概念的，鎖是計算機協(xié)調多個(gè)進(jìn)程或線(xiàn)程對某一資源并發(fā)訪(fǎng)問(wèn)的機制。

Mysql中的鎖分為表鎖和行鎖：

顧名思義，表鎖就是鎖住一張表，而行鎖就是鎖住一行。

表鎖的特點(diǎn)：開(kāi)銷(xiāo)小，不會(huì )產(chǎn)生死鎖，發(fā)生鎖沖突的概率高，并且并發(fā)度低。

行鎖的特點(diǎn)：開(kāi)銷(xiāo)大，會(huì )產(chǎn)生死鎖，發(fā)生鎖沖突的概率低，并發(fā)度高。

因此MyISAM和Memory引擎采用的是表鎖，而InnoDB存儲引擎采用的是行鎖。

MyISAM的鎖機制：

分為共享讀鎖和獨占寫(xiě)鎖。

讀鎖是：當某一進(jìn)程對某張表進(jìn)行讀操作時(shí)（select），其他線(xiàn)程也可以讀，但是不能寫(xiě)。簡(jiǎn)單的理解就是，我讀的時(shí)候你不能寫(xiě)。

寫(xiě)鎖是：當某一進(jìn)程對某種表某張表的寫(xiě)時(shí)（insert，update，，delete），其他線(xiàn)程不能寫(xiě)也不能讀?？梢岳斫鉃?，我寫(xiě)的時(shí)候，你不能讀，也不能寫(xiě)。

因此MyISAM的讀操作和寫(xiě)操作，以及寫(xiě)操作之間是串行的！MyISAM在執行讀寫(xiě)操作的時(shí)候會(huì )自動(dòng)給表加相應的鎖（也就是說(shuō)不用顯示的使用lock table命令），MyISAM總是一次獲得SQL語(yǔ)句所需要的全部鎖，這也是MyISAM不會(huì )出現死鎖的原因。

下面分別舉關(guān)于寫(xiě)鎖和讀鎖的例子：

寫(xiě)鎖：

讀鎖例子如下：

并發(fā)插入

剛說(shuō)到Mysql在插入和修改的時(shí)候都是串行的，但是MyISAM也支持查詢(xún)和插入的并發(fā)操作。

MyISAM中有一個(gè)系統變量concurrent_insert（默認為1），用以控制并發(fā)插入（用戶(hù)在表尾插入數據）行為。

當concurrent_insert為0時(shí)，不允許并發(fā)插入。

當concurrent_insert為1時(shí)，如果表中沒(méi)有空洞（中間沒(méi)有被刪除的行），MyISAM允許一個(gè)進(jìn)程在讀表的同時(shí)，另一個(gè)進(jìn)程從表尾插入記錄。

當concurrent_insert為2時(shí)，無(wú)論MyISAM表中有沒(méi)有空洞，都可以在末尾插入記錄

需要注意的：

并發(fā)插入是解決對同一表中的查詢(xún)和插入的鎖爭用。

如果對有空洞的表進(jìn)行并發(fā)插入會(huì )產(chǎn)生碎片，所以在空閑時(shí)可以利用optimize table命令回收因刪除記錄產(chǎn)生的空洞。

鎖調度

在MyISAM中當一個(gè)進(jìn)程請求某張表的讀鎖，而另一個(gè)進(jìn)程同時(shí)也請求寫(xiě)鎖，Mysql會(huì )先讓后者獲得寫(xiě)鎖。即使讀請求比寫(xiě)請求先到達鎖等待隊列，寫(xiě)鎖也會(huì )插入到讀鎖之前。

因為Mysql總是認為寫(xiě)請求一般比讀請求重要，這也就是MyISAM不太適合有大量的讀寫(xiě)操作的應用的原因，因為大量的寫(xiě)請求會(huì )讓查詢(xún)操作很難獲取到讀鎖，有可能永遠阻塞。

處理辦法：

1、指定Insert、update、delete語(yǔ)句的low_priority屬性，降低其優(yōu)先級。

2、指定啟動(dòng)參數low-priority-updates，使得MyISAM默認給讀請求優(yōu)先的權利。

3、執行命令set low_priority_updates=1，使該連接發(fā)出的請求降低。

4、指定max_write_lock_count設置一個(gè)合適的值，當寫(xiě)鎖達到這個(gè)值后，暫時(shí)降低寫(xiě)請求的優(yōu)先級，讓讀請求獲取鎖。

但是上面的處理辦法造成的原因就是當遇到復雜的查詢(xún)語(yǔ)句時(shí)，寫(xiě)請求可能很難獲取到鎖，這是一個(gè)很糾結的問(wèn)題，所以我們一般避免使用復雜的查詢(xún)語(yǔ)句，如果如法避免，則可以再數據庫空閑階段（深夜）執行。

我們知道mysql在以前，存儲引擎默認是MyISAM，但是隨著(zhù)對事務(wù)和并發(fā)的要求越來(lái)越高，便引入了InnoDB引擎，它具有支持事務(wù)安全等一系列特性。

InnoDB鎖模式

InnoDB實(shí)現了兩種類(lèi)型的行鎖。

共享鎖（S）：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同的數據集的排他鎖。

排他鎖（X）：允許獲得排他鎖的事務(wù)更新數據，但是組織其他事務(wù)獲得相同數據集的共享鎖和排他鎖。

可以這么理解：

共享鎖就是我讀的時(shí)候，你可以讀，但是不能寫(xiě)。排他鎖就是我寫(xiě)的時(shí)候，你不能讀也不能寫(xiě)。其實(shí)就是MyISAM的讀鎖和寫(xiě)鎖，但是針對的對象不同了而已。

除此之外InnoDB還有兩個(gè)表鎖：

意向共享鎖（IS）：表示事務(wù)準備給數據行加入共享鎖，也就是說(shuō)一個(gè)數據行加共享鎖前必須先取得該表的IS鎖

意向排他鎖（IX）：類(lèi)似上面，表示事務(wù)準備給數據行加入排他鎖，說(shuō)明事務(wù)在一個(gè)數據行加排他鎖前必須先取得該表的IX鎖。

InnoDB行鎖模式兼容列表：

注意：

當一個(gè)事務(wù)請求的鎖模式與當前的鎖兼容，InnoDB就將請求的鎖授予該事務(wù)；反之如果請求不兼容，則該事務(wù)就等待鎖釋放。

意向鎖是InnoDB自動(dòng)加的，不需要用戶(hù)干預。

對于insert、update、delete，InnoDB會(huì )自動(dòng)給涉及的數據加排他鎖（X）；對于一般的Select語(yǔ)句，InnoDB不會(huì )加任何鎖，事務(wù)可以通過(guò)以下語(yǔ)句給顯示加共享鎖或排他鎖。

共享鎖：select * from table_name where …..lock in share mode

排他鎖：select * from table_name where …..for update

加入共享鎖的例子：

利用select ….for update加入排他鎖

鎖的實(shí)現方式：

InnoDB行鎖是通過(guò)給索引項加鎖實(shí)現的，如果沒(méi)有索引，InnoDB會(huì )通過(guò)隱藏的聚簇索引來(lái)對記錄加鎖。

也就是說(shuō)：如果不通過(guò)索引條件檢索數據，那么InnoDB將對表中所有數據加鎖，實(shí)際效果跟表鎖一樣。

行鎖分為三種情形：

Record lock ：對索引項加鎖，即鎖定一條記錄。

Gap lock：對索引項之間的‘間隙’、對第一條記錄前的間隙或最后一條記錄后的間隙加鎖，即鎖定一個(gè)范圍的記錄，不包含記錄本身

Next-key Lock：鎖定一個(gè)范圍的記錄并包含記錄本身（上面兩者的結合）。

注意：InnoDB默認級別是repeatable-read級別，所以下面說(shuō)的都是在RR級別中的。

之前一直搞不懂Gap Lock和Next-key Lock的區別，直到在網(wǎng)上看到一句話(huà)豁然開(kāi)朗，希望對各位有幫助。

Next-Key Lock是行鎖與間隙鎖的組合，這樣，當InnoDB掃描索引記錄的時(shí)候，會(huì )首先對選中的索引記錄加上行鎖（Record Lock），再對索引記錄兩邊的間隙加上間隙鎖（Gap Lock）。如果一個(gè)間隙被事務(wù)T1加了鎖，其它事務(wù)是不能在這個(gè)間隙插入記錄的。

干巴巴的說(shuō)沒(méi)意思，我們來(lái)看看具體實(shí)例：

假設我們有一張表：

+——+———+

| id | age  |

+——+———+

|  1 |    3 |

|  2 |    6 |

|  3 |    9 |

+——+———+

表結構如下：

CREATE TABLE test (
 id int(11) NOT NULL AUTO_INCREMENT,
 age int(11) DEFAULT NULL,
 PRIMARY KEY (id),
 KEY keyname (age)
) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=gbk ;

這樣我們age段的索引就分為

(negative infinity, 3],

(3,6],

(6,9],

(9,positive infinity)；

我們來(lái)看一下幾種情況：

1、當事務(wù)A執行以下語(yǔ)句：

mysql> select * from fenye where age=6for update ;

不僅使用行鎖鎖住了相應的數據行，同時(shí)也在兩邊的區間，（5,6]和（6，9] 都加入了gap鎖。

這樣事務(wù)B就無(wú)法在這個(gè)兩個(gè)區間insert進(jìn)新數據,但是事務(wù)B可以在兩個(gè)區間外的區間插入數據。

2、當事務(wù)A執行

select * from fenye where age=7 for update ;

那么就會(huì )給(6,9]這個(gè)區間加鎖，別的事務(wù)無(wú)法在此區間插入或更新數據。

3、如果查詢(xún)的數據不再范圍內，

比如事務(wù)A執行 select * from fenye where age=100 for update ;

那么加鎖區間就是(9,positive infinity)。

小結：

行鎖防止別的事務(wù)修改或刪除，GAP鎖防止別的事務(wù)新增，行鎖和GAP鎖結合形成的的Next-Key鎖共同解決了RR級別在寫(xiě)數據時(shí)的幻讀問(wèn)題。

何時(shí)在InnoDB中使用表鎖：

InnoDB在絕大部分情況會(huì )使用行級鎖，因為事務(wù)和行鎖往往是我們選擇InnoDB的原因，但是有些情況我們也考慮使用表級鎖。

1、當事務(wù)需要更新大部分數據時(shí)，表又比較大，如果使用默認的行鎖，不僅效率低，而且還容易造成其他事務(wù)長(cháng)時(shí)間等待和鎖沖突。

2、事務(wù)比較復雜，很可能引起死鎖導致回滾。

死鎖：

我們說(shuō)過(guò)MyISAM中是不會(huì )產(chǎn)生死鎖的，因為MyISAM總是一次性獲得所需的全部鎖，要么全部滿(mǎn)足，要么全部等待。而在InnoDB中，鎖是逐步獲得的，就造成了死鎖的可能。

在上面的例子中我們可以看到，當兩個(gè)事務(wù)都需要獲得對方持有的鎖才能夠繼續完成事務(wù)，導致雙方都在等待，產(chǎn)生死鎖。

發(fā)生死鎖后，InnoDB一般都可以檢測到，并使一個(gè)事務(wù)釋放鎖回退，另一個(gè)獲取鎖完成事務(wù)。

避免死鎖：

有多種方法可以避免死鎖，這里只介紹常見(jiàn)的三種：

1、如果不同程序會(huì )并發(fā)存取多個(gè)表，盡量約定以相同的順序訪(fǎng)問(wèn)表，可以大大降低死鎖機會(huì )。

2、在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；

3、對于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級鎖定顆粒度，通過(guò)表級鎖定來(lái)減少死鎖產(chǎn)生的概率；