概述

數(shù)據(jù)庫(kù)鎖定機(jī)制簡(jiǎn)單來說布隔，就是數(shù)據(jù)庫(kù)為了保證數(shù)據(jù)的一致性物舒，而使各種共享資源在被并發(fā)訪問變得有序所設(shè)計(jì)的一種規(guī)則椭蹄。對(duì)于任何一種數(shù)據(jù)庫(kù)來說都需要有相應(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ū)別接癌。MySQL各存儲(chǔ)引擎使用了三種類型（級(jí)別）的鎖定機(jī)制：表級(jí)鎖定，行級(jí)鎖定和頁級(jí)鎖定扣讼。

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

表級(jí)別的鎖定是MySQL各存儲(chǔ)引擎中最大顆粒度的鎖定機(jī)制缺猛。該鎖定機(jī)制最大的特點(diǎn)是實(shí)現(xiàn)邏輯非常簡(jiǎn)單，帶來的系統(tǒng)負(fù)面影響最小椭符。所以獲取鎖和釋放鎖的速度很快荔燎。由于表級(jí)鎖一次會(huì)將整個(gè)表鎖定，所以可以很好的避免困擾我們的死鎖問題销钝。
當(dāng)然有咨，鎖定顆粒度大所帶來最大的負(fù)面影響就是出現(xiàn)鎖定資源爭(zhēng)用的概率也會(huì)最高，致使并大度大打折扣蒸健。
使用表級(jí)鎖定的主要是MyISAM座享，MEMORY婉商，CSV等一些非事務(wù)性存儲(chǔ)引擎。

2.行級(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í)鎖定也因此帶來了不少弊端滤祖。由于鎖定資源的顆粒度很小筷狼，所以每次獲取鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也就更大了匠童。此外埂材，行級(jí)鎖定也最容易發(fā)生死鎖。
使用行級(jí)鎖定的主要是InnoDB存儲(chǔ)引擎汤求。

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

頁級(jí)鎖定是MySQL中比較獨(dú)特的一種鎖定級(jí)別俏险，在其他數(shù)據(jù)庫(kù)管理軟件中也并不是太常見。頁級(jí)鎖定的特點(diǎn)是鎖定顆粒度介于行級(jí)鎖定與表級(jí)鎖之間扬绪，所以獲取鎖定所需要的資源開銷竖独，以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間。另外挤牛，頁級(jí)鎖定和行級(jí)鎖定一樣莹痢，會(huì)發(fā)生死鎖。
在數(shù)據(jù)庫(kù)實(shí)現(xiàn)資源鎖定的過程中墓赴，隨著鎖定資源顆粒度的減小竞膳，鎖定相同數(shù)據(jù)量的數(shù)據(jù)所需要消耗的內(nèi)存數(shù)量是越來越多的，實(shí)現(xiàn)算法也會(huì)越來越復(fù)雜诫硕。不過坦辟，隨著鎖定資源顆粒度的減小，應(yīng)用程序的訪問請(qǐng)求遇到鎖等待的可能性也會(huì)隨之降低章办，系統(tǒng)整體并發(fā)度也隨之提升锉走。
使用頁級(jí)鎖定的主要是BerkeleyDB存儲(chǔ)引擎。
總的來說藕届，MySQL這3種鎖的特性可大致歸納如下：
表級(jí)鎖：開銷小挪蹭，加鎖快；不會(huì)出現(xiàn)死鎖翰舌；鎖定粒度大嚣潜，發(fā)生鎖沖突的概率最高冬骚，并發(fā)度最低椅贱；

行級(jí)鎖：開銷大懂算，加鎖慢；會(huì)出現(xiàn)死鎖庇麦；鎖定粒度最小计技，發(fā)生鎖沖突的概率最低，并發(fā)度也最高山橄；

頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間垮媒；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間航棱，并發(fā)度一般睡雇。

適用：從鎖的角度來說，表級(jí)鎖更適合于以查詢?yōu)橹饕迹挥猩倭堪此饕龡l件更新數(shù)據(jù)的應(yīng)用它抱，如Web應(yīng)用；而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)朴艰，同時(shí)又有并發(fā)查詢的應(yīng)用观蓄，如一些在線事務(wù)處理（OLTP）系統(tǒng)。

表級(jí)鎖定

由于MyISAM存儲(chǔ)引擎使用的鎖定機(jī)制完全是由MySQL提供的表級(jí)鎖定實(shí)現(xiàn)祠墅，所以下面我們將以MyISAM存儲(chǔ)引擎作為示例存儲(chǔ)引擎侮穿。

1.MySQL表級(jí)鎖的鎖模式

MySQL的表級(jí)鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨(dú)占寫鎖（Table Write Lock）。鎖模式的兼容性：
對(duì)MyISAM表的讀操作毁嗦，不會(huì)阻塞其他用戶對(duì)同一表的讀請(qǐng)求亲茅，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求；
對(duì)MyISAM表的寫操作狗准，則會(huì)阻塞其他用戶對(duì)同一表的讀和寫操作芯急；
MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的驶俊。當(dāng)一個(gè)線程獲得對(duì)一個(gè)表的寫鎖后娶耍，只有持有鎖的線程可以對(duì)表進(jìn)行更新操作。其他線程的讀饼酿、寫操作都會(huì)等待榕酒，直到鎖被釋放為止。

2.如何加表鎖

MyISAM在執(zhí)行查詢語句（SELECT）前故俐，會(huì)自動(dòng)給涉及的所有表加讀鎖想鹰，在執(zhí)行更新操作（UPDATE、DELETE药版、INSERT等）前辑舷，會(huì)自動(dòng)給涉及的表加寫鎖，這個(gè)過程并不需要用戶干預(yù)槽片，因此何缓，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖肢础。

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

對(duì)于MyISAM存儲(chǔ)引擎，雖然使用表級(jí)鎖定在鎖定實(shí)現(xiàn)的過程中比實(shí)現(xiàn)行級(jí)鎖定或者頁級(jí)鎖所帶來的附加成本都要小碌廓，鎖定本身所消耗的資源也是最少传轰。但是由于鎖定的顆粒度比較到，所以造成鎖定資源的爭(zhēng)用情況也會(huì)比其他的鎖定級(jí)別都要多谷婆，從而在較大程度上會(huì)降低并發(fā)處理能力慨蛙。所以，在優(yōu)化MyISAM存儲(chǔ)引擎鎖定問題的時(shí)候纪挎，最關(guān)鍵的就是如何讓其提高并發(fā)度期贫。由于鎖定級(jí)別是不可能改變的了，所以我們首先需要盡可能讓鎖定的時(shí)間變短异袄，然后就是讓可能并發(fā)進(jìn)行的操作盡可能的并發(fā)唯灵。
（1）查詢表級(jí)鎖爭(zhēng)用情況
MySQL內(nèi)部有兩組專門的狀態(tài)變量記錄系統(tǒng)內(nèi)部鎖資源爭(zhēng)用情況：

mysql> show status like 'table%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Table_locks_immediate      | 100     |
| Table_locks_waited         | 11      |
+----------------------------+---------+

這里有兩個(gè)狀態(tài)變量記錄MySQL內(nèi)部表級(jí)鎖定的情況，兩個(gè)變量說明如下：
Table_locks_immediate：產(chǎn)生表級(jí)鎖定的次數(shù)隙轻；
Table_locks_waited：出現(xiàn)表級(jí)鎖定爭(zhēng)用而發(fā)生等待的次數(shù)埠帕；
兩個(gè)狀態(tài)值都是從系統(tǒng)啟動(dòng)后開始記錄，出現(xiàn)一次對(duì)應(yīng)的事件則數(shù)量加1玖绿。如果這里的Table_locks_waited狀態(tài)值比較高敛瓷，那么說明系統(tǒng)中表級(jí)鎖定爭(zhēng)用現(xiàn)象比較嚴(yán)重，就需要進(jìn)一步分析為什么會(huì)有較多的鎖定資源爭(zhēng)用了斑匪。
（2）縮短鎖定時(shí)間
如何讓鎖定時(shí)間盡可能的短呢呐籽？唯一的辦法就是讓我們的Query執(zhí)行時(shí)間盡可能的短。
a)盡兩減少大的復(fù)雜Query蚀瘸，將復(fù)雜Query分拆成幾個(gè)小的Query分布進(jìn)行狡蝶；
b)盡可能的建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速贮勃；
c)盡量讓MyISAM存儲(chǔ)引擎的表只存放必要的信息贪惹，控制字段類型；
d)利用合適的機(jī)會(huì)優(yōu)化MyISAM表數(shù)據(jù)文件寂嘉。
（3）分離能并行的操作
說到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表中有沒有空洞，都允許在表尾并發(fā)插入記錄骆撇；
concurrent_insert=1瞒御，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行）父叙，MyISAM允許在一個(gè)進(jìn)程讀表的同時(shí)神郊，另一個(gè)進(jìn)程從表尾插入記錄。這也是MySQL的默認(rèn)設(shè)置趾唱；
concurrent_insert=0涌乳，不允許并發(fā)插入。
可以利用MyISAM存儲(chǔ)引擎的并發(fā)插入特性甜癞，來解決應(yīng)用中對(duì)同一表查詢和插入的鎖爭(zhēng)用夕晓。例如，將concurrent_insert系統(tǒng)變量設(shè)為2悠咱，總是允許并發(fā)插入蒸辆；同時(shí)，通過定期在系統(tǒng)空閑時(shí)段執(zhí)行OPTIMIZE TABLE語句來整理空間碎片析既，收回因刪除記錄而產(chǎn)生的中間空洞躬贡。
（4）合理利用讀寫優(yōu)先級(jí)
MyISAM存儲(chǔ)引擎的是讀寫互相阻塞的，那么眼坏，一個(gè)進(jìn)程請(qǐng)求某個(gè)MyISAM表的讀鎖拂玻，同時(shí)另一個(gè)進(jìn)程也請(qǐng)求同一表的寫鎖，MySQL如何處理呢宰译？
答案是寫進(jìn)程先獲得鎖檐蚜。不僅如此，即使讀請(qǐng)求先到鎖等待隊(duì)列沿侈，寫請(qǐng)求后到闯第，寫鎖也會(huì)插到讀鎖請(qǐng)求之前。
這是因?yàn)镸ySQL的表級(jí)鎖定對(duì)于讀和寫是有不同優(yōu)先級(jí)設(shè)定的缀拭，默認(rèn)情況下是寫優(yōu)先級(jí)要大于讀優(yōu)先級(jí)乡括。
所以，如果我們可以根據(jù)各自系統(tǒng)環(huán)境的差異決定讀與寫的優(yōu)先級(jí)：
通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1智厌，使該連接讀比寫的優(yōu)先級(jí)高诲泌。如果我們的系統(tǒng)是一個(gè)以讀為主，可以設(shè)置此參數(shù)铣鹏，如果以寫為主敷扫，則不用設(shè)置；
通過指定INSERT、UPDATE葵第、DELETE語句的LOW_PRIORITY屬性绘迁，降低該語句的優(yōu)先級(jí)。
雖然上面方法都是要么更新優(yōu)先卒密，要么查詢優(yōu)先的方法缀台，但還是可以用其來解決查詢相對(duì)重要的應(yīng)用（如用戶登錄系統(tǒng)）中，讀鎖等待嚴(yán)重的問題哮奇。
另外膛腐，MySQL也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個(gè)合適的值鼎俘，當(dāng)一個(gè)表的讀鎖達(dá)到這個(gè)值后哲身，MySQL就暫時(shí)將寫請(qǐng)求的優(yōu)先級(jí)降低，給讀進(jìn)程一定獲得鎖的機(jī)會(huì)贸伐。
這里還要強(qiáng)調(diào)一點(diǎn)：一些需要長(zhǎng)時(shí)間運(yùn)行的查詢操作勘天，也會(huì)使寫進(jìn)程“餓死”，因此捉邢，應(yīng)用中應(yīng)盡量避免出現(xiàn)長(zhǎng)時(shí)間運(yùn)行的查詢操作脯丝，不要總想用一條SELECT語句來解決問題，因?yàn)檫@種看似巧妙的SQL語句伏伐，往往比較復(fù)雜宠进，執(zhí)行時(shí)間較長(zhǎng)，在可能的情況下可以通過使用中間表等措施對(duì)SQL語句做一定的“分解”秘案，使每一步查詢都能在較短時(shí)間完成砰苍，從而減少鎖沖突。如果復(fù)雜查詢不可避免阱高，應(yīng)盡量安排在數(shù)據(jù)庫(kù)空閑時(shí)段執(zhí)行赚导，比如一些定期統(tǒng)計(jì)可以安排在夜間執(zhí)行。

行級(jí)鎖定

行級(jí)鎖定不是MySQL自己實(shí)現(xiàn)的鎖定方式赤惊，而是由其他存儲(chǔ)引擎自己所實(shí)現(xiàn)的吼旧，如廣為大家所知的InnoDB存儲(chǔ)引擎，以及MySQL的分布式存儲(chǔ)引擎NDBCluster等都是實(shí)現(xiàn)了行級(jí)鎖定未舟∪Π担考慮到行級(jí)鎖定君由各個(gè)存儲(chǔ)引擎自行實(shí)現(xiàn)，而且具體實(shí)現(xiàn)也各有差別裕膀，而InnoDB是目前事務(wù)型存儲(chǔ)引擎中使用最為廣泛的存儲(chǔ)引擎员串，所以這里我們就主要分析一下InnoDB的鎖定特性。

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

考慮到行級(jí)鎖定君由各個(gè)存儲(chǔ)引擎自行實(shí)現(xiàn)昼扛，而且具體實(shí)現(xiàn)也各有差別寸齐，而InnoDB是目前事務(wù)型存儲(chǔ)引擎中使用最為廣泛的存儲(chǔ)引擎，所以這里我們就主要分析一下InnoDB的鎖定特性。
總的來說渺鹦，InnoDB的鎖定機(jī)制和Oracle數(shù)據(jù)庫(kù)有不少相似之處扰法。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)系：

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容慧脱，InnoDB就將請(qǐng)求的鎖授予該事務(wù)；反之贺喝，如果兩者不兼容菱鸥，該事務(wù)就要等待鎖釋放。
意向鎖是InnoDB自動(dòng)加的躏鱼，不需用戶干預(yù)氮采。對(duì)于UPDATE、DELETE和INSERT語句染苛，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X)鹊漠；對(duì)于普通SELECT語句，InnoDB不會(huì)加任何鎖；事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排他鎖贸呢。

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

用SELECT ... IN SHARE MODE獲得共享鎖镰烧，主要用在需要數(shù)據(jù)依存關(guān)系時(shí)來確認(rèn)某行記錄是否存在，并確保沒有人對(duì)這個(gè)記錄進(jìn)行UPDATE或者DELETE操作楞陷。
但是如果當(dāng)前事務(wù)也需要對(duì)該記錄進(jìn)行更新操作怔鳖，則很有可能造成死鎖，對(duì)于鎖定行記錄后需要進(jìn)行更新操作的應(yīng)用固蛾，應(yīng)該使用SELECT... FOR UPDATE方式獲得排他鎖结执。

2.InnoDB行鎖實(shí)現(xiàn)方式

InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，只有通過索引條件檢索數(shù)據(jù)艾凯，InnoDB才使用行級(jí)鎖献幔，否則，InnoDB將使用表鎖
在實(shí)際應(yīng)用中趾诗，要特別注意InnoDB行鎖的這一特性蜡感，不然的話，可能導(dǎo)致大量的鎖沖突恃泪，從而影響并發(fā)性能郑兴。下面通過一些實(shí)際例子來加以說明。
（1）在不通過索引條件查詢的時(shí)候贝乎，InnoDB確實(shí)使用的是表鎖情连，而不是行鎖。
（2）由于MySQL的行鎖是針對(duì)索引加的鎖览效，不是針對(duì)記錄加的鎖却舀，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵锤灿，是會(huì)出現(xiàn)鎖沖突的挽拔。
（3）當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行衡招，另外篱昔，不論是使用主鍵索引、唯一索引或普通索引始腾，InnoDB都會(huì)使用行鎖來對(duì)數(shù)據(jù)加鎖州刽。
（4）即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計(jì)劃的代價(jià)來決定的浪箭，如果MySQL認(rèn)為全表掃描效率更高穗椅，比如對(duì)一些很小的表，它就不會(huì)使用索引奶栖，這種情況下InnoDB將使用表鎖匹表，而不是行鎖门坷。因此，在分析鎖沖突時(shí)袍镀，別忘了檢查SQL的執(zhí)行計(jì)劃默蚌，以確認(rèn)是否真正使用了索引。

3.間隙鎖（Next-Key鎖）

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)苇羡，并請(qǐng)求共享或排他鎖時(shí)绸吸，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；
對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄设江，叫做“間隙（GAP)”锦茁，InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）叉存。
例：
假如emp表中只有101條記錄码俩，其empid的值分別是 1,2,...,100,101，下面的SQL：

mysql> select * from emp where empid > 100 for update;

是一個(gè)范圍條件的檢索歼捏，InnoDB不僅會(huì)對(duì)符合條件的empid值為101的記錄加鎖稿存，也會(huì)對(duì)empid大于101（這些記錄并不存在）的“間隙”加鎖。
InnoDB使用間隙鎖的目的：
（1）防止幻讀甫菠，以滿足相關(guān)隔離級(jí)別的要求挠铲。對(duì)于上面的例子冕屯，要是不使用間隙鎖寂诱，如果其他事務(wù)插入了empid大于100的任何記錄，那么本事務(wù)如果再次執(zhí)行上述語句安聘，就會(huì)發(fā)生幻讀痰洒；
（2）為了滿足其恢復(fù)和復(fù)制的需要。
很顯然浴韭，在使用范圍條件檢索并鎖定記錄時(shí)丘喻，即使某些不存在的鍵值也會(huì)被無辜的鎖定，而造成在鎖定的時(shí)候無法插入鎖定鍵值范圍內(nèi)的任何數(shù)據(jù)念颈。在某些場(chǎng)景下這可能會(huì)對(duì)性能造成很大的危害泉粉。
除了間隙鎖給InnoDB帶來性能的負(fù)面影響之外，通過索引實(shí)現(xiàn)鎖定的方式還存在其他幾個(gè)較大的性能隱患：
（1）當(dāng)Query無法利用索引的時(shí)候榴芳，InnoDB會(huì)放棄使用行級(jí)別鎖定而改用表級(jí)別的鎖定嗡靡，造成并發(fā)性能的降低；
（2）當(dāng)Query使用的索引并不包含所有過濾條件的時(shí)候窟感，數(shù)據(jù)檢索使用到的索引鍵所只想的數(shù)據(jù)可能有部分并不屬于該Query的結(jié)果集的行列讨彼，但是也會(huì)被鎖定，因?yàn)殚g隙鎖鎖定的是一個(gè)范圍柿祈，而不是具體的索引鍵哈误；
（3）當(dāng)Query在使用索引定位數(shù)據(jù)的時(shí)候哩至，如果使用的索引鍵一樣但訪問的數(shù)據(jù)行不同的時(shí)候（索引只是過濾條件的一部分），一樣會(huì)被鎖定蜜自。
因此菩貌，在實(shí)際應(yīng)用開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用重荠，我們要盡量?jī)?yōu)化業(yè)務(wù)邏輯菜谣，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件晚缩。
還要特別說明的是尾膊，InnoDB除了通過范圍條件加鎖時(shí)使用間隙鎖外，如果使用相等條件請(qǐng)求給一個(gè)不存在的記錄加鎖荞彼，InnoDB也會(huì)使用間隙鎖冈敛。

4.死鎖

上文講過，MyISAM表鎖是deadlock free的鸣皂，這是因?yàn)镸yISAM總是一次獲得所需的全部鎖抓谴，要么全部滿足，要么等待寞缝，因此不會(huì)出現(xiàn)死鎖癌压。但在InnoDB中，除單個(gè)SQL組成的事務(wù)外荆陆，鎖是逐步獲得的滩届，當(dāng)兩個(gè)事務(wù)都需要獲得對(duì)方持有的排他鎖才能繼續(xù)完成事務(wù)，這種循環(huán)鎖等待就是典型的死鎖被啼。
在InnoDB的事務(wù)管理和鎖定機(jī)制中帜消，有專門檢測(cè)死鎖的機(jī)制，會(huì)在系統(tǒng)中產(chǎn)生死鎖之后的很短時(shí)間內(nèi)就檢測(cè)到該死鎖的存在浓体。當(dāng)InnoDB檢測(cè)到系統(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)生死鎖的場(chǎng)景中涉及到不止InnoDB存儲(chǔ)引擎的時(shí)候冲茸，InnoDB是沒辦法檢測(cè)到該死鎖的屯阀，這時(shí)候就只能通過鎖定超時(shí)限制參數(shù)InnoDB_lock_wait_timeout來解決缅帘。
需要說明的是，這個(gè)參數(shù)并不是只用來解決死鎖問題难衰，在并發(fā)訪問比較高的情況下钦无，如果大量事務(wù)因無法立即獲得所需的鎖而掛起，會(huì)占用大量計(jì)算機(jī)資源盖袭，造成嚴(yán)重性能問題失暂，甚至拖跨數(shù)據(jù)庫(kù)。我們通過設(shè)置合適的鎖等待超時(shí)閾值鳄虱，可以避免這種情況發(fā)生弟塞。
通常來說，死鎖都是應(yīng)用設(shè)計(jì)的問題拙已，通過調(diào)整業(yè)務(wù)流程决记、數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì)、事務(wù)大小倍踪，以及訪問數(shù)據(jù)庫(kù)的SQL語句系宫，絕大部分死鎖都可以避免。下面就通過實(shí)例來介紹幾種避免死鎖的常用方法：
（1）在應(yīng)用中建车，如果不同的程序會(huì)并發(fā)存取多個(gè)表扩借，應(yīng)盡量約定以相同的順序來訪問表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)缤至。
（2）在程序以批量方式處理數(shù)據(jù)的時(shí)候潮罪，如果事先對(duì)數(shù)據(jù)排序，保證每個(gè)線程按固定的順序來處理記錄凄杯，也可以大大降低出現(xiàn)死鎖的可能错洁。
（3）在事務(wù)中，如果要更新記錄戒突，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖，即排他鎖描睦，而不應(yīng)先申請(qǐng)共享鎖膊存，更新時(shí)再申請(qǐng)排他鎖麻掸，因?yàn)楫?dāng)用戶申請(qǐng)排他鎖時(shí)蒲跨，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖进倍，從而造成鎖沖突谓传，甚至死鎖巨双。
（4）在REPEATABLE-READ隔離級(jí)別下副女，如果兩個(gè)線程同時(shí)對(duì)相同條件記錄用SELECT...FOR UPDATE加排他鎖锌仅，在沒有符合該條件記錄情況下守谓，兩個(gè)線程都會(huì)加鎖成功撵彻。程序發(fā)現(xiàn)記錄尚不存在钓株，就試圖插入一條新記錄实牡，如果兩個(gè)線程都這么做，就會(huì)出現(xiàn)死鎖轴合。這種情況下创坞，將隔離級(jí)別改成READ COMMITTED，就可避免問題受葛。
（5）當(dāng)隔離級(jí)別為READ COMMITTED時(shí)题涨，如果兩個(gè)線程都先執(zhí)行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄总滩，如果沒有纲堵，就插入記錄。此時(shí)闰渔，只有一個(gè)線程能插入成功婉支，另一個(gè)線程會(huì)出現(xiàn)鎖等待，當(dāng)?shù)?個(gè)線程提交后澜建，第2個(gè)線程會(huì)因主鍵重出錯(cuò)向挖，但雖然這個(gè)線程出錯(cuò)了，卻會(huì)獲得一個(gè)排他鎖炕舵。這時(shí)如果有第3個(gè)線程又來申請(qǐng)排他鎖何之，也會(huì)出現(xiàn)死鎖。對(duì)于這種情況咽筋，可以直接做插入操作溶推，然后再捕獲主鍵重異常，或者在遇到主鍵重錯(cuò)誤時(shí)奸攻，總是執(zhí)行ROLLBACK釋放獲得的排他鎖蒜危。

5.什么時(shí)候使用表鎖

對(duì)于InnoDB表，在絕大部分情況下都應(yīng)該使用行級(jí)鎖睹耐，因?yàn)槭聞?wù)和行鎖往往是我們之所以選擇InnoDB表的理由辐赞。但在個(gè)別特殊事務(wù)中，也可以考慮使用表級(jí)鎖：
（1）事務(wù)需要更新大部分或全部數(shù)據(jù)硝训，表又比較大响委，如果使用默認(rèn)的行鎖，不僅這個(gè)事務(wù)執(zhí)行效率低窖梁，而且可能造成其他事務(wù)長(zhǎng)時(shí)間鎖等待和鎖沖突赘风，這種情況下可以考慮使用表鎖來提高該事務(wù)的執(zhí)行速度。
（2）事務(wù)涉及多個(gè)表纵刘，比較復(fù)雜邀窃，很可能引起死鎖，造成大量事務(wù)回滾假哎。這種情況也可以考慮一次性鎖定事務(wù)涉及的表瞬捕，從而避免死鎖鞍历、減少數(shù)據(jù)庫(kù)因事務(wù)回滾帶來的開銷。
當(dāng)然山析，應(yīng)用中這兩種事務(wù)不能太多堰燎，否則，就應(yīng)該考慮使用MyISAM表了笋轨。
在InnoDB下秆剪，使用表鎖要注意以下兩點(diǎn)。
（1）使用LOCK TABLES雖然可以給InnoDB加表級(jí)鎖爵政，但必須說明的是仅讽，表鎖不是由InnoDB存儲(chǔ)引擎層管理的，而是由其上一層──MySQL Server負(fù)責(zé)的钾挟，僅當(dāng)autocommit=0洁灵、InnoDB_table_locks=1（默認(rèn)設(shè)置）時(shí)，InnoDB層才能知道MySQL加的表鎖掺出，MySQL Server也才能感知InnoDB加的行鎖徽千，這種情況下，InnoDB才能自動(dòng)識(shí)別涉及表級(jí)鎖的死鎖汤锨，否則双抽，InnoDB將無法自動(dòng)檢測(cè)并處理這種死鎖。
（2）在用 LOCK TABLES對(duì)InnoDB表加鎖時(shí)要注意闲礼，要將AUTOCOMMIT設(shè)為0牍汹，否則MySQL不會(huì)給表加鎖；事務(wù)結(jié)束前柬泽，不要用UNLOCK TABLES釋放表鎖慎菲，因?yàn)閁NLOCK TABLES會(huì)隱含地提交事務(wù)；COMMIT或ROLLBACK并不能釋放用LOCK TABLES加的表級(jí)鎖锨并，必須用UNLOCK TABLES釋放表鎖露该。正確的方式見如下語句：
例如，如果需要寫表t1并從表t讀琳疏，可以按如下做：

SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;

6.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)勢(shì)了新荤。但是揽趾，InnoDB的行級(jí)鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候苛骨，可能會(huì)讓InnoDB的整體性能表現(xiàn)不僅不能比MyISAM高篱瞎，甚至可能會(huì)更差苟呐。
（1）要想合理利用InnoDB的行級(jí)鎖定，做到揚(yáng)長(zhǎng)避短俐筋，我們必須做好以下工作：
a)盡可能讓所有的數(shù)據(jù)檢索都通過索引來完成牵素，從而避免InnoDB因?yàn)闊o法通過索引鍵加鎖而升級(jí)為表級(jí)鎖定；
b)合理設(shè)計(jì)索引澄者，讓InnoDB在索引鍵上面加鎖的時(shí)候盡可能準(zhǔn)確笆呆，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他Query的執(zhí)行粱挡；
c)盡可能減少基于范圍的數(shù)據(jù)檢索過濾條件赠幕，避免因?yàn)殚g隙鎖帶來的負(fù)面影響而鎖定了不該鎖定的記錄；
d)盡量控制事務(wù)的大小询筏，減少鎖定的資源量和鎖定時(shí)間長(zhǎng)度榕堰；
e)在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級(jí)別的事務(wù)隔離嫌套，以減少M(fèi)ySQL因?yàn)閷?shí)現(xiàn)事務(wù)隔離級(jí)別所帶來的附加成本逆屡。
（2）由于InnoDB的行級(jí)鎖定和事務(wù)性，所以肯定會(huì)產(chǎn)生死鎖踱讨，下面是一些比較常用的減少死鎖產(chǎn)生概率的小建議：
a)類似業(yè)務(wù)模塊中魏蔗，盡可能按照相同的訪問順序來訪問，防止產(chǎn)生死鎖勇蝙；
b)在同一個(gè)事務(wù)中沫勿，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率味混；
c)對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分产雹，可以嘗試使用升級(jí)鎖定顆粒度，通過表級(jí)鎖定來減少死鎖產(chǎn)生的概率翁锡。
（3）可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭(zhēng)奪情況：

mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0     |
| InnoDB_row_lock_time          | 0     |
| InnoDB_row_lock_time_avg      | 0     |
| InnoDB_row_lock_time_max      | 0     |
| InnoDB_row_lock_waits         | 0     |
+-------------------------------+-------+

InnoDB 的行級(jí)鎖定狀態(tài)變量不僅記錄了鎖定等待次數(shù)蔓挖，還記錄了鎖定總時(shí)長(zhǎng)，每次平均時(shí)長(zhǎng)馆衔，以及最大時(shí)長(zhǎng)瘟判，此外還有一個(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í)間長(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ì)劃桐筏。
如果發(fā)現(xiàn)鎖爭(zhēng)用比較嚴(yán)重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高拇砰，還可以通過設(shè)置InnoDB Monitors 來進(jìn)一步觀察發(fā)生鎖沖突的表梅忌、數(shù)據(jù)行等，并分析鎖爭(zhēng)用的原因毕匀。
鎖沖突的表铸鹰、數(shù)據(jù)行等，并分析鎖爭(zhēng)用的原因皂岔。具體方法如下：

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)視器后蹋笼，會(huì)有詳細(xì)的當(dāng)前鎖等待的信息，包括表名躁垛、鎖類型剖毯、鎖定記錄的情況等，便于進(jìn)行進(jìn)一步的分析和問題的確定教馆⊙纺保可能會(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ān)視器以后悲敷，默認(rèn)情況下每15秒會(huì)向日志中記錄監(jiān)控的內(nèi)容究恤，如果長(zhǎng)時(shí)間打開會(huì)導(dǎo)致.err文件變得非常的巨大，所以用戶在確認(rèn)問題原因之后后德，要記得刪除監(jiān)控表以關(guān)閉監(jiān)視器部宿，或者通過使用“--console”選項(xiàng)來啟動(dòng)服務(wù)器以關(guān)閉寫日志文件。

END