mysql中的鎖都有哪些

MySQL 中有哪些鎖？

創(chuàng)新互聯(lián)是一家專(zhuān)注于做網(wǎng)站、成都網(wǎng)站建設(shè)與策劃設(shè)計(jì),麒麟網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專(zhuān)注于網(wǎng)站建設(shè)10年,網(wǎng)設(shè)計(jì)領(lǐng)域的專(zhuān)業(yè)建站公司;建站業(yè)務(wù)涵蓋:麒麟等地區(qū)。麒麟做網(wǎng)站價(jià)格咨詢(xún):13518219792

數(shù)據(jù)庫(kù)中鎖的設(shè)計(jì)初衷處理并發(fā)問(wèn)題，作為多用戶(hù)共享資源，當(dāng)出現(xiàn)并發(fā)訪(fǎng)問(wèn)的時(shí)候，數(shù)據(jù)庫(kù)需要合理控制資源訪(fǎng)問(wèn)規(guī)則。鎖就是實(shí)現(xiàn)這些訪(fǎng)問(wèn)規(guī)則中的重要數(shù)據(jù)。

鎖的分類(lèi)

根據(jù)加鎖范圍，MySQL 里面的鎖可以分成 全局鎖 、 表級(jí)鎖 、 行鎖 三類(lèi)。

全局鎖

全局鎖，就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖，MySQL 提供了一個(gè)加全局讀鎖的方法，命令是:

Flush tables with read lock (FTWRL)

當(dāng)需要整個(gè)庫(kù)只讀狀態(tài)的時(shí)候，可以使用這個(gè)命令，之后其他線(xiàn)程的：數(shù)據(jù)更新語(yǔ)句（增刪改），數(shù)據(jù)定義語(yǔ)句（建表，修改表結(jié)構(gòu)）和更新事務(wù)的提交語(yǔ)句將會(huì)被阻塞。

全局鎖的使用場(chǎng)景

全局鎖的定型使用場(chǎng)景，做 全庫(kù)邏輯備份 。也就是把整個(gè)庫(kù)每個(gè)表都 Select 出來(lái)，然后存成文本。

如何整個(gè)庫(kù)都只讀，會(huì)有什么問(wèn)題？ 如果你在主庫(kù)上備份，那么在備份期間都不能執(zhí)行更想，業(yè)務(wù)就基本上停擺。 如果在從庫(kù)上備份，那么備份期間從庫(kù)不能執(zhí)行主庫(kù)同步過(guò)來(lái)的 binlog ,會(huì)導(dǎo)致從延遲。 既然要全庫(kù)只讀， 為什么不使用set global readonly=true的方式呢?

readonly 方式也可以讓全庫(kù)進(jìn)入只讀狀態(tài),但我還是會(huì)建議你用FTWRL方式， 主要有兩個(gè)原因：

一是， 在有些系統(tǒng)中， readonly的值會(huì)被用來(lái)做其他邏輯，比如用來(lái)判斷一個(gè)庫(kù)是主庫(kù)還是備庫(kù)。因此，修改global變量的方式影響面更大， 我不建議你使用。 二是， 在異常處理機(jī)制上有差異。如果執(zhí)行FTWRL命令之后由于客戶(hù)端發(fā)生異常斷開(kāi)， 那么MySQL會(huì)自動(dòng)釋放這個(gè)全局鎖， 整個(gè)庫(kù)回到可以正常更新的狀態(tài)。而將整個(gè)庫(kù)設(shè)置為readonly之后， 如果客戶(hù)端發(fā)生異常， 則數(shù)據(jù)庫(kù)就會(huì)一直保持readonly狀態(tài)， 這樣會(huì)導(dǎo)致整個(gè)庫(kù)長(zhǎng)時(shí)間處于不可寫(xiě)狀態(tài)， 風(fēng)險(xiǎn)較高 表級(jí)別鎖

MySQL 里面表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（meta data lok, MDL）。表鎖的語(yǔ)法是 ：

lock tables ... read/write

與 FTWRL 類(lèi)似,可以使用 unlock tables 主動(dòng)釋放鎖，也可以在客戶(hù)端斷開(kāi)的時(shí)候自動(dòng)釋放。需要注意的是，lock tables語(yǔ)法除了會(huì)限制別的線(xiàn)程的讀寫(xiě)外，也限定了本線(xiàn)程接下來(lái)的操作對(duì)象。

MDL 表級(jí)鎖

MDL 不需要顯示使用，在訪(fǎng)問(wèn)一個(gè)表的時(shí)候自動(dòng)加上， MDL 保證讀寫(xiě)的正確性，也就是說(shuō)在查詢(xún)數(shù)據(jù)時(shí)，不允許有其他線(xiàn)程對(duì)這個(gè)表結(jié)構(gòu)做變更。

什么操作會(huì)加 MDL 鎖？

在MySQL 5.5版本中引入了MDL， 當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加 MDL讀鎖 ；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL寫(xiě)鎖 。

讀鎖之間不互斥，因此可以有多個(gè)線(xiàn)程同時(shí)對(duì)一張表增刪改查。 讀寫(xiě)之間、寫(xiě)鎖之間是互斥的，用來(lái)保證變更表結(jié)構(gòu)操作的安全性，如果有兩個(gè)線(xiàn)程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另外一個(gè)執(zhí)行完才能執(zhí)行。 更改表結(jié)構(gòu)要注意哪些？

給一個(gè)表加字段， 或者修改字段， 或者加索引， 需要掃描全表的數(shù)據(jù)。在對(duì)大表操作的時(shí)候， 你肯定會(huì)特別小心， 以免對(duì)線(xiàn)上服務(wù)造成影響。而實(shí)際上， 即使是小表， 操作不慎也會(huì)出問(wèn)題，導(dǎo)致整個(gè)庫(kù)的線(xiàn)程爆滿(mǎn)。

舉個(gè)例子

我們來(lái)看一下下面的操作序列， 假設(shè)表t是一個(gè)小表。

image

session A先啟動(dòng)， 這時(shí)候會(huì)對(duì)表t加一個(gè) MDL讀鎖 。由于session B需要的也是 MDL讀鎖 ， 因此可以正常執(zhí)行。 session C會(huì)被blocked， 是因?yàn)閟ession A的MDL讀鎖還沒(méi)有釋放， 而session C需要MDL寫(xiě)鎖， 因此只能被阻塞，讀寫(xiě)鎖互斥。 如果只有session C自己被阻塞還沒(méi)什么關(guān)系， 但是之后所有要在表t上新申請(qǐng)MDL讀鎖的請(qǐng)求也會(huì)被session C阻塞。前面我們說(shuō)了，所有對(duì)表的增刪改查操作都需要先申請(qǐng)MDL讀鎖， 就都被鎖住， 等于這個(gè)表現(xiàn)在完全不可讀寫(xiě)了。

如果某個(gè)表上的查詢(xún)語(yǔ)句頻繁， 而且客戶(hù)端有重試機(jī)制，也就是說(shuō)超時(shí)后會(huì)再起一個(gè)新session 再請(qǐng)求的話(huà)， 這個(gè) 庫(kù)的線(xiàn)程很快就會(huì)爆滿(mǎn) 。事務(wù)中的MDL鎖， 在語(yǔ)句執(zhí)行開(kāi)始時(shí)申請(qǐng)， 但是語(yǔ)句結(jié)束后并不會(huì)馬上釋放， 而會(huì)等到整個(gè)事務(wù)提交后再釋放。

怎么解決這個(gè) 更改表結(jié)構(gòu)問(wèn)題

比較理想的機(jī)制是， 在alter table語(yǔ)句里面設(shè)定等待時(shí)間， 如果在這個(gè)指定的等待時(shí)間里面能夠拿到MDL寫(xiě)鎖最好， 拿不到也不要阻塞后面的業(yè)務(wù)語(yǔ)句， 先放棄。

ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...

mysql中鎖的類(lèi)型有哪些

全局鎖

顧名思義，全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖。MySQL 提供了一個(gè)加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當(dāng)你需要讓整個(gè)庫(kù)處于只讀狀態(tài)的時(shí)候，可以使用這個(gè)命令，之后其他線(xiàn)程的以下語(yǔ)句會(huì)被阻塞：數(shù)據(jù)更新語(yǔ)句（數(shù)據(jù)的增刪改）、數(shù)據(jù)定義語(yǔ)句（包括建表、修改表結(jié)構(gòu)等）和更新類(lèi)事務(wù)的提交語(yǔ)句。

表級(jí)鎖

MySQL 里面表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（meta data lock，MDL)。

表鎖

表鎖的語(yǔ)法是 lock tables … read/write。與 FTWRL 類(lèi)似，可以用 unlock tables 主動(dòng)釋放鎖，也可以在客戶(hù)端斷開(kāi)的時(shí)候自動(dòng)釋放。需要注意，lock tables 語(yǔ)法除了會(huì)限制別的線(xiàn)程的讀寫(xiě)外，也限定了本線(xiàn)程接下來(lái)的操作對(duì)象。

元數(shù)據(jù)鎖

MDL 不需要顯式使用，在訪(fǎng)問(wèn)一個(gè)表的時(shí)候會(huì)被自動(dòng)加上。MDL 的作用是，保證讀寫(xiě)的正確性。你可以想象一下，如果一個(gè)查詢(xún)正在遍歷一個(gè)表中的數(shù)據(jù)，而執(zhí)行期間另一個(gè)線(xiàn)程對(duì)這個(gè)表結(jié)構(gòu)做變更，刪了一列，那么查詢(xún)線(xiàn)程拿到的結(jié)果跟表結(jié)構(gòu)對(duì)不上，肯定是不行的。

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

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

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

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

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

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

表鎖又分為: 表共享讀鎖 read lock、表獨(dú)占寫(xiě)鎖write lock

語(yǔ)法: 1、加鎖 lock tables 表名 ... read/write

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

注意: 讀鎖不會(huì)阻塞其它客戶(hù)端的讀，但是會(huì)阻塞其它客戶(hù)端的寫(xiě)，寫(xiě)鎖既會(huì)阻塞其它客戶(hù)端的讀，又會(huì)阻塞其它客戶(hù)端的寫(xiě)。大家可以拿一張表來(lái)測(cè)試看看。

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

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

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

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

意向鎖，為了避免DML在執(zhí)行時(shí)，加的行鎖與表鎖的沖突，在innodb中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來(lái)減少表鎖的檢查。意向鎖分為,意向共享鎖is由語(yǔ)句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;

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

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

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

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

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

innodb實(shí)現(xiàn)了以下兩種類(lèi)型的行鎖

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

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

insert 語(yǔ)句 排他鎖 自動(dòng)添加的

update語(yǔ)句 排他鎖 自動(dòng)添加

delete 語(yǔ)句 排他鎖 自動(dòng)添加

select 正常查詢(xún)語(yǔ)句 不加鎖 。。。

select 。。。lock in share mode 共享鎖 需要手動(dòng)在select 之后加lock in share mode

select 。。。for update 排他鎖 需要手動(dòng)在select之后添加for update

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

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