MYSQL 鎖機(jī)制
數(shù)據(jù)庫在對(duì)資源進(jìn)行高并發(fā)的讀寫操作時(shí)，為了保證數(shù)據(jù)的一致性击你，有效性玉组，鎖是很重要的機(jī)制谎柄。Mysql的鎖分為三個(gè)級(jí)別：行級(jí)鎖，頁級(jí)鎖惯雳，表級(jí)鎖朝巫。對(duì)于平時(shí)常用的存儲(chǔ)引擎，MyISAM采用的是表級(jí)鎖石景，InnoDB采用的是行級(jí)鎖加表級(jí)鎖劈猿，而支持頁級(jí)鎖的BDB引擎已經(jīng)逐漸被InnoDB替代了，這里暫不討論潮孽。
表級(jí)鎖的開銷小糙臼，加鎖快，不會(huì)出現(xiàn)死鎖恩商，鎖定粒度大变逃，大概率發(fā)生鎖的沖突，并發(fā)度低
行級(jí)鎖的開銷大怠堪，加鎖滿揽乱，會(huì)出現(xiàn)死鎖，鎖定粒度小粟矿，小概率發(fā)生鎖的重讀凰棉，并發(fā)度高
上述特點(diǎn)來看，很難說哪種鎖更好陌粹，只能相對(duì)于所處的業(yè)務(wù)場景來選擇更加適合的鎖機(jī)制撒犀。如果僅從鎖的角度來看，表級(jí)鎖更適合以查詢?yōu)橹鞯膽?yīng)用場景掏秩，而行級(jí)鎖則更適合于大量按索引條件并發(fā)更新少量數(shù)據(jù)的應(yīng)用場景或舞。
MyISAM表
MyISAM存儲(chǔ)引擎只支持表級(jí)鎖，鎖的模式有共享鎖和排他鎖蒙幻。共享鎖是他人可以讀但不能寫映凳，排它鎖則會(huì)阻塞他人的讀寫操作。MyISAM的讀寫之間邮破，以及寫寫之間是串行的诈豌。
MyISAM在執(zhí)行SQL語句時(shí)，會(huì)自動(dòng)為SELECT語句加上共享鎖抒和，為UDI操作加上排它鎖矫渔。MYSQL不支持鎖升級(jí)，如果涉及到更新操作摧莽，需要在一開始就加上排它鎖庙洼。
MyISAM的并發(fā)插入
在存儲(chǔ)引擎中有一個(gè)系統(tǒng)變量concurrent_insert，專門控制其并發(fā)插入的行為
concurrent_insert=0時(shí)，不允許并發(fā)插入
concurrent_insert=1時(shí)送膳，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行）员魏，其允許在一個(gè)進(jìn)程讀表的同事，另一個(gè)進(jìn)程從表尾插入記錄叠聋，這也是MySQL的默認(rèn)設(shè)置
concurrent_insert=2時(shí)撕阎，如果MyISAM表中沒有空洞，允許在表尾并發(fā)插入記錄
MyISAM的鎖調(diào)度
在MyISAM存儲(chǔ)引擎中碌补，寫的重要性要大于讀虏束，所以在操作隊(duì)列中，即使寫的操作在讀的操作之后厦章，也會(huì)讓寫先拿到排它鎖镇匀，這也正是MyISAM不適合于大量寫入操作的應(yīng)用場景的原因，這樣可能會(huì)導(dǎo)致讀操作永遠(yuǎn)在阻塞中袜啃，永遠(yuǎn)在等待寫操作的釋放鎖汗侵。當(dāng)然，除了默認(rèn)的設(shè)置群发，可以通過設(shè)置語句的優(yōu)先級(jí)別來管理這個(gè)執(zhí)行順序
InnoDB
他與MyISAM的最大區(qū)別有兩個(gè)方面晰韵，一個(gè)是支持事務(wù)，另一個(gè)是采用了行級(jí)鎖
事務(wù)的并發(fā)處理會(huì)帶來幾個(gè)問題
1.不可重復(fù)讀熟妓，A事務(wù)在第一次讀和第二次讀之間雪猪，如果B對(duì)數(shù)據(jù)進(jìn)行的修改，則兩次讀取的數(shù)據(jù)會(huì)不一致
2.更新丟失起愈，A和B同時(shí)操作一個(gè)數(shù)據(jù)只恨，最后執(zhí)行完畢的會(huì)覆蓋前一個(gè)的執(zhí)行結(jié)果
3.臟讀，A事務(wù)添加了數(shù)據(jù)但并未提交抬虽，B讀到了這條數(shù)據(jù)后A回滾了官觅，就會(huì)導(dǎo)致臟讀（很形象）
4.幻讀，A事務(wù)第二次讀取數(shù)據(jù)之前斥赋，B數(shù)據(jù)提交了滿足條件的數(shù)據(jù)缰猴，這種現(xiàn)象就叫幻讀
為了解決以上問題，產(chǎn)生了四個(gè)隔離級(jí)別：未提交讀疤剑，提交讀，可重復(fù)讀（InnoDB事務(wù)默認(rèn)使用）闷堡，串行讀
鎖模式
共享鎖（S）：允許一個(gè)事務(wù)去讀一行隘膘，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
排他鎖（X)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)杠览，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖弯菊。
另外，為了允許行鎖和表鎖共存踱阿，實(shí)現(xiàn)多粒度鎖機(jī)制管钳，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks）钦铁，這兩種意向鎖都是表鎖。
意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖才漆，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖牛曹。
意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖醇滥。
語句示例：
共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE黎比。
排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。
行鎖的實(shí)現(xiàn)方式：
InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的鸳玩，這一點(diǎn)MySQL與Oracle不同阅虫，后者是通過在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過索引條件檢索數(shù)據(jù)不跟，InnoDB才使用行級(jí)鎖颓帝，否則，InnoDB將使用表鎖窝革！在實(shí)際應(yīng)用中躲履，要特別注意InnoDB行鎖的這一特性，不然的話聊闯，可能導(dǎo)致大量的鎖沖突工猜，從而影響并發(fā)性能。
由于MySQL的行鎖是針對(duì)索引加的鎖菱蔬，不是針對(duì)記錄加的鎖篷帅，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵拴泌，是會(huì)出現(xiàn)鎖沖突的
當(dāng)表有多個(gè)索引的時(shí)候魏身，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外蚪腐，不論是使用主鍵索引箭昵、唯一索引或普通索引，InnoDB都會(huì)使用行鎖來對(duì)數(shù)據(jù)加鎖回季。如果不同的索引碰巧都落到了同一個(gè)行上家制，那么同樣會(huì)阻塞。
即便在條件中使用了索引字段泡一，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計(jì)劃的代價(jià)來決定的颤殴，如果MySQL認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表鼻忠，它就不會(huì)使用索引涵但，這種情況下InnoDB將使用表鎖，而不是行鎖。因此矮瘟，在分析鎖沖突時(shí)瞳脓，別忘了檢查SQL的執(zhí)行計(jì)劃，以確認(rèn)是否真正使用了索引澈侠。
間隙鎖
當(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：
Select * from emp where empid > 100 for update;
是一個(gè)范圍條件的檢索，InnoDB不僅會(huì)對(duì)符合條件的empid值為101的記錄加鎖呼猪，也會(huì)對(duì)empid大于101（這些記錄并不存在）的“間隙”加鎖画畅。
InnoDB使用間隙鎖的目的，一方面是為了防止幻讀宋距，以滿足相關(guān)隔離級(jí)別的要求轴踱，對(duì)于上面的例子，要是不使用間隙鎖谚赎，如果其他事務(wù)插入了empid大于100的任何記錄淫僻，那么本事務(wù)如果再次執(zhí)行上述語句，就會(huì)發(fā)生幻讀壶唤；另外一方面雳灵，是為了滿足其恢復(fù)和復(fù)制的需要
還要特別說明的是，InnoDB除了通過范圍條件加鎖時(shí)使用間隙鎖外闸盔，如果使用相等條件請(qǐng)求給一個(gè)不存在的記錄加鎖悯辙，InnoDB也會(huì)使用間隙鎖！
MySQL的恢復(fù)機(jī)制是通過BINLOG記錄來執(zhí)行IUD操作來同步Slave的迎吵，這就要求：在一個(gè)事務(wù)未提交前躲撰，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(xiàn)幻讀钓觉，這已經(jīng)超過了ISO/ANSI SQL92“可重復(fù)讀”隔離級(jí)別的要求茴肥，實(shí)際上是要求事務(wù)要串行化。這也是許多情況下荡灾，InnoDB要用到間隙鎖的原因，比如在用范圍條件更新記錄時(shí)，無論在Read Commited或是Repeatable Read隔離級(jí)別下批幌，InnoDB都要使用間隙鎖础锐，但這并不是隔離級(jí)別要求的。
INSERT...SELECT...和 CREATE TABLE...SELECT...語句荧缘，可能會(huì)阻止對(duì)源表的并發(fā)更新皆警，造成對(duì)源表鎖的等待。如果查詢比較復(fù)雜的話截粗，會(huì)造成嚴(yán)重的性能問題信姓，我們?cè)趹?yīng)用中應(yīng)盡量避免使用。實(shí)際上绸罗，MySQL將這種SQL叫作不確定（non-deterministic）的SQL意推，不推薦使用。
什么時(shí)候使用表鎖
對(duì)于InnoDB表珊蟀，在絕大部分情況下都應(yīng)該使用行級(jí)鎖菊值，因?yàn)槭聞?wù)和行鎖往往是我們之所以選擇InnoDB表的理由。但在個(gè)別特殊事務(wù)中育灸，也可以考慮使用表級(jí)鎖腻窒。
第一種情況是：事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大磅崭，如果使用默認(rèn)的行鎖儿子，不僅這個(gè)事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長時(shí)間鎖等待和鎖沖突砸喻，這種情況下可以考慮使用表鎖來提高該事務(wù)的執(zhí)行速度柔逼。
第二種情況是：事務(wù)涉及多個(gè)表，比較復(fù)雜恩够，很可能引起死鎖卒落，造成大量事務(wù)回滾。這種情況也可以考慮一次性鎖定事務(wù)涉及的表蜂桶，從而避免死鎖儡毕、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。
如果以上兩種事務(wù)過多扑媚，那我們就可以考慮使用MyISAM引擎了
死鎖
發(fā)生死鎖后腰湾，InnoDB一般都能自動(dòng)檢測到，并使一個(gè)事務(wù)釋放鎖并回退疆股，另一個(gè)事務(wù)獲得鎖费坊，繼續(xù)完成事務(wù)。但在涉及外部鎖旬痹，或涉及表鎖的情況下附井，InnoDB并不能完全自動(dòng)檢測到死鎖讨越，這需要通過設(shè)置鎖等待超時(shí)參數(shù) innodb_lock_wait_timeout來解決。需要說明的是永毅，這個(gè)參數(shù)并不是只用來解決死鎖問題把跨，在并發(fā)訪問比較高的情況下，如果大量事務(wù)因無法立即獲得所需的鎖而掛起，會(huì)占用大量計(jì)算機(jī)資源，造成嚴(yán)重性能問題凉蜂，甚至拖跨數(shù)據(jù)庫存筏。我們通過設(shè)置合適的鎖等待超時(shí)閾值，可以避免這種情況發(fā)生。
在了解InnoDB鎖特性后，用戶可以通過設(shè)計(jì)和SQL調(diào)整等措施減少鎖沖突和死鎖，包括：
- 盡量使用較低的隔離級(jí)別秀姐；
- 精心設(shè)計(jì)索引，并盡量使用索引訪問數(shù)據(jù)魁蒜，使加鎖更精確囊扳，從而減少鎖沖突的機(jī)會(huì)；
- 選擇合理的事務(wù)大小兜看，小事務(wù)發(fā)生鎖沖突的幾率也更凶断獭；
- 給記錄集顯示加鎖時(shí)细移，最好一次性請(qǐng)求足夠級(jí)別的鎖搏予。比如要修改數(shù)據(jù)的話，最好直接申請(qǐng)排他鎖弧轧，而不是先申請(qǐng)共享鎖雪侥，修改時(shí)再請(qǐng)求排他鎖，這樣容易產(chǎn)生死鎖精绎；
- 不同的程序訪問一組表時(shí)速缨，應(yīng)盡量約定以相同的順序訪問各表，對(duì)一個(gè)表而言代乃，盡可能以固定的順序存取表中的行旬牲。這樣可以大大減少死鎖的機(jī)會(huì)；
- 盡量用相等條件訪問數(shù)據(jù)搁吓，這樣可以避免間隙鎖對(duì)并發(fā)插入的影響原茅；
- 不要申請(qǐng)超過實(shí)際需要的鎖級(jí)別；除非必須堕仔，查詢時(shí)不要顯示加鎖擂橘；
- 對(duì)于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能摩骨。
