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

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

成都創(chuàng)新互聯(lián)公司專注于句容企業(yè)網(wǎng)站建設(shè),響應(yīng)式網(wǎng)站,購物商城網(wǎng)站建設(shè)。句容網(wǎng)站建設(shè)公司,為句容等地區(qū)提供建站服務(wù)。全流程按需定制制作，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，成都創(chuàng)新互聯(lián)公司專業(yè)和態(tài)度為您提供的服務(wù)

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

解決辦法是在innodb引擎中，備份時(shí)加上--single-transaction參數(shù)來完成不加鎖的一致性數(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ú)占寫鎖write lock

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

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

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

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

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

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

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

意向鎖，為了避免DML在執(zhí)行時(shí)，加的行鎖與表鎖的沖突，在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;

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

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

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)了以下兩種類型的行鎖

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

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

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

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

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

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

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ù)在同一間隙上采用的間隙鎖。

mysql讀數(shù)據(jù)時(shí)怎么加寫鎖

加鎖情況與死鎖原因分析

為方便大家復(fù)現(xiàn)，完整表結(jié)構(gòu)和數(shù)據(jù)如下：

CREATE TABLE `t3` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`),

UNIQUE KEY `c2` (`c2`)

) ENGINE=InnoDB

insert into t3 values(1,1),(15,15),(20,20);

在 session1 執(zhí)行 commit 的瞬間，我們會(huì)看到 session2、session3 的其中一個(gè)報(bào)死鎖。這個(gè)死鎖是這樣產(chǎn)生的：

1.?session1 執(zhí)行 delete ?會(huì)在唯一索引 c2 的 c2 = 15 這一記錄上加 X lock（也就是在MySQL 內(nèi)部觀測(cè)到的：X Lock but not gap）；

2.?session2 和 session3 在執(zhí)行 insert 的時(shí)候，由于唯一約束檢測(cè)發(fā)生唯一沖突，會(huì)加 S Next-Key Lock，即對(duì) (1,15] 這個(gè)區(qū)間加鎖包括間隙，并且被 seesion1 的 X Lock 阻塞，進(jìn)入等待；

3.?session1 在執(zhí)行 commit 后，會(huì)釋放 X Lock，session2 和 session3 都獲得 S Next-Key Lock；

4.?session2 和 session3 繼續(xù)執(zhí)行插入操作，這個(gè)時(shí)候 INSERT INTENTION LOCK（插入意向鎖）出現(xiàn)了，并且由于插入意向鎖會(huì)被 gap 鎖阻塞，所以 session2 和 session3 互相等待，造成死鎖。

死鎖日志如下：

請(qǐng)點(diǎn)擊輸入圖片描述

INSERT INTENTION LOCK

在之前的死鎖分析第四點(diǎn)，如果不分析插入意向鎖，也是會(huì)造成死鎖的，因?yàn)椴迦胱罱K還是要對(duì)記錄加 X Lock 的，session2 和 session3 還是會(huì)互相阻塞互相等待。

但是插入意向鎖是客觀存在的，我們可以在官方手冊(cè)中查到，不可忽略：

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.

插入意向鎖其實(shí)是一種特殊的 gap lock，但是它不會(huì)阻塞其他鎖。假設(shè)存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個(gè)事務(wù)在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個(gè)事務(wù)不會(huì)互相沖突等待。

當(dāng)插入一條記錄時(shí)，會(huì)去檢查當(dāng)前插入位置的下一條記錄上是否存在鎖對(duì)象，如果下一條記錄上存在鎖對(duì)象，就需要判斷該鎖對(duì)象是否鎖住了 gap。如果 gap 被鎖住了，則插入意向鎖與之沖突，進(jìn)入等待狀態(tài)（插入意向鎖之間并不互斥）。總結(jié)一下這把鎖的屬性：

1. 它不會(huì)阻塞其他任何鎖；

2. 它本身僅會(huì)被 gap lock 阻塞。

在學(xué)習(xí) MySQL 過程中，一般只有在它被阻塞的時(shí)候才能觀察到，所以這也是它常常被忽略的原因吧...

GAP LOCK

在此例中，另外一個(gè)重要的點(diǎn)就是 gap lock，通常情況下我們說到 gap lock 都只會(huì)聯(lián)想到 REPEATABLE-READ 隔離級(jí)別利用其解決幻讀。但實(shí)際上在 READ-COMMITTED 隔離級(jí)別，也會(huì)存在 gap lock ，只發(fā)生在：唯一約束檢查到有唯一沖突的時(shí)候，會(huì)加 S Next-key Lock，即對(duì)記錄以及與和上一條記錄之間的間隙加共享鎖。

通過下面這個(gè)例子就能驗(yàn)證：

請(qǐng)點(diǎn)擊輸入圖片描述

這里 session1 插入數(shù)據(jù)遇到唯一沖突，雖然報(bào)錯(cuò)，但是對(duì) (15,20] 加的 S Next-Key Lock 并不會(huì)馬上釋放，所以 session2 被阻塞。另外一種情況就是本文開始的例子，當(dāng) session2 插入遇到唯一沖突但是因?yàn)楸?X Lock 阻塞，并不會(huì)立刻報(bào)錯(cuò) “Duplicate key”，但是依然要等待獲取 S Next-Key Lock 。

有個(gè)困惑很久的疑問：出現(xiàn)唯一沖突需要加 S Next-Key Lock 是事實(shí)，但是加鎖的意義是什么？還是說是通過 S Next-Key Lock 來實(shí)現(xiàn)的唯一約束檢查，但是這樣意味著在插入沒有遇到唯一沖突的時(shí)候，這個(gè)鎖會(huì)立刻釋放，這不符合二階段鎖原則。這點(diǎn)希望能與大家一起討論得到好的解釋。

如果是在 REPEATABLE-READ，除以上所說的唯一約束沖突外，gap lock 的存在是這樣的：

普通索引（非唯一索引）的S/X Lock，都帶 gap 屬性，會(huì)鎖住記錄以及前1條記錄到后1條記錄的左閉右開區(qū)間，比如有[4,6,8]記錄，delete 6，則會(huì)鎖住[4,8）整個(gè)區(qū)間。

對(duì)于 gap lock，相信 DBA 們的心情是一樣一樣的，所以我的建議是：

1. 在絕大部分的業(yè)務(wù)場(chǎng)景下，都可以把 MySQL 的隔離界別設(shè)置為 READ-COMMITTED；

2. 在業(yè)務(wù)方便控制字段值唯一的情況下，盡量減少表中唯一索引的數(shù)量。

鎖沖突矩陣

前面我們說的 GAP LOCK 其實(shí)是鎖的屬性，另外我們知道 InnoDB 常規(guī)鎖模式有：S 和 X，即共享鎖和排他鎖。鎖模式和鎖屬性是可以隨意組合的，組合之后的沖突矩陣如下，這對(duì)我們分析死鎖很有幫助：

請(qǐng)點(diǎn)擊輸入圖片描述

Mysql中鎖的類型有哪些呢？

mysql鎖分為共享鎖和排他鎖，也叫做讀鎖和寫鎖。

讀鎖是共享的，可以通過lock in share mode實(shí)現(xiàn)，這時(shí)候只能讀不能寫。

寫鎖是排他的，它會(huì)阻塞其他的寫鎖和讀鎖。從顆粒度來區(qū)分，可以分為表鎖和?鎖兩種。

表鎖會(huì)鎖定整張表并且阻塞其他?戶對(duì)該表的所有讀寫操作，?如alter修改表結(jié)構(gòu)的時(shí)候會(huì)鎖表。

?鎖?可以分為樂觀鎖和悲觀鎖，悲觀鎖可以通過for update實(shí)現(xiàn)，樂觀鎖則通過版本號(hào)實(shí)現(xiàn)。