MySQL鎖

1.鎖知識(shí)點(diǎn)腦圖

01.png

2.鎖種類釋義

2.1鎖概述

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。鎖保證數(shù)據(jù)并發(fā)訪問的一致性、有效性；鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素。鎖是Mysql在服務(wù)器層和存儲(chǔ)引擎層的的并發(fā)控制。

加鎖是消耗資源的，鎖的各種操作，包括獲得鎖、檢測(cè)鎖是否是否已解除、釋放鎖等。

2.2粒度劃分鎖

粒度:mysql中代指鎖的級(jí)別

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

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

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

2.3使用方式

共享鎖:是一種讀鎖,當(dāng)前事務(wù)加鎖后其他事務(wù)可以讀但不可以寫.存儲(chǔ)引擎不同實(shí)現(xiàn)的功能也不一樣.

排他鎖:是一種寫鎖,當(dāng)前事務(wù)加鎖后其他事務(wù)不可以讀也不可以寫.存儲(chǔ)引擎不同實(shí)現(xiàn)的功能也不一樣.

2.3邏輯劃分

悲觀鎖和樂觀鎖是在很多框架都存在的一種思想，不要狹義地認(rèn)為它們是某一種框架的鎖機(jī)制

樂觀鎖:樂觀鎖需要程序員自己去實(shí)現(xiàn)的鎖機(jī)制

悲觀鎖:悲觀鎖的實(shí)現(xiàn)是基于Mysql自身的鎖機(jī)制實(shí)現(xiàn)

2.4各存儲(chǔ)引擎對(duì)鎖的支持

MyISAM 和 MEMORY 存儲(chǔ)引擎采用的是表級(jí)鎖（table-level locking）

BDB 存儲(chǔ)引擎采用的是頁面鎖（page-level locking），但也支持表級(jí)鎖

InnoDB 存儲(chǔ)引擎既支持行級(jí)鎖（row-level locking），也支持表級(jí)鎖，但默認(rèn)情況下是采用行級(jí)鎖。

3.MyISAM鎖

3.1支持的粒度鎖

MyISAM中默認(rèn)支持的表級(jí)鎖有兩種:即[共享讀鎖]和[獨(dú)占寫鎖]。表級(jí)鎖在MyISAM和InnoDB的存儲(chǔ)引擎中都支持，但是InnoDB默認(rèn)支持的是行鎖。

3.2鎖的使用方式

獨(dú)占寫鎖:當(dāng)一個(gè)線程獲取到表級(jí)寫鎖后，只能由該線程對(duì)表進(jìn)行讀寫操作，別的線程必須等待該線程釋放鎖以后才能操作

共享讀鎖:****當(dāng)一個(gè)線程獲取到表級(jí)讀鎖后，該線程只能讀取數(shù)據(jù)不能修改數(shù)據(jù)，其它線程也只能加讀鎖，不能加寫鎖

3.3鎖競爭查看方法

MyISAM存儲(chǔ)引擎中，可以通過查詢變量來查看并發(fā)場(chǎng)景鎖的爭奪情況，具體執(zhí)行下面的sql語句:

show status like 'table%';

02.png

Table_locks_immediate：表示能夠立即獲得表級(jí)鎖的鎖請(qǐng)求次數(shù)；Table_locks_waited表示不能立即獲取表級(jí)鎖而需要等待的鎖請(qǐng)求次數(shù)分析，「值越大競爭就越嚴(yán)重」。

3.4并行插入

解鎖和釋放鎖都是Mysql底層隱式的執(zhí)行的

在我們平時(shí)執(zhí)行select語句的時(shí)候就會(huì)隱式的加讀鎖，執(zhí)行增、刪、改的操作時(shí)就會(huì)隱式的執(zhí)行加寫鎖。

MyISAM存儲(chǔ)引擎中，雖然讀寫操作是串行化的，但是它也支持并發(fā)插入，這個(gè)需要設(shè)置內(nèi)部變量concurrent_insert的值。

它的值有三個(gè)值0、1、2。可以通過以下的sql查看concurrent_insert的默認(rèn)值為「AUTO(或者1)」。

concurrent_insert的值為NEVER (or 0)表示不支持比并發(fā)插入；值為AUTO(或者1）表示在MyISAM表中沒有被刪除的行，運(yùn)行另一個(gè)線程從表尾插入數(shù)據(jù)；值為ALWAYS (or 2)表示不管是否有刪除的行，都允許在表尾插入數(shù)據(jù)。

3.5鎖調(diào)度

MyISAM存儲(chǔ)引擎中，「假如同時(shí)一個(gè)讀請(qǐng)求，一個(gè)寫請(qǐng)求過來的話，它會(huì)優(yōu)先處理寫請(qǐng)求」，因?yàn)镸yISAM存儲(chǔ)引擎中認(rèn)為寫請(qǐng)求比都請(qǐng)求重要。

這樣就會(huì)導(dǎo)致，「假如大量的讀寫請(qǐng)求過來，就會(huì)導(dǎo)致讀請(qǐng)求長時(shí)間的等待，或者"線程餓死"，因此MyISAM不適合運(yùn)用于大量讀寫操作的場(chǎng)景」，這樣會(huì)導(dǎo)致長時(shí)間讀取不到用戶數(shù)據(jù)，用戶體驗(yàn)感極差。

當(dāng)然可以通過設(shè)置low-priority-updates參數(shù)，設(shè)置請(qǐng)求鏈接的優(yōu)先級(jí)，使得Mysql優(yōu)先處理讀請(qǐng)求。

4.InnoDB鎖

4.1支持的粒度鎖

行鎖是InnoDB默認(rèn)的支持的鎖機(jī)制MyISAM不支持行鎖，這個(gè)也是InnoDB和MyISAM的區(qū)別之一。另外innoDB支持事務(wù),MyISAM不支持事務(wù).

InnoDB中除了有「表鎖」和「行級(jí)鎖」的概念，還有Gap Lock（間隙鎖）、Next-key Lock鎖，「間隙鎖主要用于范圍查詢的時(shí)候，鎖住查詢的范圍，并且間隙鎖也是解決幻讀的方案」。

InnoDB中的行級(jí)鎖是「對(duì)索引加的鎖，在不通過索引查詢數(shù)據(jù)的時(shí)候，InnoDB就會(huì)使用表鎖」

4.2使用方式

InnoDB的行鎖也是分為行級(jí)「共享讀鎖（S鎖）「和」排它寫鎖（X鎖）」，原理特點(diǎn)和MyISAM的表級(jí)鎖兩種模式是一樣的。

執(zhí)行非索引條件查詢執(zhí)行的是表鎖。

執(zhí)行索引查詢是否是加行鎖，還得看Mysql的執(zhí)行計(jì)劃，可以通過explain關(guān)鍵字來查看。

用普通鍵索引的查詢，遇到索引值相同的，也會(huì)對(duì)其他的操作數(shù)據(jù)行的產(chǎn)生影響。

4.3InnoDB間隙鎖

當(dāng)我們使用范圍條件查詢而不是等值條件查詢的時(shí)候，InnoDB就會(huì)給符合條件的范圍索引加鎖，在條件范圍內(nèi)并不存的記錄就叫做"間隙（GAP）"

大家大概都知道在事務(wù)的四大隔離級(jí)別中，不可重復(fù)讀會(huì)產(chǎn)生幻讀的現(xiàn)象，只能通過提高隔離級(jí)別到串行化來解決幻讀現(xiàn)象。

但是Mysql中的不可重復(fù)是已經(jīng)解決了幻讀問題，它通過引入間隙鎖的實(shí)現(xiàn)來解決幻讀，通過給符合條件的間隙加鎖，防止再次查詢的時(shí)候出現(xiàn)新數(shù)據(jù)產(chǎn)生幻讀的問題。

4.4死鎖

死鎖在InnoDB中才會(huì)出現(xiàn)死鎖，MyISAM是不會(huì)出現(xiàn)死鎖，因?yàn)镸yISAM支持的是表鎖，一次性獲取了所有得鎖，其它的線程只能排隊(duì)等候。

而InnoDB默認(rèn)支持行鎖，獲取鎖是分步的，并不是一次性獲取所有得鎖，因此在鎖競爭的時(shí)候就會(huì)出現(xiàn)死鎖的情況。

雖然InnoDB會(huì)出現(xiàn)死鎖，但是并不影響InnoDB最受歡成為迎的存儲(chǔ)引擎，MyISAM可以理解為串行化操作，讀寫有序，因此支持的并發(fā)性能低下。

• 死鎖案例

當(dāng)前有表如下：

03.png

其中name="spike"的有兩條數(shù)據(jù)，并且name字段為普通索引，分別是id=3和id=4的數(shù)據(jù)行，現(xiàn)在假設(shè)有兩個(gè)事務(wù)分別執(zhí)行下面的兩條sql語句：

// session1
update user_info set age = 19 where name ='spike';
// session2執(zhí)行
select * from user_info where id = 3 or id = 4;

其中session1執(zhí)行的sql獲取的數(shù)據(jù)行是兩條數(shù)據(jù)，假設(shè)先獲取到第一個(gè)id=3的數(shù)據(jù)行，然后cpu的時(shí)間分配給了另一個(gè)事務(wù)，另一個(gè)事務(wù)執(zhí)行查詢操作獲取了第三行數(shù)據(jù)也就是id=3的數(shù)據(jù)行。

當(dāng)事務(wù)2繼續(xù)執(zhí)行的時(shí)候獲取到id=3的數(shù)據(jù)行，鎖定了id=3的數(shù)據(jù)行，此時(shí)cpu又將時(shí)間分配給了第一個(gè)事務(wù)，第一個(gè)事務(wù)執(zhí)行準(zhǔn)備獲取第二行數(shù)據(jù)的鎖，發(fā)現(xiàn)已經(jīng)被其他事務(wù)獲取了，它就處于等待的狀態(tài)。

當(dāng)cpu把時(shí)間有分配給了第二個(gè)事務(wù)，第二個(gè)事務(wù)準(zhǔn)備獲取第一行數(shù)據(jù)的鎖發(fā)現(xiàn)已經(jīng)被第一個(gè)事務(wù)獲取了鎖，這樣就行了死鎖，兩個(gè)事務(wù)彼此之間相互等待。

4.5死鎖解決方案

首先要解決死鎖問題，在程序的設(shè)計(jì)上，當(dāng)發(fā)現(xiàn)程序有高并發(fā)的訪問某一個(gè)表時(shí)，盡量對(duì)該表的執(zhí)行操作串行化，或者鎖升級(jí)，一次性獲取所有的鎖資源。

然后也可以設(shè)置參數(shù)innodb_lock_wait_timeout，超時(shí)時(shí)間，并且將參數(shù)innodb_deadlock_detect 打開，當(dāng)發(fā)現(xiàn)死鎖的時(shí)候，自動(dòng)回滾其中的某一個(gè)事務(wù)。

5.總結(jié)

上面詳細(xì)的介紹了MyISAM和InnoDB兩種存儲(chǔ)引擎的鎖機(jī)制的實(shí)現(xiàn)，并進(jìn)行了測(cè)試。

MyISAM的表鎖分為兩種模式：「共享讀鎖」和「排它寫鎖」。獲取的讀鎖的線程對(duì)該數(shù)據(jù)行只能讀，不能修改，其它線程也只能對(duì)該數(shù)據(jù)行加讀鎖。

獲取到寫鎖的線程對(duì)該數(shù)據(jù)行既能讀也能寫，對(duì)其他線程對(duì)該數(shù)據(jù)行的讀寫具有排它性。

MyISAM中默認(rèn)寫優(yōu)先于去操作，因此MyISAM一般不適合運(yùn)用于大量讀寫操作的程序中。

InnoDB的行鎖雖然會(huì)出現(xiàn)死鎖的可能，但是InnoDB的支持的并發(fā)性能比MyISAM好，行鎖的粒度最小，一定的方法和措施可以解決死鎖的發(fā)生，極大的發(fā)揮InnoDB的性能。

InnoDB中引入了間隙鎖的概念來決解出現(xiàn)幻讀的問題，也引入事務(wù)的特性，通過事務(wù)的四種隔離級(jí)別，來降低鎖沖突，提高并發(fā)性能。

6.參考文檔

本文參考文檔[大廠面試官必問的Mysql鎖機(jī)制] (https://zhuanlan.zhihu.com/p/150101667),大哥文檔比較詳細(xì)我這里是按自己理解做的筆記