前言

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

MySQL鎖定機(jī)制簡(jiǎn)介

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

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

行級(jí)鎖定（row-level）

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

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

表級(jí)鎖定（table-level）

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

當(dāng)然，鎖定顆粒度大所帶來(lái)最大的負(fù)面影響就是出現(xiàn)鎖定資源爭(zhēng)用的概率也會(huì)最高，致使并大度大打折扣。

頁(yè)級(jí)鎖定（page-level）

頁(yè)級(jí)鎖定是MySQL中比較獨(dú)特的一種鎖定級(jí)別，在其他數(shù)據(jù)庫(kù)管理軟件中也并不是太常見(jiàn)。頁(yè)級(jí)鎖定的特點(diǎn)是鎖定顆粒度介于行級(jí)鎖定與表級(jí)鎖之間，所以獲取鎖定所需要的資源開(kāi)銷，以及所能提供的并發(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ā)度也隨之提升。

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

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

當(dāng)出現(xiàn)Concurrent Insert之后，MySQL的開(kāi)發(fā)人員不得不修改之前系統(tǒng)中的鎖定實(shí)現(xiàn)功能，但是僅僅只是增加了對(duì)Concurrent Insert的支持，并沒(méi)有改動(dòng)整體架構(gòu)。可是在不久之后，隨著B(niǎo)erkeleyDB存儲(chǔ)引擎的引入，之前的鎖定機(jī)制遇到了更大的挑戰(zhàn)。因?yàn)锽erkeleyDB存儲(chǔ)引擎并沒(méi)有MyISAM和Memory存儲(chǔ)引擎同一時(shí)刻只允許單一線程訪問(wèn)某一個(gè)表的限制，而是將這個(gè)單線程訪問(wèn)限制的顆粒度縮小到了單個(gè)page，這又一次迫使MySQL開(kāi)發(fā)人員不得不再一次修改鎖定機(jī)制的實(shí)現(xiàn)。

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

表級(jí)鎖定

MySQL的表級(jí)鎖定主要分為兩種類型，一種是讀鎖定，另一種是寫(xiě)鎖定。在MySQL中，主要通過(guò)四個(gè)隊(duì)列來(lái)維護(hù)這兩種鎖定：兩個(gè)存放當(dāng)前正在鎖定中的讀和寫(xiě)鎖定信息，另外兩個(gè)存放等待中的讀寫(xiě)鎖定信息，如下：

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

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

讀鎖定

一個(gè)新的客戶端請(qǐng)求在申請(qǐng)獲取讀鎖定資源的時(shí)候，需要滿足兩個(gè)條件：

1、請(qǐng)求鎖定的資源當(dāng)前沒(méi)有被寫(xiě)鎖定；
2、寫(xiě)鎖定等待隊(duì)列（Pendingwrite-lockqueue）中沒(méi)有更高優(yōu)先級(jí)的寫(xiě)鎖定等待；

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

寫(xiě)鎖定

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

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

1. 請(qǐng)求鎖定的類型為WRITE_DELAYED;
2. 請(qǐng)求鎖定的類型為WRITE_CONCURRENT_INSERT或者是TL_WRITE_ALLOW_WRITE，同時(shí)Currentreadlock是READ_NO_INSERT的鎖定類型。

當(dāng)遇到這兩種特殊情況的時(shí)候，寫(xiě)鎖定會(huì)立即獲得而進(jìn)入Current write-lock queue 中

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

讀請(qǐng)求和寫(xiě)等待隊(duì)列中的寫(xiě)鎖請(qǐng)求的優(yōu)先級(jí)規(guī)則主要為以下規(guī)則決定：

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

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

1. 在某些存儲(chǔ)引擎的允許下，可以允許一個(gè)WRITE_CONCURRENT_INSERT寫(xiě)鎖定請(qǐng)求
2. 寫(xiě)鎖定為WRITE_ALLOW_WRITE的時(shí)候，允許除了WRITE_ONLY之外的所有讀和寫(xiě)鎖定請(qǐng)求
3. 寫(xiě)鎖定為WRITE_ALLOW_READ的時(shí)候，允許除了READ_NO_INSERT之外的所有讀鎖定請(qǐng)求
4. 寫(xiě)鎖定為WRITE_DELAYED的時(shí)候，允許除了READ_NO_INSERT之外的所有讀鎖定請(qǐng)求
5. 寫(xiě)鎖定為WRITE_CONCURRENT_INSERT的時(shí)候，允許除了READ_NO_INSERT之外的所有讀鎖定請(qǐng)求

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

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

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

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

行級(jí)鎖定

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

Innodb 鎖定模式及實(shí)現(xiàn)機(jī)制

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

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

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

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

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

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

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

當(dāng)Quuery使用的索引并不包含所有過(guò)濾條件的時(shí)候，數(shù)據(jù)檢索使用到的索引鍵所只想的數(shù)據(jù)可能有部分并不屬于該Query的結(jié)果集的行列，但是也會(huì)被鎖定，因?yàn)殚g隙鎖鎖定的是一個(gè)范圍，而不是具體的索引鍵；

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

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

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

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

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

Innodb 鎖定機(jī)制示例

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

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

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

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

 1、縮短鎖定時(shí)間

縮短鎖定時(shí)間，短短幾個(gè)字，說(shuō)起來(lái)確實(shí)聽(tīng)容易的，但實(shí)際做起來(lái)恐怕就并不那么簡(jiǎn)單了。如何讓鎖定時(shí)間盡可能的短呢？唯一的辦法就是讓我們的Query執(zhí)行時(shí)間盡可能的短。

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

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

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

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

2、分離能并行的操作

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

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

concurrent_insert=2，無(wú)論MyISAM存儲(chǔ)引擎的表數(shù)據(jù)文件的中間部分是否存在因?yàn)閯h除數(shù)據(jù)而留下的空閑空間，都允許在數(shù)據(jù)文件尾部進(jìn)行ConcurrentInsert;

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

concurrent_insert=0，無(wú)論MyISAM存儲(chǔ)引擎的表數(shù)據(jù)文件的中間部分是否存在因?yàn)閯h除數(shù)據(jù)而留下的空閑空間，都不允許ConcurrentInsert。

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

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

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

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

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

要想合理利用Innodb的行級(jí)鎖定，做到揚(yáng)長(zhǎng)避短，我們必須做好以下工作：

盡可能讓所有的數(shù)據(jù)檢索都通過(guò)索引來(lái)完成，從而避免Innodb因?yàn)闊o(wú)法通過(guò)索引鍵加鎖而升級(jí)為表級(jí)鎖定；

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

盡可能減少基于范圍的數(shù)據(jù)檢索過(guò)濾條件，避免因?yàn)殚g隙鎖帶來(lái)的負(fù)面影響而鎖定了不該鎖定的記錄；

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

在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級(jí)別的事務(wù)隔離，以減少M(fèi)ySQL因?yàn)閷?shí)現(xiàn)事務(wù)隔離級(jí)別所帶來(lái)的附加成本；

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

的的小建議，讀者朋友可以根據(jù)各自的業(yè)務(wù)特點(diǎn)針對(duì)性的嘗試：a)類似業(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)生的概率；

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

MySQL 實(shí)現(xiàn)的表級(jí)鎖定的爭(zhēng)用狀態(tài)變量：

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

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

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

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

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

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

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)在等待最常的一次所花的時(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ì)劃。

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

1.通過(guò)創(chuàng)建InnodbMonitor表來(lái)打開(kāi)Innodb的monitor功能：

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

可能會(huì)有讀者朋友問(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)一步分析使用。

小結(jié)

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