MySQL 鎖概述

歡迎來到藍(lán)隊(duì)云技術(shù)小課堂，每天分享一個(gè)技術(shù)小知識(shí)。今天介紹關(guān)于mysql鎖的相關(guān)概念和發(fā)生的原因。

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。

在數(shù)據(jù)庫(kù)中，除傳統(tǒng)的計(jì)算資源（如CPU、RAM、I/O等）的爭(zhēng)用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。

如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問性能的一個(gè)重要因素。

從這個(gè)角度來說，鎖對(duì)數(shù)據(jù)庫(kù)而言顯得尤其重要，也更加復(fù)雜。

本文他對(duì)于將著重討論MySQL鎖機(jī)制的特點(diǎn)，常見的鎖問題，以及解決MySQL鎖問題的一些方法或建議：↓

- - -

相對(duì)其他數(shù)據(jù)庫(kù)而言，MySQL的鎖機(jī)制比較簡(jiǎn)單，其最顯著的特點(diǎn)是不同的存儲(chǔ)引擎支持不同的鎖機(jī)制。

比如：

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

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

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

MySQL 這 3 種鎖的特性可大致歸納如下：↓ ↓ ↓

開銷、加鎖速度、死鎖、粒度、并發(fā)性能

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

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

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

從上述特點(diǎn)可見，很難籠統(tǒng)地說哪種鎖更好，只能就具體應(yīng)用的特點(diǎn)來說哪種鎖更合適！

僅從鎖的角度來說：表級(jí)鎖更適合于以查詢?yōu)橹鳎挥猩倭堪此饕龡l件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；

而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

一切取決于應(yīng)用程序，應(yīng)用程序的不同部分可能需要不同的鎖類型。

為了確定是否想要使用行級(jí)鎖定的存儲(chǔ)引擎，應(yīng)看看應(yīng)用程序做什么并且混合使用什么樣的選擇和更新語句。

例如，大多數(shù)Web應(yīng)用程序執(zhí)行許多選擇，而很少進(jìn)行刪除，只對(duì)關(guān)鍵字的值進(jìn)行更新，并且只插入少量具體的表。

基本MySQL MyISAM 設(shè)置已經(jīng)調(diào)節(jié)得很好。

在 MySQL 中對(duì)于使用表級(jí)鎖定的存儲(chǔ)引擎，表鎖定時(shí)不會(huì)死鎖的。

InnoDB：

　　對(duì)于普通SELECT語句，InnoDB不會(huì)加任何鎖；

　　InnoDB 行鎖是通過給索引項(xiàng)加鎖實(shí)現(xiàn)的：這就意味著只有通過索引條件檢索數(shù)據(jù)時(shí)，InnoDB才使用行鎖，否則使用表鎖。

　　在mysql中，如果查詢條件帶有主鍵，會(huì)鎖行數(shù)據(jù)，如果沒有，會(huì)鎖表。

　　當(dāng)where條件中的字段沒有加索引時(shí)，會(huì)鎖住整張表。

　　在有索引的情況下，更新不同的行，innodb 默認(rèn)的行鎖是不會(huì)阻塞的。

　　where 后面的索引失效時(shí)，行鎖變表鎖，其他事物操作會(huì)有阻塞。

注意：在實(shí)際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。

　　行鎖建議：↓

　　　　盡可能讓所有數(shù)據(jù)檢索都通過索引來完成，避免無索引行鎖升級(jí)為表鎖

　　　　合理設(shè)計(jì)索引，盡量縮小索引的范圍

　　　　盡可能較少檢索條件，避免間隙鎖

　　　　盡量控制事務(wù)大小，減少鎖定資源量和時(shí)間長(zhǎng)度

　　　　盡可能低級(jí)別事務(wù)隔離

　　　　varchar 不加單引號(hào)會(huì)引發(fā)行鎖。

導(dǎo)致鎖表原因：↓

1、鎖表發(fā)生在insert update 、delete 中

2、鎖表的原理是 數(shù)據(jù)庫(kù)使用獨(dú)占式封鎖機(jī)制，當(dāng)執(zhí)行上面的語句時(shí)，對(duì)表進(jìn)行鎖住，直到發(fā)生commite 或者 回滾 或者退出數(shù)據(jù)庫(kù)用戶

　　第一、 A程序執(zhí)行了對(duì) tableA 的 insert ，并還未 commite時(shí)，B程序也對(duì)tableA 進(jìn)行insert 則此時(shí)會(huì)發(fā)生資源正忙的異常 就是鎖表

　　第二、鎖表常發(fā)生于并發(fā)而不是并行（并行時(shí)，一個(gè)線程操作數(shù)據(jù)庫(kù)時(shí)，另一個(gè)線程是不能操作數(shù)據(jù)庫(kù)的，cpu 和i/o 分配原則）

3、減少鎖表的概率：

　　減少insert 、update 、delete 語句執(zhí)行 到 commite 之間的時(shí)間。

　　具體點(diǎn)批量執(zhí)行改為單個(gè)執(zhí)行、優(yōu)化sql自身的非執(zhí)行速度 如果異常對(duì)事物進(jìn)行回滾

范例：↓ ↓ ↓

1）：使用 update

　　假設(shè)kid 是表table 的 一個(gè)索引字段 且值不唯一：

　　1）：如果kid 有多個(gè)值為12的記錄那么：

　　　　update table set name=’feie’ where kid=12; #會(huì)鎖表

　　2）：如果kid有唯一的值為1的記錄那么：

　　　　update table set name=’feie’ where kid=1; #不會(huì)鎖

總結(jié)：用索引字段做為條件進(jìn)行修改時(shí)， 是否表鎖的取決于這個(gè)索引字段能否確定記錄唯一，當(dāng)索引值對(duì)應(yīng)記錄不唯一，會(huì)進(jìn)行鎖表，相反則行鎖。

2）：使用 delete

　　如果有兩個(gè)delete ： kid1 與 kid2 是索引字段

　　1）：語句1 delete from table where kid1=1 and kid2=2;

　　2）：語句2 delete from table where kid1=1 and kid2=3;

　　　　 # 這樣的兩個(gè)delete 是不會(huì)鎖表的

　　1）：語句1 delete from table where kid1=1 and kid2=2;

　　2）：語句2 delete from table where kid1=1 ;

　　　　 # 這樣的兩個(gè)delete 會(huì)鎖表

總結(jié)：同一個(gè)表，如果進(jìn)行刪除操作時(shí)，盡量讓刪除條件統(tǒng)一，否則會(huì)相互影響造成鎖表

以上就是藍(lán)隊(duì)云的全部分享，藍(lán)隊(duì)云官網(wǎng)上擁有完善的技術(shù)支持庫(kù)可供參考，大家可自行查閱，更多技術(shù)問題，可以直接咨詢。同時(shí)，藍(lán)隊(duì)云整理了運(yùn)維必備的工具包免費(fèi)分享給大家使用，需要的朋友可以直接咨詢。

更多技術(shù)知識(shí)，藍(lán)隊(duì)云期待與你一起探索。