快捷導(dǎo)航

Mysql數(shù)據(jù)庫鎖定機(jī)制詳細(xì)介紹

更新時(shí)間：2014年12月01日 10:58:48 投稿：junjie

這篇文章主要介紹了Mysql數(shù)據(jù)庫鎖定機(jī)制詳細(xì)介紹,本文用大量內(nèi)容講解了Mysql中的鎖定機(jī)制,例如MySQL鎖定機(jī)制簡介、合理利用鎖機(jī)制優(yōu)化MySQL等內(nèi)容,需要的朋友可以參考下

前言

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

MySQL鎖定機(jī)制簡介

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

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

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

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

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

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

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

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

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

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

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

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

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

當(dāng)出現(xiàn)Concurrent Insert之后，MySQL的開發(fā)人員不得不修改之前系統(tǒng)中的鎖定實(shí)現(xiàn)功能，但是僅僅只是增加了對(duì)Concurrent Insert的支持，并沒有改動(dòng)整體架構(gòu)。可是在不久之后，隨著BerkeleyDB存儲(chǔ)引擎的引入，之前的鎖定機(jī)制遇到了更大的挑戰(zhàn)。因?yàn)锽erkeleyDB存儲(chǔ)引擎并沒有MyISAM和Memory存儲(chǔ)引擎同一時(shí)刻只允許單一線程訪問某一個(gè)表的限制，而是將這個(gè)單線程訪問限制的顆粒度縮小到了單個(gè)page，這又一次迫使MySQL開發(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通過接口傳入的鎖定類型而自行決定該怎樣鎖定數(shù)據(jù)。

表級(jí)鎖定

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

復(fù)制代碼代碼如下:

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)

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

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

鎖定類型	說明
IGNORE	當(dāng)發(fā)生鎖請(qǐng)求的時(shí)候內(nèi)部交互使用，在鎖定結(jié)構(gòu)和隊(duì)列中并不會(huì)有任何信息存儲(chǔ)
UNLOCK	釋放鎖定請(qǐng)求的交互用所類型
READ	普通讀鎖定
WRITE	普通寫鎖定
READ_WITH_SHARED_LOCKS	在Innodb中使用到，由如下方式產(chǎn)生如：SELECT...LOCKINSHAREMODE
READ_HIGH_PRIORITY	高優(yōu)先級(jí)讀鎖定
READ_NO_INSERT	不允許ConcurentInsert的鎖定
WRITE_ALLOW_WRITE	這個(gè)類型實(shí)際上就是當(dāng)由存儲(chǔ)引擎自行處理鎖定的時(shí)候，mysqld允許其他的線程再獲取讀或者寫鎖定，因?yàn)榧词官Y源沖突，存儲(chǔ)引擎自己也會(huì)知道怎么來處理
WRITE_ALLOW_READ	這種鎖定發(fā)生在對(duì)表做DDL（ALTERTABLE...）的時(shí)候，MySQL可以允許其他線程獲取讀鎖定，因?yàn)镸ySQL是通過重建整個(gè)表然后再RENAME而實(shí)現(xiàn)的該功能，所在整個(gè)過程原表仍然可以提供讀服務(wù)
WRITE_CONCURRENT_INSERT	正在進(jìn)行ConcurentInsert時(shí)候所使用的鎖定方式，該鎖定進(jìn)行的時(shí)候，除了READ_NO_INSERT之外的其他任何讀鎖定請(qǐng)求都不會(huì)被阻塞
WRITE_DELAYED	在使用INSERTDELAYED時(shí)候的鎖定類型
WRITE_LOW_PRIORITY	顯示聲明的低級(jí)別鎖定方式，通過設(shè)置LOW_PRIORITY_UPDAT=1而產(chǎn)生
WRITE_ONLY	當(dāng)在操作過程中某個(gè)鎖定異常中斷之后系統(tǒng)內(nèi)部需要進(jìn)行CLOSETABLE操作，在這個(gè)過程中出現(xiàn)的鎖定類型就是WRITE_ONLY

讀鎖定

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

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

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

寫鎖定

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

如果Currentwrite-lockqueue沒有，則再檢查Pendingwrite-lockqueue，如果在Pendingwrite-lockqueue中找到了，自己也需要進(jìn)入等待隊(duì)列并暫停自身線程等待鎖定資源。反之，如果Pendingwrite-lockqueue為空，則再檢測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í)候，寫鎖定會(huì)立即獲得而進(jìn)入Current write-lock queue 中

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

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

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

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

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

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

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

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

由于MyISAM存儲(chǔ)引擎使用的鎖定機(jī)制完全是由MySQL提供的表級(jí)鎖定實(shí)現(xiàn)，所以下面我們將以MyISAM存儲(chǔ)引擎作為示例存儲(chǔ)引擎，來實(shí)例演示表級(jí)鎖定的一些基本特性。由于，為了讓示例更加直觀，我將使用顯示給表加鎖來演示：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 解除阻塞，更新正常進(jìn)行
	無索引升級(jí)為表鎖演示
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) 阻塞解除，完成插入
	使用共同索引不同數(shù)據(jù)的阻塞示例
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 提交事務(wù)，阻塞去除，更新完成
	死鎖示例
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釋放資源，被阻塞
	兩個(gè) session 互相等等待對(duì)方的資源釋放之后才能釋放自己的資源,造成了死鎖

行級(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的鎖定特性。

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

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

共享鎖（S）	排他鎖（X）	意向共享鎖（IS）	意向排他鎖（IX）
共享鎖（S）	兼容	沖突	兼容	沖突
排他鎖（X）	沖突	沖突	沖突	沖突
意向共享鎖（IS）	兼容	沖突	兼容	兼容
意向排他鎖（IX）	沖突	沖突	兼容	兼容

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

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

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

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

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

當(dāng)Query在使用索引定位數(shù)據(jù)的時(shí)候，如果使用的索引鍵一樣但訪問的數(shù)據(jù)行不同的時(shí)候（索引只是過濾條件的一部分），一樣會(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ù)訪問。

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

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

Innodb 鎖定機(jī)制示例

復(fù)制代碼代碼如下:

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

時(shí)刻	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 解除阻塞，更新正常進(jìn)行
	無索引升級(jí)為表鎖演示
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) 阻塞解除，完成插入
	使用共同索引不同數(shù)據(jù)的阻塞示例
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 提交事務(wù)，阻塞去除，更新完成
	死鎖示例
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釋放資源，被阻塞
	兩個(gè) session 互相等等待對(duì)方的資源釋放之后才能釋放自己的資源,造成了死鎖

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

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

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

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

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

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

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

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

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

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

2、分離能并行的操作

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

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

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

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

在本章各種鎖定分析一節(jié)中我們了解到了MySQL的表級(jí)鎖定對(duì)于讀和寫是有不同優(yōu)先級(jí)設(shè)定的，默認(rèn)情況下是寫優(yōu)先級(jí)要大于讀優(yōu)先級(jí)。所以，如果我們可以根據(jù)各自系統(tǒng)環(huán)境的差異決定讀與寫的優(yōu)先級(jí)。如果我們的系統(tǒng)是一個(gè)以讀為主，而且要優(yōu)先保證查詢性能的話，我們可以通過設(shè)置系統(tǒng)參數(shù)選項(xiàng)low_priority_updates=1，將寫的優(yōu)先級(jí)設(shè)置為比讀的優(yōu)先級(jí)低，即可讓告訴MySQL盡量先處理讀請(qǐng)求。當(dāng)然，如果我們的系統(tǒng)需要有限保證數(shù)據(jù)寫入的性能的話，則可以不用設(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)方面所帶來的性能損耗可能比表級(jí)鎖定會(huì)要更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM的表級(jí)鎖定的。當(dāng)系統(tǒng)并發(fā)量較高的時(shí)候，Innodb的整體性能和MyISAM相比就會(huì)有比較明顯的優(yōu)勢了。但是，Innodb的行級(jí)鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候，可能會(huì)讓Innodb的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會(huì)更差。

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

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

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

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

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

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

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

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

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

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

復(fù)制代碼代碼如下:

mysql> show status like 'table%'; 

+-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ 

| Table_locks_immediate | 100 |

| Table_locks_waited | 0 |

 +-----------------------+-------+

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

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

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

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

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

復(fù)制代碼代碼如下:

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

Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量；

Innodb_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長度；

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

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

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

復(fù)制代碼代碼如下:

mysql> create table innodb_monitor(a int) engine=innodb;

Query OK, 0 rows affected (0.07 sec)

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

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

小結(jié)

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

您可能感興趣的文章: