前言
為了保證數據的一致完整性，任何一個數據庫都存在鎖定機制。鎖定機制的優劣直接應想到一個數據庫系統的並發處理能力和性能，所以鎖定機制的實現也就成為了各種數據庫的核心技術之一。本章將對MySQL中兩種使用最為頻繁的存儲引擎MyISAM和Innodb各自的鎖定機制進行較為詳細的分析。
MySQL鎖定機制簡介
數據庫鎖定機制簡單來說就是數據庫為了保證數據的一致性而使各種共享資源在被並發訪問訪問變得有序所設計的一種規則。對於任何一種數據庫來說都需要有相應的鎖定機制，所以MySQL自然也不能例外。MySQL數據庫由於其自身架構的特點，存在多種數據存儲引擎，每種存儲引擎所針對的應用場景特點都不太一樣，為了滿足各自特定應用場景的需求，每種存儲引擎的鎖定機制都是為各自所面對的特定場景而優化設計，所以各存儲引擎的鎖定機制也有較大區別。
總的來說，MySQL各存儲引擎使用了三種類型（級別）的鎖定機制：行級鎖定，頁級鎖定和表級鎖定。下面我們先分析一下MySQL這三種鎖定的特點和各自的優劣所在。
行級鎖定（row-level）
行級鎖定最大的特點就是鎖定對象的顆粒度很小，也是目前各大數據庫管理軟件所實現的鎖定顆粒度最小的。由於鎖定顆粒度很小，所以發生鎖定資源爭用的概率也最小，能夠給予應用程序盡可能大的並發處理能力而提高一些需要高並發應用系統的整體性能。
雖然能夠在並發處理能力上面有較大的優勢，但是行級鎖定也因此帶來了不少弊端。由於鎖定資源的顆粒度很小，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了。此外，行級鎖定也最容易發生死鎖。
表級鎖定（table-level）
和行級鎖定相反，表級別的鎖定是MySQL各存儲引擎中最大顆粒度的鎖定機制。該鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小。所以獲取鎖和釋放鎖的速度很快。由於表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。
當然，鎖定顆粒度大所帶來最大的負面影響就是出現鎖定資源爭用的概率也會最高，致使並大度大打折扣。
頁級鎖定（page-level）
頁級鎖定是MySQL中比較獨特的一種鎖定級別，在其他數據庫管理軟件中也並不是太常見。頁級鎖定的特點是鎖定顆粒度介於行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的並發處理能力也同樣是介於上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發生死鎖。
在數據庫實現資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同數據量的數據所需要消耗的內存數量是越來越多的，實現算法也會越來越復雜。不過，隨著鎖定資源顆粒度的減小，應用程序的訪問請求遇到鎖等待的可能性也會隨之降低，系統整體並發度也隨之提升。
在MySQL數據庫中，使用表級鎖定的主要是MyISAM，Memory，CSV等一些非事務性存儲引擎，而使用行級鎖定的主要是Innodb存儲引擎和NDBCluster存儲引擎，頁級鎖定主要是BerkeleyDB存儲引擎的鎖定方式。
MySQL的如此的鎖定機制主要是由於其最初的歷史所決定的。在最初，MySQL希望設計一種完全獨立於各種存儲引擎的鎖定機制，而且在早期的MySQL數據庫中，MySQL的存儲引擎（MyISAM和Momery）的設計是建立在“任何表在同一時刻都只允許單個線程對其訪問（包括讀）”這樣的假設之上。但是，隨著MySQL的不斷完善，系統的不斷改進，在MySQL3.23版本開發的時候，MySQL開發人員不得不修正之前的假設。因為他們發現一個線程正在讀某個表的時候，另一個線程是可以對該表進行insert操作的，只不過只能INSERT到數據文件的最尾部。這也就是從MySQL從3.23版本開始提供的我們所說的Concurrent Insert。
當出現Concurrent Insert之後，MySQL的開發人員不得不修改之前系統中的鎖定實現功能，但是僅僅只是增加了對Concurrent Insert的支持，並沒有改動整體架構。可是在不久之後，隨著BerkeleyDB存儲引擎的引入，之前的鎖定機制遇到了更大的挑戰。因為BerkeleyDB存儲引擎並沒有MyISAM和Memory存儲引擎同一時刻只允許單一線程訪問某一個表的限制，而是將這個單線程訪問限制的顆粒度縮小到了單個page，這又一次迫使MySQL開發人員不得不再一次修改鎖定機制的實現。
由於新的存儲引擎的引入，導致鎖定機制不能滿足要求，讓MySQL的人意識到已經不可能實現一種完全獨立的滿足各種存儲引擎要求的鎖定實現機制。如果因為鎖定機制的拙劣實現而導致存儲引擎的整體性能的下降，肯定會嚴重打擊存儲引擎提供者的積極性，這是MySQL公司非常不願意看到的，因為這完全不符合MySQL的戰略發展思路。所以工程師們不得不放棄了最初的設計初衷，在鎖定實現機制中作出修改，允許存儲引擎自己改變MySQL通過接口傳入的鎖定類型而自行決定該怎樣鎖定數據。
表級鎖定
MySQL的表級鎖定主要分為兩種類型，一種是讀鎖定，另一種是寫鎖定。在MySQL中，主要通過四個隊列來維護這兩種鎖定：兩個存放當前正在鎖定中的讀和寫鎖定信息，另外兩個存放等待中的讀寫鎖定信息，如下：
復制代碼 代碼如下:
Current read-lock queue (lock->read)
Pending read-lock queue (lock->read_wait)
Current write-lock queue (lock->write)
Pending write-lock queue (lock->write_wait)
當前持有讀鎖的所有線程的相關信息都能夠在Currentread-lockqueue中找到，隊列中的信息按照獲取到鎖的時間依序存放。而正在等待鎖定資源的信息則存放在Pendingread-lockqueue裡面，另外兩個存放寫鎖信息的隊列也按照上面相同規則來存放信息。
雖然對於我們這些使用者來說MySQL展現出來的鎖定（表鎖定）只有讀鎖定和寫鎖定這兩種類型，但是在MySQL內部實現中卻有多達11種鎖定類型，由系統中一個枚舉量（thr_lock_type）定義，各值描述如下：
鎖定類型
說明
IGNORE
當發生鎖請求的時候內部交互使用，在鎖定結構和隊列中並不會有任何信息存儲
UNLOCK
釋放鎖定請求的交互用所類型
READ
普通讀鎖定
WRITE
普通寫鎖定
READ_WITH_SHARED_LOCKS
在Innodb中使用到，由如下方式產生如：SELECT...LOCKINSHAREMODE
READ_HIGH_PRIORITY
高優先級讀鎖定
READ_NO_INSERT
不允許ConcurentInsert的鎖定
WRITE_ALLOW_WRITE
這個類型實際上就是當由存儲引擎自行處理鎖定的時候，mysqld允許其他的線程再獲取讀或者寫鎖定，因為即使資源沖突，存儲引擎自己也會知道怎麼來處理
WRITE_ALLOW_READ
這種鎖定發生在對表做DDL（ALTERTABLE...）的時候，MySQL可以允許其他線程獲取讀鎖定，因為MySQL是通過重建整個表然後再RENAME而實現的該功能，所在整個過程原表仍然可以提供讀服務
WRITE_CONCURRENT_INSERT
正在進行ConcurentInsert時候所使用的鎖定方式，該鎖定進行的時候，除了READ_NO_INSERT之外的其他任何讀鎖定請求都不會被阻塞
WRITE_DELAYED
在使用INSERTDELAYED時候的鎖定類型
WRITE_LOW_PRIORITY
顯示聲明的低級別鎖定方式，通過設置LOW_PRIORITY_UPDAT=1而產生
WRITE_ONLY
當在操作過程中某個鎖定異常中斷之後系統內部需要進行CLOSETABLE操作，在這個過程中出現的鎖定類型就是WRITE_ONLY
讀鎖定
一個新的客戶端請求在申請獲取讀鎖定資源的時候，需要滿足兩個條件：
1、請求鎖定的資源當前沒有被寫鎖定；
2、寫鎖定等待隊列（Pendingwrite-lockqueue）中沒有更高優先級的寫鎖定等待；
如果滿足了上面兩個條件之後，該請求會被立即通過，並將相關的信息存入Currentread-lockqueue中，而如果上面兩個條件中任何一個沒有滿足，都會被迫進入等待隊列Pendingread-lockqueue中等待資源的釋放。
寫鎖定
當客戶端請求寫鎖定的時候，MySQL首先檢查在Currentwrite-lockqueue是否已經有鎖定相同資源的信息存在。
如果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中已經存在了鎖定相同資源的寫鎖定存在，那麼就只能進入等待隊列等待相應資源鎖定的釋放了。
讀請求和寫等待隊列中的寫鎖請求的優先級規則主要為以下規則決定：
1. 除了READ_HIGH_PRIORITY的讀鎖定之外，Pendingwrite-lockqueue中的WRITE寫鎖定能夠阻塞所有其他的讀鎖定；
2. READ_HIGH_PRIORITY讀鎖定的請求能夠阻塞所有Pendingwrite-lockqueue中的寫鎖定；
3. 除了WRITE寫鎖定之外，Pendingwrite-lockqueue中的其他任何寫鎖定都比讀鎖定的優先級低。
寫鎖定出現在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存儲引擎的不斷發展，目前MySQL自身提供的鎖定機制已經沒有辦法滿足需求了，很多存儲引擎都在MySQL所提供的鎖定機制之上做了存儲引擎自己的擴展和改造。
MyISAM存儲引擎基本上可以說是對MySQL所提供的鎖定機制所實現的表級鎖定依賴最大的一種存儲引擎了，雖然MyISAM存儲引擎自己並沒有在自身增加其他的鎖定機制，但是為了更好的支持相關特性，MySQL在原有鎖定機制的基礎上為了支持其ConcurrentInsert的特性而進行了相應的實現改造。
而其他幾種支持事務的存儲存儲引擎，如Innodb，NDBCluster以及BerkeleyDB存儲引擎則是讓MySQL將鎖定的處理直接交給存儲引擎自己來處理，在MySQL中僅持有WRITE_ALLOW_WRITE類型的鎖定。
由於MyISAM存儲引擎使用的鎖定機制完全是由MySQL提供的表級鎖定實現，所以下面我們將以MyISAM存儲引擎作為示例存儲引擎，來實例演示表級鎖定的一些基本特性。由於，為了讓示例更加直觀，我將使用顯示給表加鎖來演示：RITE_ALLOW_READ 類型的寫鎖定。
刻
Session a
Session b
行鎖定基本演示
1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b = 'b1' where a = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新，但是不提交
2
mysql> update test_innodb_lock set b = 'b1' where a = 1;
被阻塞，等待
3
mysql> commit; Query OK, 0 rows affected (0.05 sec) 提交
4
mysql> update test_innodb_lock set b = 'b1' where a = 1;
Query OK, 0 rows affected (36.14 sec)
Rows matched: 1 Changed: 0 Warnings: 0
解除阻塞，更新正常進行
無索引升級為表鎖演示
5
mysql> update test_innodb_lock set b = '2' where b = 2000;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_innodb_lock set b = '3' where b = 3000;
被阻塞，等待
6
7
mysql> commit; Query OK, 0 rows affected (0.10 sec)
8
mysql> update test_innodb_lock set b = '3' where b = 3000;
Query OK, 1 row affected (1 min 3.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
阻塞解除，完成更新
間隙鎖帶來的插入問題演示
9
mysql> select * from test_innodb_lock;
+------+------+ | a | b |+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
mysql> update test_innodb_lock set b = a * 100 where a < 4 and a > 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
10
mysql> insert into test_innodb_lock values(2,'200');
被阻塞，等待
11
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
12
mysql> insert into test_innodb_lock values(2,'200');
Query OK, 1 row affected (38.68 sec)
阻塞解除，完成插入
使用共同索引不同數據的阻塞示例
13
mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
14
mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; 被阻塞
15
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
16
mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; Query OK, 1 row affected (42.89 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 提交事務，阻塞去除，更新完成
死鎖示例
17
mysql> update t1 set id = 110 where id = 11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
18
mysql> update t2 set id = 210 where id = 21;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
19
mysql>update t2 set id=2100 where id=21;
等待sessionb釋放資源，被阻塞
20
mysql>update t1 set id=1100 where id=11;
Query OK,0 rows affected (0.39sec)
Rows matched: 0 Changed: 0 Warnings:0
等待sessiona釋放資源，被阻塞
兩個 session 互相等等待對方的資源釋放之後才能釋放自己的資源,造成了死鎖
行級鎖定
行級鎖定不是MySQL自己實現的鎖定方式，而是由其他存儲引擎自己所實現的，如廣為大家所知的Innodb存儲引擎，以及MySQL的分布式存儲引擎NDBCluster等都是實現了行級鎖定。
Innodb 鎖定模式及實現機制
考慮到行級鎖定君由各個存儲引擎自行實現，而且具體實現也各有差別，而Innodb是目前事務型存儲引擎中使用最為廣泛的存儲引擎，所以這裡我們就主要分析一下Innodb的鎖定特性。
總的來說，Innodb的鎖定機制和Oracle數據庫有不少相似之處。Innodb的行級鎖定同樣分為兩種類型，共享鎖和排他鎖，而在鎖定機制的實現過程中為了讓行級鎖定和表級鎖定共存，Innodb也同樣使用了意向鎖（表級鎖定）的概念，也就有了意向共享鎖和意向排他鎖這兩種。
當一個事務需要給自己需要的某個資源加鎖的時候，如果遇到一個共享鎖正鎖定著自己需要的資源的時候，自己可以再加一個共享鎖，不過不能加排他鎖。但是，如果遇到自己需要鎖定的資源已經被一個排他鎖占有之後，則只能等待該鎖定釋放資源之後自己才能獲取鎖定資源並添加自己的鎖定。而意向鎖的作用就是當一個事務在需要獲取資源鎖定的時候，如果遇到自己需要的資源已經被排他鎖占用的時候，該事務可以需要鎖定行的表上面添加一個合適的意向鎖。如果自己需要一個共享鎖，那麼就在表上面添加一個意向共享鎖。而如果自己需要的是某行（或者某些行）上面添加一個排他鎖的話，則先在表上面添加一個意向排他鎖。意向共享鎖可以同時並存多個，但是意向排他鎖同時只能有一個存在。所以，可以說Innodb的鎖定模式實際上可以分為四種：共享鎖（S），排他鎖（X），意向共享鎖（IS）和意向排他鎖（IX），我們可以通過以下表格來總結上面這四種所的共存邏輯關系：
共享鎖（S）
排他鎖（X）
意向共享鎖（IS）
意向排他鎖（IX）
共享鎖（S）
兼容
沖突
兼容
沖突
排他鎖（X）
沖突
沖突
沖突
沖突
意向共享鎖（IS）
兼容
沖突
兼容
兼容
意向排他鎖（IX）
沖突
沖突
兼容
兼容
雖然Innodb的鎖定機制和Oracle有不少相近的地方，但是兩者的實現確是截然不同的。總的來說就是Oracle鎖定數據是通過需要鎖定的某行記錄所在的物理block上的事務槽上表級鎖定信息，而Innodb的鎖定則是通過在指向數據記錄的第一個索引鍵之前和最後一個索引鍵之後的空域空間上標記鎖定信息而實現的。Innodb的這種鎖定實現方式被稱為“NEXT-KEYlocking”（間隙鎖），因為Query執行過程中通過過范圍查找的華，他會鎖定整個范圍內所有的索引鍵值，即使這個鍵值並不存在。
間隙鎖有一個比較致命的弱點，就是當鎖定一個范圍鍵值之後，即使某些不存在的鍵值也會被無辜的鎖定，而造成在鎖定的時候無法插入鎖定鍵值范圍內的任何數據。在某些場景下這可能會對性能造成很大的危害。而Innodb給出的解釋是為了組織幻讀的出現，所以他們選擇的間隙鎖來實現鎖定。
除了間隙鎖給Innodb帶來性能的負面影響之外，通過索引實現鎖定的方式還存在其他幾個較大的性能隱患：
當Query無法利用索引的時候，Innodb會放棄使用行級別鎖定而改用表級別的鎖定，造成並發性能的降低；
當Quuery使用的索引並不包含所有過濾條件的時候，數據檢索使用到的索引鍵所只想的數據可能有部分並不屬於該Query的結果集的行列，但是也會被鎖定，因為間隙鎖鎖定的是一個范圍，而不是具體的索引鍵；
當Query在使用索引定位數據的時候，如果使用的索引鍵一樣但訪問的數據行不同的時候（索引只是過濾條件的一部分），一樣會被鎖定
Innodb 各事務隔離級別下鎖定及死鎖
Innodb實現的在ISO／ANSISQL92規范中所定義的ReadUnCommited，ReadCommited，RepeatableRead和Serializable這四種事務隔離級別。同時，為了保證數據在事務中的一致性，實現了多版本數據訪問。
之前在第一節中我們已經介紹過，行級鎖定肯定會帶來死鎖問題，Innodb也不可能例外。至於死鎖的產生過程我們就不在這裡詳細描述了，在後面的鎖定示例中會通過一個實際的例子為大家愛展示死鎖的產生過程。這裡我們主要介紹一下，在Innodb中當系檢測到死鎖產生之後是如何來處理的。
在Innodb的事務管理和鎖定機制中，有專門檢測死鎖的機制，會在系統中產生死鎖之後的很短時間內就檢測到該死鎖的存在。當Innodb檢測到系統中產生了死鎖之後，Innodb會通過相應的判斷來選這產生死鎖的兩個事務中較小的事務來回滾，而讓另外一個較大的事務成功完成。那Innodb是以什麼來為標准判定事務的大小的呢？MySQL官方手冊中也提到了這個問題，實際上在Innodb發現死鎖之後，會計算出兩個事務各自插入、更新或者刪除的數據量來判定兩個事務的大小。也就是說哪個事務所改變的記錄條數越多，在死鎖中就越不會被回滾掉。但是有一點需要注意的就是，當產生死鎖的場景中涉及到不止Innodb存儲引擎的時候，Innodb是沒辦法檢測到該死鎖的，這時候就只能通過鎖定超時限制來解決該死鎖了。另外，死鎖的產生過程的示例將在本節最後的Innodb鎖定示例中演示。
Innodb 鎖定機制示例
復制代碼 代碼如下:
mysql> create table test_innodb_lock (a int(11),b varchar(16)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create index test_innodb_a_ind on test_innodb_lock(a);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index test_innodb_lock_b_ind on test_innodb_lock(b);
Query OK, 11 rows affected (0.01 sec)
Records: 11 Duplicates: 0 Warnings: 0
時刻
Session a
Session b
行鎖定基本演示
1
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> update test_innodb_lock set b = 'b1' where a = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
更新，但是不提交
2
mysql> update test_innodb_lock set b = 'b1' where a = 1;
被阻塞，等待
3
mysql> commit; Query OK, 0 rows affected (0.05 sec) 提交
4
mysql> update test_innodb_lock set b = 'b1' where a = 1;
Query OK, 0 rows affected (36.14 sec)
Rows matched: 1 Changed: 0 Warnings: 0
解除阻塞，更新正常進行
無索引升級為表鎖演示
5
mysql> update test_innodb_lock set b = '2' where b = 2000;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test_innodb_lock set b = '3' where b = 3000;
被阻塞，等待
6
7
mysql> commit; Query OK, 0 rows affected (0.10 sec)
8
mysql> update test_innodb_lock set b = '3' where b = 3000;
Query OK, 1 row affected (1 min 3.41 sec)
Rows matched: 1 Changed: 1 Warnings: 0
阻塞解除，完成更新
間隙鎖帶來的插入問題演示
9
mysql> select * from test_innodb_lock;
+------+------+ | a | b |+------+------+
| 1 | b2 |
| 3 | 3 |
| 4 | 4000 |
| 5 | 5000 |
| 6 | 6000 |
| 7 | 7000 |
| 8 | 8000 |
| 9 | 9000 |
| 1 | b1 |
+------+------+
9 rows in set (0.00 sec)
mysql> update test_innodb_lock set b = a * 100 where a < 4 and a > 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
10
mysql> insert into test_innodb_lock values(2,'200');
被阻塞，等待
11
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
12
mysql> insert into test_innodb_lock values(2,'200');
Query OK, 1 row affected (38.68 sec)
阻塞解除，完成插入
使用共同索引不同數據的阻塞示例
13
mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b2';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
14
mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; 被阻塞
15
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
16
mysql> update test_innodb_lock set b = 'bbbbb' where a = 1 and b = 'b1'; Query OK, 1 row affected (42.89 sec)
Rows matched: 1 Changed: 1 Warnings: 0
session 提交事務，阻塞去除，更新完成
死鎖示例
17
mysql> update t1 set id = 110 where id = 11;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
18
mysql> update t2 set id = 210 where id = 21;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
19
mysql>update t2 set id=2100 where id=21;
等待sessionb釋放資源，被阻塞
20
mysql>update t1 set id=1100 where id=11;
Query OK,0 rows affected (0.39sec)
Rows matched: 0 Changed: 0 Warnings:0
等待sessiona釋放資源，被阻塞
兩個 session 互相等等待對方的資源釋放之後才能釋放自己的資源,造成了死鎖
合理利用鎖機制優化MySQL
MyISAM 表鎖優化建議
對於MyISAM存儲引擎，雖然使用表級鎖定在鎖定實現的過程中比實現行級鎖定或者頁級鎖所帶來的附加成本都要小，鎖定本身所消耗的資源也是最少。但是由於鎖定的顆粒度比較到，所以造成鎖定資源的爭用情況也會比其他的鎖定級別都要多，從而在較大程度上會降低並發處理能力。
所以，在優化MyISAM存儲引擎鎖定問題的時候，最關鍵的就是如何讓其提高並發度。由於鎖定級別是不可能改變的了，所以我們首先需要盡可能讓鎖定的時間變短，然後就是讓可能並發進行的操作盡可能的並發。
1、縮短鎖定時間
縮短鎖定時間，短短幾個字，說起來確實聽容易的，但實際做起來恐怕就並不那麼簡單了。如何讓鎖定時間盡可能的短呢？唯一的辦法就是讓我們的Query執行時間盡可能的短。
盡兩減少大的復雜Query，將復雜Query分拆成幾個小的Query分布進行；
盡可能的建立足夠高效的索引，讓數據檢索更迅速；
盡量讓MyISAM存儲引擎的表只存放必要的信息，控制字段類型；
利用合適的機會優化MyISAM表數據文件；
2、分離能並行的操作
說到MyISAM的表鎖，而且是讀寫互相阻塞的表鎖，可能有些人會認為在MyISAM存儲引擎的表上就只能是完全的串行化，沒辦法再並行了。大家不要忘記了，MyISAM的存儲引擎還有一個非常有用的特性，那就是ConcurrentInsert（並發插入）的特性。
MyISAM存儲引擎有一個控制是否打開Concurrent Insert功能的參數選項：concurrent_insert，可以設置為0，1或者2。三個值的具體說明如下：
concurrent_insert=2，無論MyISAM存儲引擎的表數據文件的中間部分是否存在因為刪除數據而留下的空閒空間，都允許在數據文件尾部進行ConcurrentInsert;
concurrent_insert=1，當MyISAM存儲引擎表數據文件中間不存在空閒空間的時候，可以從文件尾部進行ConcurrentInsert;
concurrent_insert=0，無論MyISAM存儲引擎的表數據文件的中間部分是否存在因為刪除數據而留下的空閒空間，都不允許ConcurrentInsert。
3、合理利用讀寫優先級
在本章各種鎖定分析一節中我們了解到了MySQL的表級鎖定對於讀和寫是有不同優先級設定的，默認情況下是寫優先級要大於讀優先級。所以，如果我們可以根據各自系統環境的差異決定讀與寫的優先級。如果我們的系統是一個以讀為主，而且要優先保證查詢性能的話，我們可以通過設置系統參數選項low_priority_updates=1，將寫的優先級設置為比讀的優先級低，即可讓告訴MySQL盡量先處理讀請求。當然，如果我們的系統需要有限保證數據寫入的性能的話，則可以不用設置low_priority_updates參數了。
這裡我們完全可以利用這個特性，將concurrent_insert參數設置為1，甚至如果數據被刪除的可能性很小的時候，如果對暫時性的浪費少量空間並不是特別的在乎的話，將concurrent_insert參數設置為2都可以嘗試。當然，數據文件中間留有空域空間，在浪費空間的時候，還會造成在查詢的時候需要讀取更多的數據，所以如果刪除量不是很小的話，還是建議將concurrent_insert設置為1更為合適。
Innodb 行鎖優化建議
Innodb存儲引擎由於實現了行級鎖定，雖然在鎖定機制的實現方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體並發處理能力方面要遠遠優於MyISAM的表級鎖定的。當系統並發量較高的時候，Innodb的整體性能和MyISAM相比就會有比較明顯的優勢了。但是，Innodb的行級鎖定同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓Innodb的整體性能表現不僅不能比MyISAM高，甚至可能會更差。
要想合理利用Innodb的行級鎖定，做到揚長避短，我們必須做好以下工作：
盡可能讓所有的數據檢索都通過索引來完成，從而避免Innodb因為無法通過索引鍵加鎖而升級為表級鎖定；
合理設計索引，讓Innodb在索引鍵上面加鎖的時候盡可能准確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他Query的執行；
盡可能減少基於范圍的數據檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的記錄；
盡量控制事務的大小，減少鎖定的資源量和鎖定時間長度；
在業務環境允許的情況下，盡量使用較低級別的事務隔離，以減少MySQL因為實現事務隔離級別所帶來的附加成本；
由於Innodb的行級鎖定和事務性，所以肯定會產生死鎖，下面是一些比較常用的減少死鎖產生概率
的的小建議，讀者朋友可以根據各自的業務特點針對性的嘗試：a)類似業務模塊中，盡可能按照相同的訪問順序來訪問，防止產生死鎖；b)在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；c)對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；
系統鎖定爭用情況查詢對於兩種鎖定級別，MySQL內部有兩組專門的狀態變量記錄系統內部鎖資源爭用情況，我們先看看
MySQL 實現的表級鎖定的爭用狀態變量：
復制代碼 代碼如下:
mysql> show status like 'table%';
+-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+
| Table_locks_immediate | 100 |
| Table_locks_waited | 0 |
+-----------------------+-------+
這裡有兩個狀態變量記錄MySQL內部表級鎖定的情況，兩個變量說明如下：
Table_locks_immediate：產生表級鎖定的次數；
Table_locks_waited：出現表級鎖定爭用而發生等待的次數；
兩個狀態值都是從系統啟動後開始記錄，沒出現一次對應的事件則數量加1。如果這裡的Table_locks_waited狀態值比較高，那麼說明系統中表級鎖定爭用現象比較嚴重，就需要進一步分析為什麼會有較多的鎖定資源爭用了。
對於Innodb所使用的行級鎖定，系統中是通過另外一組更為詳細的狀態變量來記錄的，如下：
復制代碼 代碼如下:
mysql>showstatuslike'innodb_row_lock%';
+-------------------------------+--------+|Variable_name|Value|+-------------------------------+--------+
|Innodb_row_lock_current_waits|0|
|Innodb_row_lock_time|490578|
|Innodb_row_lock_time_avg|37736|
|Innodb_row_lock_time_max|121411|
|Innodb_row_lock_waits|13|
+-------------------------------+--------+
Innodb 的行級鎖定狀態變量不僅記錄了鎖定等待次數，還記錄了鎖定總時長，每次平均時長，以及最大時長，此外還有一個非累積狀態量顯示了當前正在等待鎖定的等待數量。對各個狀態量的說明如下：
Innodb_row_lock_current_waits：當前正在等待鎖定的數量；
Innodb_row_lock_time：從系統啟動到現在鎖定總時間長度；
Innodb_row_lock_time_avg：每次等待所花平均時間；
Innodb_row_lock_time_max：從系統啟動到現在等待最常的一次所花的時間；
Innodb_row_lock_waits：系統啟動後到現在總共等待的次數；
對於這5個狀態變量，比較重要的主要是Innodb_row_lock_time_avg（等待平均時長），Innodb_row_lock_waits（等待總次數）以及Innodb_row_lock_time（等待總時長）這三項。尤其是當等待次數很高，而且每次等待時長也不小的時候，我們就需要分析系統中為什麼會有如此多的等待，然後根據分析結果著手指定優化計劃。
此外，Innodb出了提供這五個系統狀態變量之外，還提供的其他更為豐富的即時狀態信息供我們分析使用。可以通過如下方法查看：
1.通過創建InnodbMonitor表來打開Innodb的monitor功能：
復制代碼 代碼如下:
mysql> create table innodb_monitor(a int) engine=innodb;
Query OK, 0 rows affected (0.07 sec)
2.然後通過使用“SHOWINNODBSTATUS”查看細節信息（由於輸出內容太多就不在此記錄了）；
可能會有讀者朋友問為什麼要先創建一個叫innodb_monitor的表呢？因為創建該表實際上就是告訴Innodb我們開始要監控他的細節狀態了，然後Innodb就會將比較詳細的事務以及鎖定信息記錄進入MySQL的errorlog中，以便我們後面做進一步分析使用。
小結
本章以MySQLServer中的鎖定簡介開始，分析了當前MySQL中使用最為廣泛的鎖定方式表級鎖定和行級鎖定的基本實現機制，並通過MyISAM和Innodb這兩大典型的存儲引擎作為示例存儲引擎所使用的表級鎖定和行級鎖定做了較為詳細的分析和演示。然後，再通過分析兩種鎖定方式的特性，給出相應的優化建議和策略。最後了解了一下在MySQLServer中如何獲得系統當前各種鎖定的資源爭用狀況。希望本章內容能夠對各位讀者朋友在理解MySQL鎖定機制方面有一定的幫助。
