MySQL 行鎖和表鎖的含義及區(qū)別詳解

一、前言

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

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

相對其他數(shù)據(jù)庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。

MySQL大致可歸納為以下3種鎖：

• 表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
• 行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
• 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般

如何加鎖？

MyISAM在執(zhí)行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預，因此用戶一般不需要直接用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；

二、表鎖

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

MyISAM引擎

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

MySQL的表級鎖有兩種模式：

• 表共享讀鎖
• 表獨占寫鎖

讀鎖會阻塞寫，寫鎖會阻塞讀和寫

• 對MyISAM表的讀操作，不會阻塞其它進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放后，才會執(zhí)行其它進程的寫操作。
• 對MyISAM表的寫操作，會阻塞其它進程對同一表的讀和寫操作，只有當寫鎖釋放后，才會執(zhí)行其它進程的讀寫操作。

MyISAM不適合做寫為主表的引擎，因為寫鎖后，其它線程不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠阻塞

三、行鎖

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

在MySQL的InnoDB引擎支持行鎖，與Oracle不同，MySQL的行鎖是通過索引加載的，也就是說，行鎖是加在索引響應的行上的，要是對應的SQL語句沒有走索引，則會全表掃描，行鎖則無法實現(xià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

-- 這里，我們建一個user表，主鍵為id

 

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

 

-- A通過name執(zhí)行插入操作，但事務未提交
update user set count=10 where name='xxx';
-- B在此時也執(zhí)行更新操作
update user set count=10 where id=2;
-- 由于是通過非主鍵或索引選中的，升級為為表級鎖，
-- B則無法對該表進行更新或插入操作，只有當A提交事務后，B才會成功執(zhí)行

for update

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

-- A用戶對id=1的記錄進行加鎖
select * from user where id=1 for update;

-- B用戶無法對該記錄進行操作
update user set count=10 where id=1;

-- A用戶commit以后則B用戶可以對該記錄進行操作

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

• 行鎖必須有索引才能實現(xiàn)，否則會自動鎖全表，那么就不是行鎖了。
• 兩個事務不能鎖同一個索引。
• insert，delete，update在事務中都會自動默認加上排它鎖。

行鎖場景：

A用戶消費，service層先查詢該用戶的賬戶余額，若余額足夠，則進行后續(xù)的扣款操作；這種情況查詢的時候應該對該記錄進行加鎖。

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

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

擴展：間隙鎖

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

InnoDB也會對這個"間隙"加鎖，這種鎖機制就是所謂的間隙鎖

-- 用戶A
update user set count=8 where id>2 and id6

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

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

建議：

• 盡可能讓所有數(shù)據(jù)檢索都通過索引來完成，避免無索引行鎖升級為表鎖
• 合理設計索引，盡量縮小鎖的范圍
• 盡可能減少索引條件，避免間隙鎖
• 盡量控制事務大小，減少鎖定資源量和時間長度

 到此這篇關于MySQL 行鎖和表鎖的含義及區(qū)別詳解的文章就介紹到這了,更多相關MySQL 行鎖和表鎖內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！