表鎖 行鎖 頁(yè)鎖

表鎖：
表級(jí)別的鎖定是MySQL各存儲(chǔ)引擎中最大顆粒度的鎖定機(jī)制澄步。該鎖定機(jī)制最大的特點(diǎn)：實(shí)現(xiàn)邏輯非常簡(jiǎn)單南缓，帶來(lái)的系統(tǒng)負(fù)面影響最小。獲取鎖和釋放鎖的速度很快厌秒。由于表級(jí)鎖一次會(huì)將整個(gè)表鎖定读拆，所以可以很好的避免困擾我們的死鎖問(wèn)題。
缺點(diǎn)：鎖定顆粒度大所帶來(lái)最大的負(fù)面影響就是出現(xiàn)鎖定資源爭(zhēng)用的概率也會(huì)最高鸵闪，致使并大度大打折扣檐晕。
使用表級(jí)鎖定的主要是MyISAM，MEMORY蚌讼，CSV等一些非事務(wù)性存儲(chǔ)引擎棉姐。

行鎖：
行級(jí)鎖定最大的特點(diǎn)就是鎖定對(duì)象的顆粒度很小，也是目前各大數(shù)據(jù)庫(kù)管理軟件所實(shí)現(xiàn)的鎖定顆粒度最小的啦逆。由于鎖定顆粒度很小伞矩，所以發(fā)生鎖定資源爭(zhēng)用的概率也最小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力而提高一些需要高并發(fā)應(yīng)用系統(tǒng)的整體性能夏志。
優(yōu)勢(shì)：在并發(fā)處理能力較強(qiáng)
弊端：由于鎖定資源的顆粒度很小乃坤，所以每次獲取鎖和釋放鎖需要做的事情也更多苛让，帶來(lái)的消耗自然也就更大了。此外湿诊，行級(jí)鎖定也最容易發(fā)生死鎖狱杰。
使用行級(jí)鎖定的主要是InnoDB存儲(chǔ)引擎。

頁(yè)鎖
頁(yè)級(jí)鎖定是MySQL中比較獨(dú)特的一種鎖定級(jí)別厅须，在其他數(shù)據(jù)庫(kù)管理軟件中也并不是太常見(jiàn)仿畸。
特點(diǎn)：1.鎖定顆粒度介于行級(jí)鎖定與表級(jí)鎖之間，
2.獲取鎖定所需要的資源開(kāi)銷(xiāo)朗和，
以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間错沽。

另外，頁(yè)級(jí)鎖定和行級(jí)鎖定一樣眶拉，會(huì)發(fā)生死鎖千埃。
在數(shù)據(jù)庫(kù)實(shí)現(xiàn)資源鎖定的過(guò)程中，隨著鎖定資源顆粒度的減小忆植，鎖定相同數(shù)據(jù)量的數(shù)據(jù)所需要消耗的內(nèi)存數(shù)量是越來(lái)越多的放可，實(shí)現(xiàn)算法也會(huì)越來(lái)越復(fù)雜。不過(guò)朝刊，隨著鎖定資源顆粒度的減小耀里，應(yīng)用程序的訪問(wèn)請(qǐng)求遇到鎖等待的可能性也會(huì)隨之降低，系統(tǒng)整體并發(fā)度也隨之提升拾氓。
使用頁(yè)級(jí)鎖定的主要是BerkeleyDB存儲(chǔ)引擎备韧。

總體歸納如下

表鎖詳解

mysql的MyISAM完全使用表鎖，InnoDB沒(méi)有使用索引條件檢索數(shù)據(jù)時(shí)也是采用表鎖的形式痪枫。

表鎖分為表共享讀鎖（Table Read Lock）和表獨(dú)占寫(xiě)鎖（Table Write Lock）织堂。
對(duì)MyISAM表的讀操作，不會(huì)阻塞其他用戶(hù)對(duì)同一表的讀請(qǐng)求奶陈，但會(huì)阻塞對(duì)同一表的寫(xiě)請(qǐng)求易阳；(讀阻塞寫(xiě)）
對(duì)MyISAM表的寫(xiě)操作，則會(huì)阻塞其他用戶(hù)對(duì)同一表的讀和寫(xiě)操作吃粒；（寫(xiě)阻塞讀寫(xiě)）
MyISAM表的讀操作與寫(xiě)操作之間潦俺，以及寫(xiě)操作之間是串行的。當(dāng)一個(gè)線(xiàn)程獲得對(duì)一個(gè)表的寫(xiě)鎖后徐勃，只有持有鎖的線(xiàn)程可以對(duì)表進(jìn)行更新操作事示。其他線(xiàn)程的讀、寫(xiě)操作都會(huì)等待僻肖，直到鎖被釋放為止肖爵。

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

行鎖詳解

行鎖分為：共享鎖（S）驻呐，排他鎖（X），意向共享鎖（IS）和意向排他鎖（IX）
當(dāng)對(duì)某個(gè)資源加鎖時(shí)芳来，如果

• 有共享鎖含末，可以再加一個(gè)共享鎖，不過(guò)不能加排他鎖绣张。
• 有排它鎖，就在表上添加意向共享鎖或意向排他鎖关带。
  意向共享鎖可以同時(shí)并存多個(gè)侥涵，但是意向排他鎖同時(shí)只能有一個(gè)存在。意向鎖是InnoDB自動(dòng)加的宋雏，不需用戶(hù)干預(yù)芜飘。

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE

意向鎖是InnoDB自動(dòng)加的，不需用戶(hù)干預(yù)磨总。
UPDATE嗦明、DELETE和INSERT語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X)蚪燕；對(duì)于普通SELECT語(yǔ)句娶牌，InnoDB不會(huì)加任何鎖；

間隙鎖（Next-Key鎖）（鍵值在條件范圍內(nèi)但并不存在的記錄）
當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)馆纳，并請(qǐng)求共享或排他鎖時(shí)诗良，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；
對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄鲁驶，叫做“間隙（GAP)”鉴裹，InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）钥弯。

select * from emp where empid > 100 for update;
一個(gè)范圍條件的檢索径荔，InnoDB不僅會(huì)對(duì)符合條件的empid值為101的記錄加鎖，也會(huì)對(duì)empid大于101（這些記錄并不存在）的“間隙”加鎖脆霎。

InnoDB使用間隙鎖的目的：
（1）防止幻讀总处，以滿(mǎn)足相關(guān)隔離級(jí)別的要求。對(duì)于上面的例子睛蛛，要是不使用間隙鎖辨泳，如果其他事務(wù)插入了empid大于100的任何記錄虱岂，那么本事務(wù)如果再次執(zhí)行上述語(yǔ)句，就會(huì)發(fā)生幻讀菠红；
（2）為了滿(mǎn)足其恢復(fù)和復(fù)制的需要第岖。
很顯然，在使用范圍條件檢索并鎖定記錄時(shí)试溯，即使某些不存在的鍵值也會(huì)被無(wú)辜的鎖定蔑滓，而造成在鎖定的時(shí)候無(wú)法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)。在某些場(chǎng)景下這可能會(huì)對(duì)性能造成很大的危害遇绞。

死鎖原因

在InnoDB中键袱，除單個(gè)SQL組成的事務(wù)外，鎖是逐步獲得的摹闽，當(dāng)兩個(gè)事務(wù)都需要獲得對(duì)方持有的排他鎖才能繼續(xù)完成事務(wù)蹄咖，這種循環(huán)鎖等待就是典型的死鎖。

如何解決

（1）在應(yīng)用中付鹿，如果不同的程序會(huì)并發(fā)存取多個(gè)表澜汤，應(yīng)盡量約定以相同的順序（按順序持有鎖，不易沖突）來(lái)訪問(wèn)表舵匾，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)俊抵。
（2）在程序以批量方式處理數(shù)據(jù)的時(shí)候，如果事先對(duì)數(shù)據(jù)排序坐梯，保證每個(gè)線(xiàn)程按固定的順序來(lái)處理記錄徽诲，也可以大大降低出現(xiàn)死鎖的可能。
（3）在事務(wù)中吵血，如果要更新（不管先后谎替，直接申請(qǐng)排他鎖）記錄，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖蹋辅，即排他鎖院喜，而不應(yīng)先申請(qǐng)共享鎖，更新時(shí)再申請(qǐng)排他鎖晕翠，因?yàn)楫?dāng)用戶(hù)申請(qǐng)排他鎖時(shí)喷舀，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突淋肾，甚至死鎖硫麻。
（4）在REPEATABLE-READ（可重復(fù)讀）隔離級(jí)別下，如果兩個(gè)線(xiàn)程同時(shí)對(duì)相同條件記錄用SELECT…FOR UPDATE加排他鎖樊卓，在沒(méi)有符合該條件記錄情況下（即不存在的記錄）拿愧，兩個(gè)線(xiàn)程都會(huì)加鎖成功。程序發(fā)現(xiàn)記錄尚不存在碌尔，就試圖插入一條新記錄浇辜，如果兩個(gè)線(xiàn)程都這么做券敌，就會(huì)出現(xiàn)死鎖。這種情況下柳洋，將隔離級(jí)別改成READ COMMITTED待诅，就可避免問(wèn)題。
（5）當(dāng)隔離級(jí)別為READ COMMITTED時(shí)熊镣，如果兩個(gè)線(xiàn)程都先執(zhí)行SELECT…FOR UPDATE卑雁，判斷是否存在符合條件的記錄，如果沒(méi)有绪囱，就插入記錄测蹲。此時(shí)，只有一個(gè)線(xiàn)程能插入成功鬼吵，另一個(gè)線(xiàn)程會(huì)出現(xiàn)鎖等待扣甲，當(dāng)?shù)?個(gè)線(xiàn)程提交后，第2個(gè)線(xiàn)程會(huì)因主鍵重出錯(cuò)齿椅，但雖然這個(gè)線(xiàn)程出錯(cuò)了琉挖，卻會(huì)獲得一個(gè)排他鎖。這時(shí)如果有第3個(gè)線(xiàn)程又來(lái)申請(qǐng)排他鎖媒咳，也會(huì)出現(xiàn)死鎖粹排。對(duì)于這種情況种远，可以直接做插入操作涩澡，然后再捕獲主鍵重異常，或者在遇到主鍵重錯(cuò)誤時(shí)坠敷，總是執(zhí)行ROLLBACK釋放獲得的排他鎖妙同。

InnoDB行鎖優(yōu)化建議

• 1 要想合理利用InnoDB的行級(jí)鎖定，做到揚(yáng)長(zhǎng)避短膝迎，我們必須做好以下工作：
  a)盡可能讓所有的數(shù)據(jù)檢索都通過(guò)索引來(lái)完成粥帚，從而避免InnoDB因?yàn)闊o(wú)法通過(guò)索引鍵加鎖而升級(jí)為表級(jí)鎖定；
  b)合理設(shè)計(jì)索引限次，讓InnoDB在索引鍵上面加鎖的時(shí)候盡可能準(zhǔn)確芒涡，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他Query的執(zhí)行卖漫；
  c)盡可能減少基于范圍的數(shù)據(jù)檢索過(guò)濾條件费尽，避免因?yàn)殚g隙鎖帶來(lái)的負(fù)面影響而鎖定了不該鎖定的記錄；
  d)盡量控制事務(wù)的大小羊始，減少鎖定的資源量和鎖定時(shí)間長(zhǎng)度旱幼；
  e)在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級(jí)別的事務(wù)隔離突委，以減少M(fèi)ySQL因?yàn)閷?shí)現(xiàn)事務(wù)隔離級(jí)別所帶來(lái)的附加成本柏卤。
• 2 由于InnoDB的行級(jí)鎖定和事務(wù)性冬三，所以肯定會(huì)產(chǎn)生死鎖，下面是一些比較常用的減少死鎖產(chǎn)生概率的小建議：
  a)類(lèi)似業(yè)務(wù)模塊中缘缚，盡可能按照相同的訪問(wèn)順序來(lái)訪問(wèn)勾笆，防止產(chǎn)生死鎖；
  b)在同一個(gè)事務(wù)中忙灼，盡可能做到一次鎖定所需要的所有資源匠襟，減少死鎖產(chǎn)生概率；
  c)對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分该园，可以嘗試使用升級(jí)鎖定顆粒度酸舍，通過(guò)表級(jí)鎖定來(lái)減少死鎖產(chǎn)生的概率。
• 3 可以通過(guò)檢查InnoDB_row_lock狀態(tài)變量來(lái)分析系統(tǒng)上的行鎖的爭(zhēng)奪情況：
  show status like 'InnoDB_row_lock%';

InnoDB_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量里初；
InnoDB_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度啃勉；
InnoDB_row_lock_time_avg：每次等待所花平均時(shí)間；
InnoDB_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次所花的時(shí)間双妨；
InnoDB_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)淮阐；
對(duì)于這5個(gè)狀態(tài)變量，比較重要的主要是InnoDB_row_lock_time_avg（等待平均時(shí)長(zhǎng)）刁品，InnoDB_row_lock_waits（等待總次數(shù)）以及InnoDB_row_lock_time（等待總時(shí)長(zhǎng)）這三項(xiàng)泣特。尤其是當(dāng)?shù)却螖?shù)很高，而且每次等待時(shí)長(zhǎng)也不小的時(shí)候挑随，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待状您，然后根據(jù)分析結(jié)果著手指定優(yōu)化計(jì)劃。

如果發(fā)現(xiàn)鎖爭(zhēng)用比較嚴(yán)重兜挨，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高膏孟，還可以通過(guò)設(shè)置InnoDB Monitors 來(lái)進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等拌汇，并分析鎖爭(zhēng)用的原因柒桑。
鎖沖突的表、數(shù)據(jù)行等噪舀，并分析鎖爭(zhēng)用的原因魁淳。具體方法如下：
mysql> create table InnoDB_monitor(a INT) engine=InnoDB;

然后就可以用下面的語(yǔ)句來(lái)進(jìn)行查看：

mysql> show engine InnoDB status;

監(jiān)視器可以通過(guò)發(fā)出下列語(yǔ)句來(lái)停止查看：

mysql> drop table InnoDB_monitor;

設(shè)置監(jiān)視器后，會(huì)有詳細(xì)的當(dāng)前鎖等待的信息与倡，包括表名界逛、鎖類(lèi)型、鎖定記錄的情況等蒸走，便于進(jìn)行進(jìn)一步的分析和問(wèn)題的確定仇奶。為什么要先創(chuàng)建一個(gè)叫InnoDB_monitor的表呢？因?yàn)閯?chuàng)建該表實(shí)際上就是告訴InnoDB我們開(kāi)始要監(jiān)控他的細(xì)節(jié)狀態(tài)了，然后InnoDB就會(huì)將比較詳細(xì)的事務(wù)以及鎖定信息記錄進(jìn)入MySQL的errorlog中该溯，以便我們后面做進(jìn)一步分析使用岛抄。打開(kāi)監(jiān)視器以后，默認(rèn)情況下每15秒會(huì)向日志中記錄監(jiān)控的內(nèi)容狈茉，如果長(zhǎng)時(shí)間打開(kāi)會(huì)導(dǎo)致.err文件變得非常的巨大夫椭，所以用戶(hù)在確認(rèn)問(wèn)題原因之后，要記得刪除監(jiān)控表以關(guān)閉監(jiān)視器氯庆，或者通過(guò)使用“–console”選項(xiàng)來(lái)啟動(dòng)服務(wù)器以關(guān)閉寫(xiě)日志文件蹭秋。

引用

本文只是本人平時(shí)方便查看，原文引用鏈接：https://yq.aliyun.com/articles/603991