表鎖 行鎖 頁鎖

表鎖：
表級別的鎖定是MySQL各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點：實現(xiàn)邏輯非常簡單，帶來的系統(tǒng)負(fù)面影響最小。獲取鎖和釋放鎖的速度很快。由于表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。
缺點：鎖定顆粒度大所帶來最大的負(fù)面影響就是出現(xiàn)鎖定資源爭用的概率也會最高，致使并大度大打折扣。
使用表級鎖定的主要是MyISAM，MEMORY，CSV等一些非事務(wù)性存儲引擎。

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

頁鎖
頁級鎖定是MySQL中比較獨特的一種鎖定級別，在其他數(shù)據(jù)庫管理軟件中也并不是太常見。
特點：1.鎖定顆粒度介于行級鎖定與表級鎖之間，
2.獲取鎖定所需要的資源開銷，
以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間。

另外，頁級鎖定和行級鎖定一樣，會發(fā)生死鎖。
在數(shù)據(jù)庫實現(xiàn)資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同數(shù)據(jù)量的數(shù)據(jù)所需要消耗的內(nèi)存數(shù)量是越來越多的，實現(xiàn)算法也會越來越復(fù)雜。不過，隨著鎖定資源顆粒度的減小，應(yīng)用程序的訪問請求遇到鎖等待的可能性也會隨之降低，系統(tǒng)整體并發(fā)度也隨之提升。
使用頁級鎖定的主要是BerkeleyDB存儲引擎。

總體歸納如下

表鎖詳解

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

表鎖分為表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。
對MyISAM表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；(讀阻塞寫）
對MyISAM表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；（寫阻塞讀寫）
MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的。當(dāng)一個線程獲得對一個表的寫鎖后，只有持有鎖的線程可以對表進(jìn)行更新操作。其他線程的讀、寫操作都會等待，直到鎖被釋放為止。

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

行鎖詳解

行鎖分為：共享鎖（S），排他鎖（X），意向共享鎖（IS）和意向排他鎖（IX）
當(dāng)對某個資源加鎖時，如果

• 有共享鎖，可以再加一個共享鎖，不過不能加排他鎖。
• 有排它鎖，就在表上添加意向共享鎖或意向排他鎖。
  意向共享鎖可以同時并存多個，但是意向排他鎖同時只能有一個存在。意向鎖是InnoDB自動加的，不需用戶干預(yù)。

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

意向鎖是InnoDB自動加的，不需用戶干預(yù)。
UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數(shù)據(jù)集加排他鎖（X)；對于普通SELECT語句，InnoDB不會加任何鎖；

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

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

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

死鎖原因

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

如何解決

（1）在應(yīng)用中，如果不同的程序會并發(fā)存取多個表，應(yīng)盡量約定以相同的順序（按順序持有鎖，不易沖突）來訪問表，這樣可以大大降低產(chǎn)生死鎖的機會。
（2）在程序以批量方式處理數(shù)據(jù)的時候，如果事先對數(shù)據(jù)排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現(xiàn)死鎖的可能。
（3）在事務(wù)中，如果要更新（不管先后，直接申請排他鎖）記錄，應(yīng)該直接申請足夠級別的鎖，即排他鎖，而不應(yīng)先申請共享鎖，更新時再申請排他鎖，因為當(dāng)用戶申請排他鎖時，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。
（4）在REPEATABLE-READ（可重復(fù)讀）隔離級別下，如果兩個線程同時對相同條件記錄用SELECT…FOR UPDATE加排他鎖，在沒有符合該條件記錄情況下（即不存在的記錄），兩個線程都會加鎖成功。程序發(fā)現(xiàn)記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現(xiàn)死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可避免問題。
（5）當(dāng)隔離級別為READ COMMITTED時，如果兩個線程都先執(zhí)行SELECT…FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現(xiàn)鎖等待，當(dāng)?shù)?個線程提交后，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖。這時如果有第3個線程又來申請排他鎖，也會出現(xiàn)死鎖。對于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯誤時，總是執(zhí)行ROLLBACK釋放獲得的排他鎖。

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

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

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

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

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

mysql> show engine InnoDB status;

監(jiān)視器可以通過發(fā)出下列語句來停止查看：

mysql> drop table InnoDB_monitor;

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

引用

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