表級(jí)鎖

該鎖會(huì)鎖定整張表，它是MySQL中最基本的鎖策略，并不依賴于存儲(chǔ)引擎(不管你是MySQL的什么存儲(chǔ)引擎，對(duì)于表鎖的策略都是一樣的)，并且表鎖是開銷最小的策略（因?yàn)榱６缺容^大)。由于表級(jí)鎖一次會(huì)將整個(gè)表鎖定，所以可以很好的避免死鎖問(wèn)題。當(dāng)然，鎖的粒度大所帶來(lái)最大的負(fù)面影響就是出現(xiàn)鎖資源爭(zhēng)用的概率也會(huì)最高，導(dǎo)致并發(fā)率大打折扣。

1、表級(jí)別的S鎖，X鎖

InnoDB存儲(chǔ)引擎

在對(duì)某個(gè)表執(zhí)行SELECT、INSERT、DELETE、UPDATE 語(yǔ)句時(shí)，InnoDB存儲(chǔ)引擎是不會(huì)為這個(gè)表添加表級(jí)別的S鎖或者X鎖的。

一般情況下，不會(huì)使用InnoDB存儲(chǔ)引擎提供的表級(jí)別的S鎖和X鎖。只會(huì)在一些特殊情況下，比方說(shuō)崩潰恢復(fù)過(guò)程中用到。

InnoDB存儲(chǔ)引擎下，手動(dòng)添加表t的S鎖或X鎖：

lock tables t read  -- S鎖
lock tables t write  -- X鎖

不過(guò)盡量避免在使用InnoDB存儲(chǔ)引擎的表上使用LOCK TABLES這樣的手動(dòng)鎖表語(yǔ)句，它們并不會(huì)提供什么額外的保護(hù)，只是會(huì)降低并發(fā)能力而已。

MyISAM存儲(chǔ)引擎

MyISAM 的表級(jí)鎖有2種模式，分別為：表共享讀鎖（S鎖） 和 表獨(dú)占寫鎖（X鎖）。

表共享讀鎖（S鎖）：當(dāng)開啟事務(wù)A 獲取表共享讀鎖， 則其他新開啟事務(wù)只能讀取數(shù)據(jù)，不能對(duì)操作的同張表進(jìn)行更新或者插入操作，刪除操作，

表獨(dú)占寫鎖（X鎖）：當(dāng)開啟事務(wù)A 獲取獨(dú)占寫鎖，則其他新開啟的事物 讀取，新增，修改，刪除 等操作會(huì)處于阻塞狀態(tài)， 只到 事務(wù)A 主動(dòng)釋放鎖。

MyISAM存儲(chǔ)引擎下，手動(dòng)添加表t的S鎖或X鎖：

lock tables t read  -- S鎖
lock tables t write  -- X鎖

可通過(guò) show status like 'tables%'; 命令來(lái) 查看 mysql 內(nèi)部表級(jí)鎖定的情況：

2、意向鎖

意向鎖概述

InnoDB支持多粒度鎖(multiple granularity locking)，它允許行級(jí)鎖與表級(jí)鎖共存，而意向鎖就是其中的一種表鎖。

==意向鎖的存在是為了協(xié)調(diào)行鎖和表鎖的關(guān)系，支持多粒度（表鎖與行鎖）的鎖并存。==

意向鎖是一種不與行級(jí)鎖沖突的表級(jí)鎖，這一點(diǎn)非常重要。

意向鎖分為兩種：

• 意向共享鎖（intention shared lock, IS）：事務(wù)有意向?qū)Ρ碇械哪承┬屑庸蚕礞i（S鎖）

select column from table ... lock in share mode; -- 

• 意向排他鎖（intention exclusive lock, IX）：事務(wù)有意向?qū)Ρ碇械哪承┬屑优潘i（X鎖）

select column from table ... for mode; -- 

申請(qǐng)意向鎖的動(dòng)作是數(shù)據(jù)庫(kù)完成的，就是說(shuō)，事務(wù)A申請(qǐng)一行的行鎖的時(shí)候，數(shù)據(jù)庫(kù)會(huì)自動(dòng)先開始申請(qǐng)表的意向鎖，不需要我們程序員使用代碼來(lái)申請(qǐng)。

意向鎖解決的問(wèn)題

事務(wù)A鎖住了表中的一行，讓這一行只能讀，不能寫。之后，事務(wù)B申請(qǐng)整個(gè)表的寫鎖。

如果事務(wù)B申請(qǐng)成功，那么理論上它就能修改表中的任意一行，這與A持有的行鎖是沖突的。

數(shù)據(jù)庫(kù)需要避免這種沖突，就是說(shuō)要讓B的申請(qǐng)被阻塞，直到A釋放了行鎖。于是就有了意向鎖。

事務(wù)B只需檢查表上的意向鎖，發(fā)現(xiàn)表上有意向共享鎖IS，說(shuō)明表中有些行被共享行鎖鎖住了，因此，事務(wù)B申請(qǐng)表的寫鎖會(huì)被阻塞。

在數(shù)據(jù)表的場(chǎng)景中，如果我們給某一行數(shù)據(jù)加上了排它鎖，數(shù)據(jù)庫(kù)會(huì)自動(dòng)給更大一級(jí)的空間，比如數(shù)據(jù)頁(yè)或數(shù)據(jù)表加上意向鎖，告訴其他人這個(gè)數(shù)據(jù)頁(yè)或數(shù)據(jù)表已經(jīng)有人上過(guò)排它鎖了，這樣當(dāng)其他人想要獲取數(shù)據(jù)表排它鎖的時(shí)候，只需要了解是否有人已經(jīng)獲取了這個(gè)數(shù)據(jù)表的意向排他鎖即可。

• 如果事務(wù)想要獲得數(shù)據(jù)表中某些記錄的共享鎖，就需要在數(shù)據(jù)表上添加意向共享鎖。
• 如果事務(wù)想要獲得數(shù)據(jù)表中某些記錄的排他鎖，就需要在數(shù)據(jù)表上添加意向排他鎖。

意向鎖的并發(fā)性

開啟一個(gè)事務(wù)，并給查詢記錄加上X鎖：此時(shí)針對(duì)查詢的記錄還加上了一個(gè)表級(jí)別的共享排它鎖(IX)

再開啟一個(gè)事務(wù)，查詢不同記錄，并給查詢記錄加上X鎖：表級(jí)別的 IX共享排它鎖加鎖成功，因?yàn)閮纱问聞?wù)加的IX是針對(duì)不同的記錄的

結(jié)論:

• InnoDB支持多粒度鎖，特定場(chǎng)景下，行級(jí)鎖可以與表級(jí)鎖共存。
• 意向鎖之間互不排斥，但除了IS與S兼容外，意向鎖會(huì)與共享鎖/排他鎖互斥。
• lX，IS是表級(jí)鎖，不會(huì)和行級(jí)的X，S鎖發(fā)生沖突。只會(huì)和表級(jí)的X，S發(fā)生沖突。
• 意向鎖在保證并發(fā)性的前提下，實(shí)現(xiàn)了行鎖和表鎖共存且滿足事務(wù)隔離性的要求。

3、自增鎖（AUTO-INC鎖）

自增鎖是MySQL一種特殊的鎖，如果表中存在自增字段，當(dāng)向表中插入數(shù)據(jù)時(shí)，MySQL便會(huì)自動(dòng)維護(hù)一個(gè)表級(jí)的自增鎖。

在執(zhí)行插入語(yǔ)句時(shí)就在表級(jí)別加一個(gè)AUTO-INC鎖，然后為每條待插入記錄的AUTO_INCREMENT修飾的列分配遞增的值，在該語(yǔ)句執(zhí)行結(jié)束后，再把AUTO-INC鎖釋放掉。

一個(gè)事務(wù)在持有AUTO-INC鎖的過(guò)程中，其他事務(wù)的插入語(yǔ)句都要被阻塞，可以保證一個(gè)語(yǔ)句中分配的遞增值是連續(xù)的。也正因?yàn)榇?，其并發(fā)性顯然并不高，當(dāng)我們向一個(gè)有AUTO_INCREMENT關(guān)鍵字的主鍵插入值的時(shí)候，每條語(yǔ)句都要對(duì)這個(gè)表鎖進(jìn)行競(jìng)爭(zhēng)，這樣的并發(fā)潛力其實(shí)是很低下的。

所以 innodb 引擎通過(guò)設(shè)置 innodb_autoinc_lock_mode 的值來(lái)提供不同的鎖定機(jī)制，來(lái)顯著提高sQL語(yǔ)句的可伸縮性和性能。

innodb_autoinc_lock_mode有三個(gè)取值：0,1,2

tradition（innodb_autoinc_lock_mode = 0) 模式：==傳統(tǒng)==鎖定模式

• 它提供了一個(gè)向后兼容的能力
• 在這一模式下，所有類型的insert語(yǔ)句都會(huì)在語(yǔ)句開始的時(shí)候得到一個(gè)表級(jí)的auto_inc鎖，用于插入具有auto_inc列的表，在語(yǔ)句結(jié)束的時(shí)候才釋放這把鎖，注意，這里說(shuō)的是語(yǔ)句級(jí)而不是事務(wù)級(jí)的，一個(gè)事務(wù)可能包涵有一個(gè)或多個(gè)語(yǔ)句。
• 它能保證值分配的可預(yù)見性，與連續(xù)性，可重復(fù)性，這個(gè)也就保證了insert語(yǔ)句在復(fù)制到slave的時(shí)候還能生成和master那邊一樣的值(它保證了基于語(yǔ)句復(fù)制的安全)。
• 由于在這種模式下auto_inc鎖一直要保持到語(yǔ)句的結(jié)束，所以這個(gè)就影響到了并發(fā)的插入。因?yàn)槭潜砑?jí)鎖，當(dāng)在同一時(shí)間多個(gè)事務(wù)中執(zhí)行 insert 的時(shí)候，對(duì)于auto_inc鎖的爭(zhēng)奪會(huì)限制并發(fā)能力。

consecutive（innodb_autoinc_lock_mode = 1) 模式：==連續(xù)==鎖定模式

• 在MySQL8.0之前，==連續(xù)==鎖定模式是默認(rèn)的添加模式
• 這一模式在simple insert （要插入的行數(shù)已知）做了優(yōu)化，由于simple insert一次性插入值的個(gè)數(shù)可以立馬得到確定，所以mysql可以一次生成幾個(gè)連續(xù)的值，用于這個(gè)insert語(yǔ)句；總的來(lái)說(shuō)這個(gè)對(duì)復(fù)制也是安全的 (它保證了基于語(yǔ)句復(fù)制的安全)
• 這一模式也是mysql的默認(rèn)模式，這個(gè)模式的好處是auto_inc鎖不要一直保持到語(yǔ)句的結(jié)束，只要語(yǔ)句得到了相應(yīng)的值后就可以提前釋放鎖

interleaved（innodb_autoinc_lock_mode = 2) 模式：==交錯(cuò)==鎖定模式

• 在MySQL8.0，==交錯(cuò)==鎖定模式是默認(rèn)的添加模式
• 由于這個(gè)模式下所有insert語(yǔ)句都不回使用表級(jí)auto_inc鎖，并且可以同時(shí)執(zhí)行多個(gè)語(yǔ)句，這是最快和最可擴(kuò)展的鎖定模式，所以這個(gè)模式下的性能是最好的；但是它也有一個(gè)問(wèn)題，由于多個(gè)語(yǔ)句可以同時(shí)生成數(shù)字，為任何給定語(yǔ)句插入的行生成的值可能是不連續(xù)的。

4、元數(shù)據(jù)鎖（MDL鎖）

在對(duì)某個(gè)表執(zhí)行一些諸如ALTER TABLE、DROP TABLE 這類的 DDL 語(yǔ)句時(shí)，其他事務(wù)對(duì)這個(gè)表并發(fā)執(zhí)行諸如 SELECT、INSERT、DELETE、UPDATE的語(yǔ)句會(huì)發(fā)生阻塞。

同理，某個(gè)事務(wù)中對(duì)某個(gè)表執(zhí)行SELECT、INSERT、DELETE、UPDATE語(yǔ)句時(shí)，在其他會(huì)話中對(duì)這個(gè)表執(zhí)行DDL語(yǔ)句也會(huì)發(fā)生阻塞。

這個(gè)過(guò)程其實(shí)是通過(guò)在server層使用一種稱之為元數(shù)據(jù)鎖(英文名: Metadata Locks，簡(jiǎn)稱MDL）結(jié)構(gòu)來(lái)實(shí)現(xiàn)的。

MySQL5.5引入了meta data lock，簡(jiǎn)稱MDL鎖，屬于表鎖范疇。MDL的作用是，保證讀寫的正確性。比如，如果一個(gè)查詢正在遍歷一個(gè)表中的數(shù)據(jù)，而執(zhí)行期間另一個(gè)線程對(duì)這個(gè)表結(jié)構(gòu)做變更，增加了一列，那么查詢線程拿到的結(jié)果跟表結(jié)構(gòu)對(duì)不上，肯定是不行的。

因此，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加MDL讀鎖；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加MDL寫鎖。

==讀鎖之間不互斥，因此你可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查。讀鎖和寫鎖之間、寫鎖和寫鎖之間是互斥的==，用來(lái)保證變更表結(jié)構(gòu)操作的安全性，解決了 DML 和 DDL 操作之間的一致性問(wèn)題。MDL鎖不需要顯式使用，在訪問(wèn)一個(gè)表的時(shí)候會(huì)被自動(dòng)加上。

以上就是MySQL表級(jí)鎖使用說(shuō)明的詳細(xì)內(nèi)容，更多關(guān)于MySQL 表級(jí)鎖的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

最新評(píng)論