前言

為了保證數(shù)據(jù)的一致完整性，任何一個數(shù)據(jù)庫都存在鎖定機制。鎖定機制的優(yōu)劣直接應(yīng)想到一個數(shù)據(jù)庫系統(tǒng)的并發(fā)處理能力和性能，所以鎖定機制的實現(xiàn)也就成為了各種數(shù)據(jù)庫的核心技術(shù)之一。本章將對MySQL中兩種使用最為頻繁的存儲引擎MyISAM和Innodb各自的鎖定機制進行較為詳細的分析。

MySQL鎖定機制簡介

數(shù)據(jù)庫鎖定機制簡單來說就是數(shù)據(jù)庫為了保證數(shù)據(jù)的一致性而使各種共享資源在被并發(fā)訪問訪問變得有序所設(shè)計的一種規(guī)則。對于任何一種數(shù)據(jù)庫來說都需要有相應(yīng)的鎖定機制，所以MySQL自然也不能例外。MySQL數(shù)據(jù)庫由于其自身架構(gòu)的特點，存在多種數(shù)據(jù)存儲引擎，每種存儲引擎所針對的應(yīng)用場景特點都不太一樣，為了滿足各自特定應(yīng)用場景的需求，每種存儲引擎的鎖定機制都是為各自所面對的特定場景而優(yōu)化設(shè)計，所以各存儲引擎的鎖定機制也有較大區(qū)別。

總的來說，MySQL各存儲引擎使用了三種類型（級別）的鎖定機制：行級鎖定，頁級鎖定和表級鎖定。下面我們先分析一下MySQL這三種鎖定的特點和各自的優(yōu)劣所在。

行級鎖定（row-level）

行級鎖定最大的特點就是鎖定對象的顆粒度很小，也是目前各大數(shù)據(jù)庫管理軟件所實現(xiàn)的鎖定顆粒度最小的。由于鎖定顆粒度很小，所以發(fā)生鎖定資源爭用的概率也最小，能夠給予應(yīng)用程序盡可能大的并發(fā)處理能力而提高一些需要高并發(fā)應(yīng)用系統(tǒng)的整體性能。

雖然能夠在并發(fā)處理能力上面有較大的優(yōu)勢，但是行級鎖定也因此帶來了不少弊端。由于鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了。此外，行級鎖定也最容易發(fā)生死鎖。

表級鎖定（table-level）

和行級鎖定相反，表級別的鎖定是MySQL各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現(xiàn)邏輯非常簡單，帶來的系統(tǒng)負面影響最小。所以獲取鎖和釋放鎖的速度很快。由于表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。

當然，鎖定顆粒度大所帶來最大的負面影響就是出現(xiàn)鎖定資源爭用的概率也會最高，致使并大度大打折扣。

頁級鎖定（page-level）

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

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

在MySQL數(shù)據(jù)庫中，使用表級鎖定的主要是MyISAM，Memory，CSV等一些非事務(wù)性存儲引擎，而使用行級鎖定的主要是Innodb存儲引擎和NDBCluster存儲引擎，頁級鎖定主要是BerkeleyDB存儲引擎的鎖定方式。

MySQL的如此的鎖定機制主要是由于其最初的歷史所決定的。在最初，MySQL希望設(shè)計一種完全獨立于各種存儲引擎的鎖定機制，而且在早期的MySQL數(shù)據(jù)庫中，MySQL的存儲引擎（MyISAM和Momery）的設(shè)計是建立在“任何表在同一時刻都只允許單個線程對其訪問（包括讀）”這樣的假設(shè)之上。但是，隨著MySQL的不斷完善，系統(tǒng)的不斷改進，在MySQL3.23版本開發(fā)的時候，MySQL開發(fā)人員不得不修正之前的假設(shè)。因為他們發(fā)現(xiàn)一個線程正在讀某個表的時候，另一個線程是可以對該表進行insert操作的，只不過只能INSERT到數(shù)據(jù)文件的最尾部。這也就是從MySQL從3.23版本開始提供的我們所說的Concurrent Insert。

當出現(xiàn)Concurrent Insert之后，MySQL的開發(fā)人員不得不修改之前系統(tǒng)中的鎖定實現(xiàn)功能，但是僅僅只是增加了對Concurrent Insert的支持，并沒有改動整體架構(gòu)?？墒窃诓痪弥?，隨著BerkeleyDB存儲引擎的引入，之前的鎖定機制遇到了更大的挑戰(zhàn)。因為BerkeleyDB存儲引擎并沒有MyISAM和Memory存儲引擎同一時刻只允許單一線程訪問某一個表的限制，而是將這個單線程訪問限制的顆粒度縮小到了單個page，這又一次迫使MySQL開發(fā)人員不得不再一次修改鎖定機制的實現(xiàn)。

由于新的存儲引擎的引入，導(dǎo)致鎖定機制不能滿足要求，讓MySQL的人意識到已經(jīng)不可能實現(xiàn)一種完全獨立的滿足各種存儲引擎要求的鎖定實現(xiàn)機制。如果因為鎖定機制的拙劣實現(xiàn)而導(dǎo)致存儲引擎的整體性能的下降，肯定會嚴重打擊存儲引擎提供者的積極性，這是MySQL公司非常不愿意看到的，因為這完全不符合MySQL的戰(zhàn)略發(fā)展思路。所以工程師們不得不放棄了最初的設(shè)計初衷，在鎖定實現(xiàn)機制中作出修改，允許存儲引擎自己改變MySQL通過接口傳入的鎖定類型而自行決定該怎樣鎖定數(shù)據(jù)。

表級鎖定

MySQL的表級鎖定主要分為兩種類型，一種是讀鎖定，另一種是寫鎖定。在MySQL中，主要通過四個隊列來維護這兩種鎖定：兩個存放當前正在鎖定中的讀和寫鎖定信息，另外兩個存放等待中的讀寫鎖定信息，如下：

當前持有讀鎖的所有線程的相關(guān)信息都能夠在Currentread-lockqueue中找到，隊列中的信息按照獲取到鎖的時間依序存放。而正在等待鎖定資源的信息則存放在Pendingread-lockqueue里面，另外兩個存放寫鎖信息的隊列也按照上面相同規(guī)則來存放信息。

雖然對于我們這些使用者來說MySQL展現(xiàn)出來的鎖定（表鎖定）只有讀鎖定和寫鎖定這兩種類型，但是在MySQL內(nèi)部實現(xiàn)中卻有多達11種鎖定類型，由系統(tǒng)中一個枚舉量（thr_lock_type）定義，各值描述如下：

讀鎖定

一個新的客戶端請求在申請獲取讀鎖定資源的時候，需要滿足兩個條件：

1、請求鎖定的資源當前沒有被寫鎖定；
2、寫鎖定等待隊列（Pendingwrite-lockqueue）中沒有更高優(yōu)先級的寫鎖定等待；

如果滿足了上面兩個條件之后，該請求會被立即通過，并將相關(guān)的信息存入Currentread-lockqueue中，而如果上面兩個條件中任何一個沒有滿足，都會被迫進入等待隊列Pendingread-lockqueue中等待資源的釋放。

寫鎖定

當客戶端請求寫鎖定的時候，MySQL首先檢查在Currentwrite-lockqueue是否已經(jīng)有鎖定相同資源的信息存在。

如果Currentwrite-lockqueue沒有，則再檢查Pendingwrite-lockqueue，如果在Pendingwrite-lockqueue中找到了，自己也需要進入等待隊列并暫停自身線程等待鎖定資源。反之，如果Pendingwrite-lockqueue為空，則再檢測Currentread-lockqueue，如果有鎖定存在，則同樣需要進入Pendingwrite-lockqueue等待。當然，也可能遇到以下這兩種特殊情況：

1. 請求鎖定的類型為WRITE_DELAYED;
2. 請求鎖定的類型為WRITE_CONCURRENT_INSERT或者是TL_WRITE_ALLOW_WRITE，同時Currentreadlock是READ_NO_INSERT的鎖定類型。

當遇到這兩種特殊情況的時候，寫鎖定會立即獲得而進入Current write-lock queue 中

如果剛開始第一次檢測就Currentwrite-lockqueue中已經(jīng)存在了鎖定相同資源的寫鎖定存在，那么就只能進入等待隊列等待相應(yīng)資源鎖定的釋放了。

讀請求和寫等待隊列中的寫鎖請求的優(yōu)先級規(guī)則主要為以下規(guī)則決定：

1. 除了READ_HIGH_PRIORITY的讀鎖定之外，Pendingwrite-lockqueue中的WRITE寫鎖定能夠阻塞所有其他的讀鎖定；
2. READ_HIGH_PRIORITY讀鎖定的請求能夠阻塞所有Pendingwrite-lockqueue中的寫鎖定；
3. 除了WRITE寫鎖定之外，Pendingwrite-lockqueue中的其他任何寫鎖定都比讀鎖定的優(yōu)先級低。

寫鎖定出現(xiàn)在Currentwrite-lockqueue之后，會阻塞除了以下情況下的所有其他鎖定的請求：

1. 在某些存儲引擎的允許下，可以允許一個WRITE_CONCURRENT_INSERT寫鎖定請求
2. 寫鎖定為WRITE_ALLOW_WRITE的時候，允許除了WRITE_ONLY之外的所有讀和寫鎖定請求
3. 寫鎖定為WRITE_ALLOW_READ的時候，允許除了READ_NO_INSERT之外的所有讀鎖定請求
4. 寫鎖定為WRITE_DELAYED的時候，允許除了READ_NO_INSERT之外的所有讀鎖定請求
5. 寫鎖定為WRITE_CONCURRENT_INSERT的時候，允許除了READ_NO_INSERT之外的所有讀鎖定請求

隨著MySQL存儲引擎的不斷發(fā)展，目前MySQL自身提供的鎖定機制已經(jīng)沒有辦法滿足需求了，很多存儲引擎都在MySQL所提供的鎖定機制之上做了存儲引擎自己的擴展和改造。

MyISAM存儲引擎基本上可以說是對MySQL所提供的鎖定機制所實現(xiàn)的表級鎖定依賴最大的一種存儲引擎了，雖然MyISAM存儲引擎自己并沒有在自身增加其他的鎖定機制，但是為了更好的支持相關(guān)特性，MySQL在原有鎖定機制的基礎(chǔ)上為了支持其ConcurrentInsert的特性而進行了相應(yīng)的實現(xiàn)改造。

而其他幾種支持事務(wù)的存儲存儲引擎，如Innodb，NDBCluster以及BerkeleyDB存儲引擎則是讓MySQL將鎖定的處理直接交給存儲引擎自己來處理，在MySQL中僅持有WRITE_ALLOW_WRITE類型的鎖定。

由于MyISAM存儲引擎使用的鎖定機制完全是由MySQL提供的表級鎖定實現(xiàn)，所以下面我們將以MyISAM存儲引擎作為示例存儲引擎，來實例演示表級鎖定的一些基本特性。由于，為了讓示例更加直觀，我將使用顯示給表加鎖來演示：RITE_ALLOW_READ 類型的寫鎖定。

行級鎖定

行級鎖定不是MySQL自己實現(xiàn)的鎖定方式，而是由其他存儲引擎自己所實現(xiàn)的，如廣為大家所知的Innodb存儲引擎，以及MySQL的分布式存儲引擎NDBCluster等都是實現(xiàn)了行級鎖定。

Innodb 鎖定模式及實現(xiàn)機制

考慮到行級鎖定君由各個存儲引擎自行實現(xiàn)，而且具體實現(xiàn)也各有差別，而Innodb是目前事務(wù)型存儲引擎中使用最為廣泛的存儲引擎，所以這里我們就主要分析一下Innodb的鎖定特性。

總的來說，Innodb的鎖定機制和Oracle數(shù)據(jù)庫有不少相似之處。Innodb的行級鎖定同樣分為兩種類型，共享鎖和排他鎖，而在鎖定機制的實現(xiàn)過程中為了讓行級鎖定和表級鎖定共存，Innodb也同樣使用了意向鎖（表級鎖定）的概念，也就有了意向共享鎖和意向排他鎖這兩種。

當一個事務(wù)需要給自己需要的某個資源加鎖的時候，如果遇到一個共享鎖正鎖定著自己需要的資源的時候，自己可以再加一個共享鎖，不過不能加排他鎖。但是，如果遇到自己需要鎖定的資源已經(jīng)被一個排他鎖占有之后，則只能等待該鎖定釋放資源之后自己才能獲取鎖定資源并添加自己的鎖定。而意向鎖的作用就是當一個事務(wù)在需要獲取資源鎖定的時候，如果遇到自己需要的資源已經(jīng)被排他鎖占用的時候，該事務(wù)可以需要鎖定行的表上面添加一個合適的意向鎖。如果自己需要一個共享鎖，那么就在表上面添加一個意向共享鎖。而如果自己需要的是某行（或者某些行）上面添加一個排他鎖的話，則先在表上面添加一個意向排他鎖。意向共享鎖可以同時并存多個，但是意向排他鎖同時只能有一個存在。所以，可以說Innodb的鎖定模式實際上可以分為四種：共享鎖（S），排他鎖（X），意向共享鎖（IS）和意向排他鎖（IX），我們可以通過以下表格來總結(jié)上面這四種所的共存邏輯關(guān)系：

雖然Innodb的鎖定機制和Oracle有不少相近的地方，但是兩者的實現(xiàn)確是截然不同的?？偟膩碚f就是Oracle鎖定數(shù)據(jù)是通過需要鎖定的某行記錄所在的物理block上的事務(wù)槽上表級鎖定信息，而Innodb的鎖定則是通過在指向數(shù)據(jù)記錄的第一個索引鍵之前和最后一個索引鍵之后的空域空間上標記鎖定信息而實現(xiàn)的。Innodb的這種鎖定實現(xiàn)方式被稱為“NEXT-KEYlocking”（間隙鎖），因為Query執(zhí)行過程中通過過范圍查找的華，他會鎖定整個范圍內(nèi)所有的索引鍵值，即使這個鍵值并不存在。

間隙鎖有一個比較致命的弱點，就是當鎖定一個范圍鍵值之后，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)。在某些場景下這可能會對性能造成很大的危害。而Innodb給出的解釋是為了組織幻讀的出現(xiàn)，所以他們選擇的間隙鎖來實現(xiàn)鎖定。

除了間隙鎖給Innodb帶來性能的負面影響之外，通過索引實現(xiàn)鎖定的方式還存在其他幾個較大的性能隱患：

當Query無法利用索引的時候，Innodb會放棄使用行級別鎖定而改用表級別的鎖定，造成并發(fā)性能的降低；

當Quuery使用的索引并不包含所有過濾條件的時候，數(shù)據(jù)檢索使用到的索引鍵所只想的數(shù)據(jù)可能有部分并不屬于該Query的結(jié)果集的行列，但是也會被鎖定，因為間隙鎖鎖定的是一個范圍，而不是具體的索引鍵；

當Query在使用索引定位數(shù)據(jù)的時候，如果使用的索引鍵一樣但訪問的數(shù)據(jù)行不同的時候（索引只是過濾條件的一部分），一樣會被鎖定

Innodb 各事務(wù)隔離級別下鎖定及死鎖

Innodb實現(xiàn)的在ISO／ANSISQL92規(guī)范中所定義的ReadUnCommited，ReadCommited，RepeatableRead和Serializable這四種事務(wù)隔離級別。同時，為了保證數(shù)據(jù)在事務(wù)中的一致性，實現(xiàn)了多版本數(shù)據(jù)訪問。

之前在第一節(jié)中我們已經(jīng)介紹過，行級鎖定肯定會帶來死鎖問題，Innodb也不可能例外。至于死鎖的產(chǎn)生過程我們就不在這里詳細描述了，在后面的鎖定示例中會通過一個實際的例子為大家愛展示死鎖的產(chǎn)生過程。這里我們主要介紹一下，在Innodb中當系檢測到死鎖產(chǎn)生之后是如何來處理的。

在Innodb的事務(wù)管理和鎖定機制中，有專門檢測死鎖的機制，會在系統(tǒng)中產(chǎn)生死鎖之后的很短時間內(nèi)就檢測到該死鎖的存在。當Innodb檢測到系統(tǒng)中產(chǎn)生了死鎖之后，Innodb會通過相應(yīng)的判斷來選這產(chǎn)生死鎖的兩個事務(wù)中較小的事務(wù)來回滾，而讓另外一個較大的事務(wù)成功完成。那Innodb是以什么來為標準判定事務(wù)的大小的呢？MySQL官方手冊中也提到了這個問題，實際上在Innodb發(fā)現(xiàn)死鎖之后，會計算出兩個事務(wù)各自插入、更新或者刪除的數(shù)據(jù)量來判定兩個事務(wù)的大小。也就是說哪個事務(wù)所改變的記錄條數(shù)越多，在死鎖中就越不會被回滾掉。但是有一點需要注意的就是，當產(chǎn)生死鎖的場景中涉及到不止Innodb存儲引擎的時候，Innodb是沒辦法檢測到該死鎖的，這時候就只能通過鎖定超時限制來解決該死鎖了。另外，死鎖的產(chǎn)生過程的示例將在本節(jié)最后的Innodb鎖定示例中演示。

Innodb 鎖定機制示例

合理利用鎖機制優(yōu)化MySQL

MyISAM 表鎖優(yōu)化建議

對于MyISAM存儲引擎，雖然使用表級鎖定在鎖定實現(xiàn)的過程中比實現(xiàn)行級鎖定或者頁級鎖所帶來的附加成本都要小，鎖定本身所消耗的資源也是最少。但是由于鎖定的顆粒度比較到，所以造成鎖定資源的爭用情況也會比其他的鎖定級別都要多，從而在較大程度上會降低并發(fā)處理能力。

所以，在優(yōu)化MyISAM存儲引擎鎖定問題的時候，最關(guān)鍵的就是如何讓其提高并發(fā)度。由于鎖定級別是不可能改變的了，所以我們首先需要盡可能讓鎖定的時間變短，然后就是讓可能并發(fā)進行的操作盡可能的并發(fā)。

 1、縮短鎖定時間

縮短鎖定時間，短短幾個字，說起來確實聽容易的，但實際做起來恐怕就并不那么簡單了。如何讓鎖定時間盡可能的短呢？唯一的辦法就是讓我們的Query執(zhí)行時間盡可能的短。

盡兩減少大的復(fù)雜Query，將復(fù)雜Query分拆成幾個小的Query分布進行；

盡可能的建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速；

盡量讓MyISAM存儲引擎的表只存放必要的信息，控制字段類型；

利用合適的機會優(yōu)化MyISAM表數(shù)據(jù)文件；

2、分離能并行的操作

說到MyISAM的表鎖，而且是讀寫互相阻塞的表鎖，可能有些人會認為在MyISAM存儲引擎的表上就只能是完全的串行化，沒辦法再并行了。大家不要忘記了，MyISAM的存儲引擎還有一個非常有用的特性，那就是ConcurrentInsert（并發(fā)插入）的特性。

MyISAM存儲引擎有一個控制是否打開Concurrent Insert功能的參數(shù)選項：concurrent_insert，可以設(shè)置為0，1或者2。三個值的具體說明如下：

concurrent_insert=2，無論MyISAM存儲引擎的表數(shù)據(jù)文件的中間部分是否存在因為刪除數(shù)據(jù)而留下的空閑空間，都允許在數(shù)據(jù)文件尾部進行ConcurrentInsert;

concurrent_insert=1，當MyISAM存儲引擎表數(shù)據(jù)文件中間不存在空閑空間的時候，可以從文件尾部進行ConcurrentInsert;

concurrent_insert=0，無論MyISAM存儲引擎的表數(shù)據(jù)文件的中間部分是否存在因為刪除數(shù)據(jù)而留下的空閑空間，都不允許ConcurrentInsert。

3、合理利用讀寫優(yōu)先級

在本章各種鎖定分析一節(jié)中我們了解到了MySQL的表級鎖定對于讀和寫是有不同優(yōu)先級設(shè)定的，默認情況下是寫優(yōu)先級要大于讀優(yōu)先級。所以，如果我們可以根據(jù)各自系統(tǒng)環(huán)境的差異決定讀與寫的優(yōu)先級。如果我們的系統(tǒng)是一個以讀為主，而且要優(yōu)先保證查詢性能的話，我們可以通過設(shè)置系統(tǒng)參數(shù)選項low_priority_updates=1，將寫的優(yōu)先級設(shè)置為比讀的優(yōu)先級低，即可讓告訴MySQL盡量先處理讀請求。當然，如果我們的系統(tǒng)需要有限保證數(shù)據(jù)寫入的性能的話，則可以不用設(shè)置low_priority_updates參數(shù)了。

這里我們完全可以利用這個特性，將concurrent_insert參數(shù)設(shè)置為1，甚至如果數(shù)據(jù)被刪除的可能性很小的時候，如果對暫時性的浪費少量空間并不是特別的在乎的話，將concurrent_insert參數(shù)設(shè)置為2都可以嘗試。當然，數(shù)據(jù)文件中間留有空域空間，在浪費空間的時候，還會造成在查詢的時候需要讀取更多的數(shù)據(jù)，所以如果刪除量不是很小的話，還是建議將concurrent_insert設(shè)置為1更為合適。

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

Innodb存儲引擎由于實現(xiàn)了行級鎖定，雖然在鎖定機制的實現(xiàn)方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發(fā)處理能力方面要遠遠優(yōu)于MyISAM的表級鎖定的。當系統(tǒng)并發(fā)量較高的時候，Innodb的整體性能和MyISAM相比就會有比較明顯的優(yōu)勢了。但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當?shù)臅r候，可能會讓Innodb的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會更差。

要想合理利用Innodb的行級鎖定，做到揚長避短，我們必須做好以下工作：

盡可能讓所有的數(shù)據(jù)檢索都通過索引來完成，從而避免Innodb因為無法通過索引鍵加鎖而升級為表級鎖定；

合理設(shè)計索引，讓Innodb在索引鍵上面加鎖的時候盡可能準確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他Query的執(zhí)行；

盡可能減少基于范圍的數(shù)據(jù)檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄；

盡量控制事務(wù)的大小，減少鎖定的資源量和鎖定時間長度；

在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級別的事務(wù)隔離，以減少MySQL因為實現(xiàn)事務(wù)隔離級別所帶來的附加成本；

由于Innodb的行級鎖定和事務(wù)性，所以肯定會產(chǎn)生死鎖，下面是一些比較常用的減少死鎖產(chǎn)生概率

的的小建議，讀者朋友可以根據(jù)各自的業(yè)務(wù)特點針對性的嘗試：a)類似業(yè)務(wù)模塊中，盡可能按照相同的訪問順序來訪問，防止產(chǎn)生死鎖；b)在同一個事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；c)對于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產(chǎn)生的概率；

系統(tǒng)鎖定爭用情況查詢對于兩種鎖定級別，MySQL內(nèi)部有兩組專門的狀態(tài)變量記錄系統(tǒng)內(nèi)部鎖資源爭用情況，我們先看看

MySQL 實現(xiàn)的表級鎖定的爭用狀態(tài)變量：

這里有兩個狀態(tài)變量記錄MySQL內(nèi)部表級鎖定的情況，兩個變量說明如下：

Table_locks_immediate：產(chǎn)生表級鎖定的次數(shù)；

Table_locks_waited：出現(xiàn)表級鎖定爭用而發(fā)生等待的次數(shù)；

兩個狀態(tài)值都是從系統(tǒng)啟動后開始記錄，沒出現(xiàn)一次對應(yīng)的事件則數(shù)量加1。如果這里的Table_locks_waited狀態(tài)值比較高，那么說明系統(tǒng)中表級鎖定爭用現(xiàn)象比較嚴重，就需要進一步分析為什么會有較多的鎖定資源爭用了。

對于Innodb所使用的行級鎖定，系統(tǒng)中是通過另外一組更為詳細的狀態(tài)變量來記錄的，如下：

Innodb 的行級鎖定狀態(tài)變量不僅記錄了鎖定等待次數(shù)，還記錄了鎖定總時長，每次平均時長，以及最大時長，此外還有一個非累積狀態(tài)量顯示了當前正在等待鎖定的等待數(shù)量。對各個狀態(tài)量的說明如下：

Innodb_row_lock_current_waits：當前正在等待鎖定的數(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（等待總時長）這三項。尤其是當?shù)却螖?shù)很高，而且每次等待時長也不小的時候，我們就需要分析系統(tǒng)中為什么會有如此多的等待，然后根據(jù)分析結(jié)果著手指定優(yōu)化計劃。

此外，Innodb出了提供這五個系統(tǒng)狀態(tài)變量之外，還提供的其他更為豐富的即時狀態(tài)信息供我們分析使用?？梢酝ㄟ^如下方法查看：

1.通過創(chuàng)建InnodbMonitor表來打開Innodb的monitor功能：

2.然后通過使用“SHOWINNODBSTATUS”查看細節(jié)信息（由于輸出內(nèi)容太多就不在此記錄了）；

可能會有讀者朋友問為什么要先創(chuàng)建一個叫innodb_monitor的表呢？因為創(chuàng)建該表實際上就是告訴Innodb我們開始要監(jiān)控他的細節(jié)狀態(tài)了，然后Innodb就會將比較詳細的事務(wù)以及鎖定信息記錄進入MySQL的errorlog中，以便我們后面做進一步分析使用。

小結(jié)

本章以MySQLServer中的鎖定簡介開始，分析了當前MySQL中使用最為廣泛的鎖定方式表級鎖定和行級鎖定的基本實現(xiàn)機制，并通過MyISAM和Innodb這兩大典型的存儲引擎作為示例存儲引擎所使用的表級鎖定和行級鎖定做了較為詳細的分析和演示。然后，再通過分析兩種鎖定方式的特性，給出相應(yīng)的優(yōu)化建議和策略。最后了解了一下在MySQLServer中如何獲得系統(tǒng)當前各種鎖定的資源爭用狀況。希望本章內(nèi)容能夠?qū)Ω魑蛔x者朋友在理解MySQL鎖定機制方面有一定的幫助。

最新評論