MyISAM和MEMORY采用表級(jí)鎖（table-level locking）

BDB采用頁(yè)面鎖（page-leve locking）或表級(jí)鎖，默認(rèn)為頁(yè)面鎖

InnoDB支持行級(jí)鎖（row-level locking）和表級(jí)鎖，默認(rèn)為行級(jí)鎖

各種鎖特點(diǎn)

表級(jí)鎖：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生沖突的概率最高，并發(fā)度最低

行級(jí)鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高

頁(yè)面鎖：開銷和加鎖時(shí)間介于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般

InnoDB存儲(chǔ)引擎的鎖

InnoDB存儲(chǔ)引擎實(shí)現(xiàn)了如下兩種鎖

1、共享鎖（S Lock），允許事務(wù)讀一行數(shù)據(jù)

2、排他鎖（X Lock)，允許事務(wù)更新或者刪除一行數(shù)據(jù)

共享鎖和排他鎖的兼容如下圖所示

一致性的非鎖定讀

一致性的非鎖定行讀（consistent nonlocking read）是指InnoDB存儲(chǔ)引擎通過行多版本控制（multi versioning）的方式來(lái)讀取當(dāng)前執(zhí)行時(shí)間數(shù)據(jù)庫(kù)中行的數(shù)據(jù)。如果讀取的行正在執(zhí)行DELETE、UPDATE操作，這是讀取操作不會(huì)因此而會(huì)等待行上鎖的釋放，相反，InnoDB會(huì)去讀取行的一個(gè)快照數(shù)據(jù)。

之所以稱其為非鎖定讀，因?yàn)椴恍枰却L問的行上X鎖的釋放。快照數(shù)據(jù)是指改行之前版本的數(shù)據(jù)，該實(shí)現(xiàn)是通過undo段來(lái)實(shí)現(xiàn)的。但是在不同事務(wù)隔離級(jí)別下，讀取的方式不同，并不是每個(gè)事務(wù)隔離級(jí)別下讀取的都是一致性讀。

例如：

對(duì)于read committed的事務(wù)隔離級(jí)別，他總是讀取行的最新版本，如果行被鎖定了，則讀取該行版本的最新一個(gè)快照。

對(duì)于repeatable read(innoDB存儲(chǔ)引擎的默認(rèn)隔離級(jí)別),總是讀取事務(wù)開始時(shí)的行數(shù)據(jù)。

 非鎖定讀的機(jī)制大大提高了數(shù)據(jù)讀取的并發(fā)性，在Innodb存儲(chǔ)引擎默認(rèn)設(shè)置下，這是默認(rèn)的讀取方式，但是在某些情況下，可以對(duì)讀進(jìn)行加鎖，比如：

1、顯式對(duì)讀進(jìn)行加鎖，如使用 select --- for update ;select --- lock in share mode

2、在外鍵的插入和更新上，因?yàn)樵谕怄I的插入和更新上，對(duì)于數(shù)據(jù)的隔離性要求較高，在插入前需要掃描父表中的記錄是否存在，所以，在外鍵的插入刪除上，InnoDB會(huì)使用加S鎖的方式來(lái)實(shí)現(xiàn)。

InnoDB鎖的算法

1、Record Lock：?jiǎn)蝹€(gè)行記錄上的鎖

2、Gap Lock：間隙鎖，鎖定一個(gè)范圍，但不包含記錄本身

3、Next-key Lock:Gap Lock+Record Lock，鎖定一個(gè)范圍，并且鎖定記錄本身

Record Lock總是會(huì)去鎖住索引記錄，如果InnoDB存儲(chǔ)引擎表建立的時(shí)候沒有設(shè)置任何一個(gè)索引，這時(shí)InnodB存儲(chǔ)引擎會(huì)使用隱式的主鍵來(lái)進(jìn)行鎖定，在Repeatable Read隔離級(jí)別下，Next-key Lock 算法是默認(rèn)的行記錄鎖定算法。

鎖帶來(lái)的問題

1、丟失更新

如何避免丟失更新：讓事務(wù)變成串行操作，而不是并發(fā)的操作，即對(duì)每個(gè)事務(wù)開始---對(duì)讀取記錄加排他鎖。

2、臟讀

臟讀即一個(gè)事務(wù)可以讀到另一個(gè)事務(wù)中未提交的數(shù)據(jù)，這違反了數(shù)據(jù)庫(kù)的隔離性。

臟讀發(fā)生的條件是需要事務(wù)的隔離級(jí)別為Read uncommitted。

3、不可重復(fù)讀

不可重復(fù)讀與臟讀的區(qū)別是：臟讀是讀到未提交的數(shù)據(jù)，而不可重復(fù)讀讀到的是已經(jīng)提交的數(shù)據(jù)。

一般來(lái)說(shuō)，不可重復(fù)讀是可以接受的，在InnoDB存儲(chǔ)引擎中，通過使用Next-Key Lock算法來(lái)避免不可重復(fù)讀的問題。

值得注意的是，默認(rèn)情況下InnoDB存儲(chǔ)引擎不會(huì)回滾超時(shí)引發(fā)的錯(cuò)誤異常。

死鎖的相關(guān)問題

1、死鎖發(fā)生的條件

互斥條件：一個(gè)資源每次只能被一個(gè)進(jìn)程使用；請(qǐng)求與保持條件：一個(gè)進(jìn)程因請(qǐng)求資源而阻塞時(shí)，對(duì)已獲得的資源保持不放；不剝奪條件:進(jìn)程已獲得的資源，在末使用完之前，不能強(qiáng)行剝奪；循環(huán)等待條件:若干進(jìn)程之間形成一種頭尾相接的循環(huán)等待資源關(guān)系。

2、死鎖檢測(cè)（根據(jù)網(wǎng)上的經(jīng)驗(yàn)）

Innodb檢測(cè)死鎖有兩種情況，一種是滿足循環(huán)等待條件，還有另一種策略：鎖結(jié)構(gòu)超過mysql配置中設(shè)置的最大數(shù)量或鎖的遍歷深度超過設(shè)置的最大深度時(shí)，innodb也會(huì)判斷為死鎖（這是提高性能方面的考慮，避免事務(wù)一次占用太多的資源）。

因循環(huán)等待條件而產(chǎn)生的死鎖只有可能是四種形式：兩張表兩行記錄交叉申請(qǐng)互斥鎖、同一張表則存在主鍵索引鎖沖突、主鍵索引鎖與非聚簇索引鎖沖突、鎖升級(jí)導(dǎo)致的鎖等待隊(duì)列阻塞。

3、死鎖避免（根據(jù)網(wǎng)上的經(jīng)驗(yàn)）

1.如果使用insert…select語(yǔ)句備份表格且數(shù)據(jù)量較大，在單獨(dú)的時(shí)間點(diǎn)操作，避免與其他sql語(yǔ)句爭(zhēng)奪資源，或使用select into outfile加上load data infile代替 insert…select，這樣不僅快，而且不會(huì)要求鎖定
2. 一個(gè)鎖定記錄集的事務(wù)，其操作結(jié)果集應(yīng)盡量簡(jiǎn)短，以免一次占用太多資源，與其他事務(wù)處理的記錄沖突。
3.更新或者刪除表格數(shù)據(jù)，sql語(yǔ)句的where條件都是主鍵或都是索引，避免兩種情況交叉，造成死鎖。對(duì)于where子句較復(fù)雜的情況，將其單獨(dú)通過sql得到后，再在更新語(yǔ)句中使用。
4. sql語(yǔ)句的嵌套表格不要太多，能拆分就拆分，避免占有資源同時(shí)等待資源，導(dǎo)致與其他事務(wù)沖突。
5. 對(duì)定點(diǎn)運(yùn)行腳本的情況，避免在同一時(shí)間點(diǎn)運(yùn)行多個(gè)對(duì)同一表進(jìn)行讀寫的腳本，特別注意加鎖且操作數(shù)據(jù)量比較大的語(yǔ)句。
6.應(yīng)用程序中增加對(duì)死鎖的判斷，如果事務(wù)意外結(jié)束，重新運(yùn)行該事務(wù)，減少對(duì)功能的影響。

4、死鎖解決

1)先執(zhí)行show processlist找到死鎖線程號(hào).然后Kill pid

2)Show innodb status檢查引擎狀態(tài) ,可以看到哪些語(yǔ)句產(chǎn)生死鎖

3)查看information_schema架構(gòu)下的innodb_locks、innodb_trx、innodb_lock_waits等表

 
PS：Mysql死鎖

既然談到死鎖，那附帶地就專門說(shuō)一下。
何為死鎖？
 
死鎖是對(duì)資源的分配和使用不當(dāng)而造成的。是兩個(gè)進(jìn)程爭(zhēng)奪某一資源而出現(xiàn)相互等待的現(xiàn)象。具體的來(lái)講，出現(xiàn)死鎖需要滿足四個(gè)必要條件：
（1）互斥條件：每一個(gè)資源都只能被一個(gè)進(jìn)程使用
（2）請(qǐng)求與保持條件：一個(gè)進(jìn)程因請(qǐng)求資源而阻塞時(shí)，對(duì)已獲得的資源保持不放
（3）不剝奪條件：進(jìn)程已獲得的資源，在末使用完之前，不能強(qiáng)行剝奪。
（4）循環(huán)等待條件：若干進(jìn)程之間形成一種頭尾相接的循環(huán)等待資源關(guān)系。
很顯然，出現(xiàn)死鎖需要兩個(gè)或者兩個(gè)以上的進(jìn)程，換句話說(shuō)，死鎖發(fā)生在并發(fā)的程序中。在Mysql中，由于目前只有InnoDB引擎使用事務(wù)（InnoDB支持鎖），便有了InnoDB和死鎖的曠世基情。
死鎖的檢測(cè)
 
1、通過使用Show innodb status檢查引擎狀態(tài) ,可以看到哪些語(yǔ)句產(chǎn)生deadlock
2、MySQL提供了一個(gè)information_schema，通過查看innodb_locks、innodb_trx、innodb_lock_waits這幾個(gè)表檢測(cè)死鎖。
因循環(huán)等待條件而產(chǎn)生的死鎖只有可能是四種形式：兩張表兩行記錄交叉申請(qǐng)互斥鎖、同一張表則存在主鍵索引鎖沖突、主鍵索引鎖與非聚簇索引鎖沖突、鎖升級(jí)導(dǎo)致的鎖等待隊(duì)列阻塞。

死鎖避免

1.如果使用insert…select語(yǔ)句備份表格且數(shù)據(jù)量較大，在單獨(dú)的時(shí)間點(diǎn)操作，避免與其他sql語(yǔ)句爭(zhēng)奪資源，或使用select into outfile加上load data infile代替 insert…select，這樣不僅快，而且不會(huì)要求鎖定
2. 一個(gè)鎖定記錄集的事務(wù)，其操作結(jié)果集應(yīng)盡量簡(jiǎn)短，以免一次占用太多資源，與其他事務(wù)處理的記錄沖突。
3.更新或者刪除表格數(shù)據(jù)，sql語(yǔ)句的where條件都是主鍵或都是索引，避免兩種情況交叉，造成死鎖。對(duì)于where子句較復(fù)雜的情況，將其單獨(dú)通過sql得到后，再在更新語(yǔ)句中使用。
4. sql語(yǔ)句的嵌套表格不要太多，能拆分就拆分，避免占有資源同時(shí)等待資源，導(dǎo)致與其他事務(wù)沖突。
5. 對(duì)定點(diǎn)運(yùn)行腳本的情況，避免在同一時(shí)間點(diǎn)運(yùn)行多個(gè)對(duì)同一表進(jìn)行讀寫的腳本，特別注意加鎖且操作數(shù)據(jù)量比較大的語(yǔ)句。
6.應(yīng)用程序中增加對(duì)死鎖的判斷，如果事務(wù)意外結(jié)束，重新運(yùn)行該事務(wù)，減少對(duì)功能的影響。

最新評(píng)論