【61期】MySQL行鎖和表鎖的含義及區(qū)別（MySQL面試第四彈）

程序員的成長(zhǎng)之路

互聯(lián)網(wǎng)/程序員/技術(shù)/資料共享?

閱讀本文大概需要 4?分鐘。

來(lái)自：網(wǎng)絡(luò)

一、前言

對(duì)于行鎖和表鎖的含義區(qū)別，在面試中應(yīng)該是高頻出現(xiàn)的，我們應(yīng)該對(duì)MySQL中的鎖有一個(gè)系統(tǒng)的認(rèn)識(shí)，更詳細(xì)的需要自行查閱資料，本篇為概括性的總結(jié)回答。

MySQL常用引擎有MyISAM和InnoDB，而InnoDB是mysql默認(rèn)的引擎。MyISAM不支持行鎖，而InnoDB支持行鎖和表鎖。

如何加鎖？

MyISAM在執(zhí)行查詢語(yǔ)句（SELECT）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT等）前，會(huì)自動(dòng)給涉及的表加寫鎖，這個(gè)過(guò)程并不需要用戶干預(yù)，因此用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。

顯式加鎖：

上共享鎖（讀鎖）的寫法：lock in share mode，例如：

select??math?from?zje?where?math>60?lock?in?share?mode；

上排它鎖（寫鎖）的寫法：for update，例如：

select?math?from?zje?where?math?>60?for?update；

二、表鎖

不會(huì)出現(xiàn)死鎖，發(fā)生鎖沖突幾率高，并發(fā)低。

MyISAM引擎

MyISAM在執(zhí)行查詢語(yǔ)句（select）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行增刪改操作前，會(huì)自動(dòng)給涉及的表加寫鎖。

MySQL的表級(jí)鎖有兩種模式：

• 表共享讀鎖

• 表獨(dú)占寫鎖

讀鎖會(huì)阻塞寫，寫鎖會(huì)阻塞讀和寫

• 對(duì)MyISAM表的讀操作，不會(huì)阻塞其它進(jìn)程對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求。只有當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的寫操作。

• 對(duì)MyISAM表的寫操作，會(huì)阻塞其它進(jìn)程對(duì)同一表的讀和寫操作，只有當(dāng)寫鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的讀寫操作。

MyISAM不適合做寫為主表的引擎，因?yàn)閷戞i后，其它線程不能做任何操作，大量的更新會(huì)使查詢很難得到鎖，從而造成永遠(yuǎn)阻塞

三、行鎖

會(huì)出現(xiàn)死鎖，發(fā)生鎖沖突幾率低，并發(fā)高。

在MySQL的InnoDB引擎支持行鎖，與Oracle不同，MySQL的行鎖是通過(guò)索引加載的，也就是說(shuō)，行鎖是加在索引響應(yīng)的行上的，要是對(duì)應(yīng)的SQL語(yǔ)句沒(méi)有走索引，則會(huì)全表掃描，行鎖則無(wú)法實(shí)現(xiàn)，取而代之的是表鎖，此時(shí)其它事務(wù)無(wú)法對(duì)當(dāng)前表進(jìn)行更新或插入操作。

CREATE?TABLE?`user`?(
??`name`?VARCHAR(32)?DEFAULT?NULL,
??`count`?INT(11)?DEFAULT?NULL,
??`id`?INT(11)?NOT?NULL?AUTO_INCREMENT,
??PRIMARY?KEY?(`id`)
)?ENGINE=INNODB?AUTO_INCREMENT=7?DEFAULT?CHARSET=utf8

--?這里，我們建一個(gè)user表，主鍵為id



--?A通過(guò)主鍵執(zhí)行插入操作，但事務(wù)未提交
update?user?set?count=10?where?id=1;
--?B在此時(shí)也執(zhí)行更新操作
update?user?set?count=10?where?id=2;
--?由于是通過(guò)主鍵選中的，為行級(jí)鎖，A和B操作的不是同一行，B執(zhí)行的操作是可以執(zhí)行的



--?A通過(guò)name執(zhí)行插入操作，但事務(wù)未提交
update?user?set?count=10?where?name='xxx';
--?B在此時(shí)也執(zhí)行更新操作
update?user?set?count=10?where?id=2;

--?由于是通過(guò)非主鍵或索引選中的，升級(jí)為為表級(jí)鎖，
-- B則無(wú)法對(duì)該表進(jìn)行更新或插入操作，只有當(dāng)A提交事務(wù)后，B才會(huì)成功執(zhí)行

for update

如果在一條select語(yǔ)句后加上for update，則查詢到的數(shù)據(jù)會(huì)被加上一條排它鎖，其它事務(wù)可以讀取，但不能進(jìn)行更新和插入操作

--?A用戶對(duì)id=1的記錄進(jìn)行加鎖
select?*?from?user?where?id=1?for?update;

--?B用戶無(wú)法對(duì)該記錄進(jìn)行操作
update?user?set?count=10?where?id=1;

--?A用戶commit以后則B用戶可以對(duì)該記錄進(jìn)行操作

行鎖的實(shí)現(xiàn)需要注意：

1. 行鎖必須有索引才能實(shí)現(xiàn)，否則會(huì)自動(dòng)鎖全表，那么就不是行鎖了。

2. 兩個(gè)事務(wù)不能鎖同一個(gè)索引。

3. insert，delete，update在事務(wù)中都會(huì)自動(dòng)默認(rèn)加上排它鎖。

行鎖場(chǎng)景：

A用戶消費(fèi)，service層先查詢?cè)撚脩舻馁~戶余額，若余額足夠，則進(jìn)行后續(xù)的扣款操作；這種情況查詢的時(shí)候應(yīng)該對(duì)該記錄進(jìn)行加鎖。

否則，B用戶在A用戶查詢后消費(fèi)前先一步將A用戶賬號(hào)上的錢轉(zhuǎn)走，而此時(shí)A用戶已經(jīng)進(jìn)行了用戶余額是否足夠的判斷，則可能會(huì)出現(xiàn)余額已經(jīng)不足但卻扣款成功的情況。

為了避免此情況，需要在A用戶操作該記錄的時(shí)候進(jìn)行for update加鎖

擴(kuò)展：間隙鎖

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)并不存在的記錄，叫做間隙

InnoDB也會(huì)對(duì)這個(gè)"間隙"加鎖，這種鎖機(jī)制就是所謂的間隙鎖

--?用戶A
update?user?set?count=8?where?id>2?and?id<6

--?用戶B
update?user?set?count=10?where?id=5;

如果用戶A在進(jìn)行了上述操作后，事務(wù)還未提交，則B無(wú)法對(duì)2~6之間的記錄進(jìn)行更新或插入記錄，會(huì)阻塞，當(dāng)A將事務(wù)提交后，B的更新操作會(huì)執(zhí)行。

建議：

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

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

• 盡可能減少索引條件，避免間隙鎖

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

推薦閱讀：

【60期】事務(wù)隔離級(jí)別中的可重復(fù)讀能防幻讀嗎?（MySQL面試第三彈）

【59期】MySQL索引是如何提高查詢效率的呢？（MySQL面試第二彈）

【58期】盤點(diǎn)那些面試中最常問(wèn)的MySQL問(wèn)題，第一彈！

5T技術(shù)資源大放送！包括但不限于：C/C++，Linux，Python，Java，PHP，人工智能，單片機(jī)，樹(shù)莓派，等等。在公眾號(hào)內(nèi)回復(fù)「2048」，即可免費(fèi)獲?。?！

微信掃描二維碼，關(guān)注我的公眾號(hào)

朕已閱?