[toc]

前言

鎖是MySQL在服務(wù)器層和存儲(chǔ)引擎的并發(fā)控制
加鎖是消耗資源的私爷，鎖的各種操作雾棺，包括獲得鎖，檢測(cè)鎖是否已解除衬浑，釋放鎖等捌浩。

鎖機(jī)制

MySQL不同的存儲(chǔ)引擎支持不同的鎖機(jī)制，所有的存儲(chǔ)引擎都以自己的方式顯現(xiàn)了鎖機(jī)制工秩，服務(wù)器層完全不了解存儲(chǔ)引擎中的鎖實(shí)現(xiàn)：

• MyISAM和MEMORY存儲(chǔ)引擎采用的是表級(jí)鎖(table-level locking)
• BDB存儲(chǔ)引擎采用的是頁(yè)面鎖(page-level locking)尸饺，但是也支持表級(jí)鎖
• InnoDB存儲(chǔ)印尼去即支持行級(jí)鎖(row-level locking)进统，也支持表級(jí)鎖，但默認(rèn)情況下是采用行級(jí)鎖浪听。

默認(rèn)情況向下螟碎，表鎖和行鎖都是自動(dòng)獲得的，不需要額外的命令迹栓。

但是在有的情況下掉分，用戶需要明確進(jìn)行鎖表或者進(jìn)行事務(wù)控制，以便確保整個(gè)事務(wù)的完整性克伊，這樣就需要使用事務(wù)控制和鎖定語句來完成酥郭。

不同粒度鎖的比較

• 表級(jí)鎖：開銷小，加鎖塊愿吹，不會(huì)出現(xiàn)死鎖不从，鎖粒度大，發(fā)生鎖沖突的概率最高犁跪，并發(fā)度最低椿息。
  • 這些存儲(chǔ)引擎通過總是一次性同時(shí)獲取所有需要的鎖已經(jīng)總是按相同的順序獲取表鎖來避免死鎖
  • 表鎖更適合以查詢?yōu)橹鳎l(fā)用戶少坷衍，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用
• 行級(jí)鎖：開銷大寝优，加鎖慢，會(huì)出現(xiàn)死鎖惫叛，鎖粒度最小倡勇，發(fā)生鎖沖突的概率最低，并發(fā)程度也是最高
  • 最大程度下支持并發(fā)嘉涌，同時(shí)也帶來了最大的鎖開銷
  • 在InnoDB中妻熊，除單個(gè)SQL組成的事務(wù)外，鎖是逐步獲得的仑最，這就決定了在InnoDB中發(fā)生死鎖的可能扔役。
  • 行級(jí)鎖只在存儲(chǔ)引擎層實(shí)現(xiàn)，而MySQL服務(wù)器層沒有實(shí)現(xiàn)警医，行級(jí)鎖更適合有大量按索引條件并發(fā)更新少量數(shù)據(jù)亿胸，同時(shí)又有并發(fā)查詢的應(yīng)用。
• 頁(yè)面鎖：開銷和加鎖時(shí)間介于表鎖和行鎖只在预皇，會(huì)出現(xiàn)死鎖侈玄，鎖定的力度介于表鎖和行鎖之間，并發(fā)程度一般吟温。

MyISAM表鎖

MyISAM表級(jí)鎖模式：

• 表共享鎖(Table Read Lock)：不會(huì)阻塞其他用戶同一張表的讀請(qǐng)求序仙，但會(huì)阻塞對(duì)同一表寫請(qǐng)求。
• 表獨(dú)占鎖(Table Write Lock)：會(huì)阻塞其他用戶對(duì)同一表的讀寫操作

MyISAM表的讀操作與寫操作之間鲁豪，以及寫操作之間是串行的潘悼，當(dāng)一個(gè)線程獲得對(duì)一個(gè)表的寫鎖后律秃，只有持有鎖的線程可以對(duì)表進(jìn)行更新操作，其他線程的讀治唤，寫操作都會(huì)等待棒动，知道釋放鎖為止。

默認(rèn)情況下宾添，寫鎖比讀鎖具有更高的優(yōu)先級(jí)：當(dāng)一個(gè)鎖釋放時(shí)船惨，這個(gè)鎖會(huì)優(yōu)先給寫鎖隊(duì)列中等候的獲取鎖的請(qǐng)求，然后在給讀鎖隊(duì)列中等待獲取鎖的請(qǐng)求

這也正是MyISAM表不適合有大量更新操作和查詢操作的應(yīng)用缕陕，因?yàn)榇罅康母虏僮鲿?huì)造成查詢操作很難獲取鎖掷漱，從而可能永遠(yuǎn)阻塞，同時(shí)榄檬，一些需要長(zhǎng)時(shí)間運(yùn)行的查詢操作，也會(huì)使寫線程餓死衔统，應(yīng)用中應(yīng)盡量避免出現(xiàn)長(zhǎng)時(shí)間查詢操作(有可能的情況下可以通過使用中間表等措施對(duì)SQL語句做一定的分解鹿榜，使每一步查詢都能在較短時(shí)間完成，從而減少鎖沖突锦爵，如果復(fù)雜查詢不可避免舱殿，應(yīng)盡量安排在數(shù)據(jù)庫(kù)空閑時(shí)間段執(zhí)行，比如一些定期統(tǒng)計(jì)可以安排在夜間執(zhí)行)险掀。

MyISAM加鎖方法

MyISAM在執(zhí)行查詢語句(SELECT)前沪袭，會(huì)自動(dòng)給涉及的表加讀鎖，在執(zhí)行更新操作(UPDATE樟氢、DELETE冈绊、INSERT)前，會(huì)自動(dòng)給涉及的表加寫鎖埠啃，這個(gè)過程并不需要用戶的干預(yù)死宣，因此用戶一般不需要直接使用LOCK TABLE命令給MyISAM表顯示加鎖。

在自動(dòng)加鎖的情況下碴开，MyISAM總是一次獲得SQL語句需要的全部鎖毅该，這也正是MyISAM表不會(huì)出現(xiàn)死鎖的原因。

MyISAM存儲(chǔ)引擎支持并發(fā)插入潦牛，以減少給定表的讀和寫操作之間的爭(zhēng)用：

如果MyISAM表在數(shù)據(jù)文件中間沒有空缺塊眶掌，則行始終插入數(shù)據(jù)文件插入數(shù)據(jù)文件的末尾，在這種情況下巴碗，你 可以自由混合并發(fā)使用MyISAM表的INSERT和SELECT語句而不需要加鎖可以在其他線程進(jìn)行讀操作朴爬，同時(shí)將行插入到MyISAM表中，文件中間有空閑塊可能是從表格中間刪除或更新的行產(chǎn)生的良价，如果文件中間有空缺塊寝殴，則并發(fā)插入會(huì)被禁用蒿叠，但是當(dāng)所有空閑塊都填充有新數(shù)據(jù)時(shí)，他會(huì)自動(dòng)重新啟用蚣常，要控制此行為市咽，可以使用MySQL的concurrent_insert系統(tǒng)變量。

如果使用LOCK TABLES顯示獲取表鎖抵蚊，則可以請(qǐng)求READ LOCAL鎖而不是READ鎖施绎，以便在鎖定表時(shí)，其他會(huì)話可以使用并發(fā)插入贞绳。

• 當(dāng)concurrent_insert設(shè)置為0時(shí)谷醉，不允許并發(fā)插入
• 當(dāng)concurretn_insert設(shè)置為1時(shí)，如果MyISAM表中沒有空洞(即表中間沒有被刪除的行)冈闭，MyISAM運(yùn)行在一個(gè)線程讀表的同時(shí)俱尼，另一個(gè)線程從表尾插入記錄，這也是MySQL的默認(rèn)設(shè)置
• 當(dāng)concurrent_insert設(shè)置為2時(shí)萎攒，無論MyISAM有沒有空洞遇八，都允許在表尾并發(fā)插入記錄。

查詢表級(jí)鎖爭(zhēng)用情況

可以通過檢查table_locks_waited和tabl_locks_immediate狀態(tài)變量來分析系統(tǒng)上的表鎖的爭(zhēng)奪耍休，如果table_locks_waited的值比較高刃永，則說明存在這比較嚴(yán)重的表級(jí)鎖爭(zhēng)用情況。

InnoDB行級(jí)鎖和表級(jí)鎖

InnoDB鎖模式

InnoDB實(shí)現(xiàn)了以下兩種類型的行鎖：

• 共享鎖(S)：允許一個(gè)事務(wù)去讀一行羊精，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排它鎖
• 排它鎖(X)：允許獲得排它鎖的事務(wù)更新數(shù)據(jù)斯够，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排它寫鎖

為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制喧锦，InnoDB還有兩種內(nèi)部使用的意向鎖(Intentionn Locks)读规，這兩種意向鎖都是表鎖：

• 意向共享鎖(IS)：事務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖
• 意向排它鎖(IX)：事務(wù)打算給數(shù)據(jù)行加行排它鎖裸违，事務(wù)在給一個(gè)數(shù)據(jù)行加排它鎖必須先獲取該表的IX鎖掖桦。

鎖模式的兼容情況：

image

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容，InnoDB就將請(qǐng)求的鎖授予該事務(wù)供汛；反之如果兩者不兼容枪汪，該事物就要等待鎖釋放。

InnoDB加鎖方法

• 意向鎖是InnoDB自動(dòng)加的怔昨，不需要用戶干預(yù)
• 對(duì)于UPDATE雀久、DELETE和INSERT語句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排它鎖(X)
• 對(duì)于普通SELETE語句InnoDB不會(huì)加任何鎖趁舀，事務(wù)可以通過以下語句顯示給記錄集添加共享鎖或排它鎖：
  • 共享鎖(S)：select * from table_name where ... LOCK IN SHARE MODE赖捌。其他事務(wù)仍然可以查詢記錄，并也可以對(duì)該記錄加share mode的共享鎖，但是如果當(dāng)前事務(wù)需要對(duì)該記錄進(jìn)行更新操作越庇，則很有可能造成死鎖罩锐。
  • 排它鎖(X)：select * from table_name where ... for update 。其他事務(wù)可以查詢?cè)撚涗浡卑Γ遣荒軐?duì)該記錄加共享鎖和排它鎖涩惑，而是等待獲的鎖。
• 隱式鎖定
  InnoDB在事務(wù)執(zhí)行過程中桑驱，使用兩種段鎖協(xié)議：
  隨時(shí)都可以執(zhí)行鎖定竭恬，InnoDB會(huì)根據(jù)隔離級(jí)別在需要的時(shí)候自動(dòng)加鎖。

鎖只有在執(zhí)行commit或者rollback的時(shí)候才會(huì)釋放熬的，并且所有鎖同一時(shí)刻釋放

• 顯式鎖定：

select ... lock in share mode //共享鎖
select ... for update //排它鎖

select for update：在執(zhí)行這個(gè)select查詢語句的時(shí)候痊硕，會(huì)將對(duì)應(yīng)的索引訪問條目加上排它鎖(X鎖)，也就是說這個(gè)語句對(duì)應(yīng)的鎖就相當(dāng)于update帶來的效果押框。

所使用場(chǎng)景：為了讓自己查到的數(shù)據(jù)確保是最新數(shù)據(jù)岔绸，并且查到后的數(shù)據(jù)只允許自己來修改的時(shí)候，需要用到for update字句橡伞。

select lock in share mode：inshare mode子句的作用就是將查找到的數(shù)據(jù)加上一個(gè)share鎖亭螟，這個(gè)就是表示其他事務(wù)只能對(duì)這些數(shù)據(jù)進(jìn)行簡(jiǎn)單的select操作，并不能進(jìn)行DML操作骑歹。

使用場(chǎng)景：為了確保自己查到的數(shù)據(jù)沒用被其他的事務(wù)正在修改，也就是說確保查到是最新數(shù)據(jù)墨微，并不允許其他人修改數(shù)據(jù)道媚，但是自己不一定能夠修改數(shù)據(jù)，因?yàn)橛锌赡芷渌挛镆矊?duì)這些數(shù)據(jù)使用了in share mode當(dāng)方式上了S鎖翘县。

性能影響：select for update語句最域，相當(dāng)于一個(gè)update語句，在業(yè)務(wù)繁忙的時(shí)候锈麸，如果事務(wù)沒有及時(shí)commit或者rollback可能會(huì)造成其他事務(wù)長(zhǎng)時(shí)間的等待镀脂，從而數(shù)據(jù)庫(kù)的并發(fā)使用效率。select lock in share mode語句時(shí)一個(gè)給查詢數(shù)據(jù)上了一個(gè)共享鎖(S鎖)功能忘伞，它允許其他事務(wù)也對(duì)改數(shù)據(jù)上S鎖薄翅，但是不能夠允許對(duì)改數(shù)據(jù)進(jìn)行修改，如果不及時(shí)commit或者rollback也可能造成大量的事務(wù)等待氓奈。

for update和lock in share mode區(qū)別：

前一個(gè)上的是排它鎖(X鎖)翘魄，一旦一個(gè)事務(wù)獲取了這個(gè)鎖，其他事務(wù)是沒法在這些數(shù)據(jù)上執(zhí)行for update舀奶；后者是共享鎖暑竟，多個(gè)事務(wù)可以同時(shí)對(duì)相同數(shù)據(jù)執(zhí)行l(wèi)ock in share mode。

InnoDB行鎖的實(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將使用表鎖。
• 不論是使用主鍵索引潜慎、唯一索引或者普通索引捡多，InnoDB都會(huì)使用行鎖來對(duì)數(shù)據(jù)加鎖
• 只有執(zhí)行計(jì)劃真正使用了索引，才會(huì)使用行鎖铐炫，即便在條件中使用了索引字段垒手，但是否使用索引來檢索孫書記是由MySQL通過判斷不同的執(zhí)行計(jì)劃的代價(jià)來決定的，如果MySQL認(rèn)為全表掃描效率更高倒信，比如對(duì)一些很小的表科贬，就不會(huì)使用索引，這種情況下InnoDB將使用表鎖鳖悠，而不是行鎖榜掌，因此分析所沖突時(shí)，別忘了檢查SQL的執(zhí)行計(jì)劃(可以通過explain檢查SQL的執(zhí)行計(jì)劃)乘综，以確認(rèn)是否真正使用了索引
• 由于MySQL的行鎖是針對(duì)索引加鎖憎账，不是針對(duì)記錄加鎖，所以雖然多個(gè)session是訪問不同行的記錄卡辰，但是如果是使用相同的索引建胞皱，會(huì)出現(xiàn)鎖沖突(后使用這些索引的session需要等待先使用索引的session釋放鎖后，才能獲取鎖)九妈，應(yīng)用設(shè)計(jì)的時(shí)候要注意這一點(diǎn)反砌。

InnoDB的間隙鎖

當(dāng)我們范圍條件而不是相等條件檢索輸送機(jī)，并請(qǐng)求共享或排它鎖時(shí)萌朱，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖宴树；對(duì)于鍵值在條件范圍內(nèi)但不存在的記錄，叫做間隙(GAP)晶疼，InnoDB也會(huì)對(duì)這個(gè)間隙加鎖酒贬，這種鎖機(jī)制就是所謂的間隙鎖(Next-Key鎖)。

很顯然翠霍，在使用范圍條件檢索并鎖定記錄時(shí)同衣，InnoDB這種鎖機(jī)制會(huì)阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入，這往往會(huì)造成嚴(yán)重的鎖等待壶运，因此耐齐，在實(shí)際開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用，我們要盡量?jī)?yōu)化業(yè)務(wù)邏輯埠况，盡量使用相等條件來訪問更新呢?cái)?shù)據(jù)耸携，避免使用范圍條件。

InnoDB使用間隙鎖的目的：

• 防止幻讀辕翰，以滿足相關(guān)隔離級(jí)別的要求
• 滿足恢復(fù)和復(fù)制的需要

MySQL通過BINLOG錄入執(zhí)行成功的INSERT夺衍、UPDATE、DELETE等更新數(shù)據(jù)的SQL語句喜命，并由次實(shí)現(xiàn)數(shù)據(jù)庫(kù)的恢復(fù)和主從復(fù)制沟沙。MySQL的恢復(fù)機(jī)制(復(fù)制其實(shí)就是在Slave MySQL不斷做基于BINLOG恢復(fù))有以下特點(diǎn)：

• MySQL的恢復(fù)是SQL語句級(jí)的，也就是重新執(zhí)行BINLOG的中的SQL語句
• MySQL的BINLOG是按照事務(wù)提交的先后順序記錄的壁榕，恢復(fù)也是按這個(gè)順序執(zhí)行的矛紫。

由此可見，MySQL的恢復(fù)機(jī)制的要求牌里，在一個(gè)事務(wù)未體檢前颊咬，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(xiàn)幻讀牡辽。

InnoDB在不同隔離級(jí)別下的一致性讀及鎖的差異：

鎖和多版本數(shù)據(jù)(MVCC)是InnoDB實(shí)現(xiàn)一致性讀和ISO/ANSI SQL92隔離級(jí)別的手段喳篇。

因此在不同的隔離級(jí)別下，InnoDB處理SQL時(shí)采用的一致性讀策略和需要的鎖是不同的：

image

image

對(duì)于許多SQL态辛，隔離級(jí)別越高麸澜，InnoDB給記錄集加的鎖就越嚴(yán)格(尤其是使用范圍條件的時(shí)候)，產(chǎn)生鎖沖突的可能性也就越高奏黑，從而對(duì)并發(fā)性事務(wù)處理性能的影響也就越大痰憎。

因此，我們?cè)趹?yīng)用中攀涵，應(yīng)該盡量使用較低的隔離級(jí)別，以減少鎖競(jìng)爭(zhēng)的幾率洽沟。

LOCK TABLES 和UNLOCK TABLES

MySQL也支持lock tables和un lock tables以故，這都是在服務(wù)器層(MySQL Service層)實(shí)現(xiàn)的，和存儲(chǔ)引擎無關(guān)灌灾，他們有自己的用途吹截，并不能替代事務(wù)的處理弛秋。

• LOCK TABLES可以鎖定用于當(dāng)前線程的表，如果表被其他線程鎖定昆烁，則當(dāng)前線程會(huì)等待，直到可以獲取所有鎖定為止缎岗。
• UNLOCK TABLES可以釋放當(dāng)前線程獲得的任何鎖定静尼，當(dāng)前線程執(zhí)行另一個(gè)LOCK TABLES時(shí)，或當(dāng)與服務(wù)器的鏈接被關(guān)閉時(shí)，所有由當(dāng)前線程鎖定的表被隱含的解鎖鼠渺。

LOCK TABLES語法：

• 在用LOCK TABLES對(duì)InnoDB表加鎖時(shí)要注意鸭巴，要將AUTOCOMMIT設(shè)為0，否則MYSQL不會(huì)給表加鎖
• 事務(wù)結(jié)束前拦盹，不要用UNLOCK TABLES釋放表鎖鹃祖，因?yàn)閁NLOCK TABLES隱含的提交事務(wù)。
• COMMIT或ROLLBACK并不能釋放用LOCK TABLES加的表級(jí)鎖普舆，必須用UNLOCK TABLES釋放表級(jí)鎖恬口。

正確的方式見如下語句：
例如，如果需要寫表t1并從表t讀沼侣，可以按照如下去做：

set autocommit=0;
lock tables t1 where ,t2 read,...;
[do something with tables t1 and t2 here]; 
commit;
unlock tables;

使用LOCK TABLES的場(chǎng)景

給表顯示加表級(jí)鎖(InnoDB表和MyISAM都可以)祖能，一般為了在一定程度模擬事務(wù)操作，實(shí)現(xiàn)對(duì)某一時(shí)間點(diǎn)多個(gè)表的一致性讀取华临。(與MyISAM默認(rèn)的表鎖行為類似)
在用LOCK TABLES給表顯示加表鎖時(shí)芯杀，必須同時(shí)獲取所有涉及到表的鎖，并且MySQL不支持鎖升級(jí)雅潭，也就是說揭厚，在執(zhí)行LOCK TABLES后，只能訪問顯示加鎖的這些表扶供，不能訪問未加鎖的表筛圆；童年故事如果加的是讀鎖，那么只能執(zhí)行查詢操作椿浓，而不能執(zhí)行更新操作太援。

其實(shí)，在MyISAM自動(dòng)加鎖(表鎖)的情況下也大致如此扳碍，MyISAM總是一次獲得SQLy語句所需要的全部鎖提岔，這正是MyISAM表不會(huì)出現(xiàn)死鎖的原因。

死鎖

• 死鎖產(chǎn)生：
  • 死鎖是指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用笋敞，并請(qǐng)求鎖定對(duì)方占用的資源碱蒙，從而倒追惡性循環(huán)
  • 當(dāng)事務(wù)試圖以不同順序鎖定資源時(shí)，就可能產(chǎn)生死鎖夯巷，多個(gè)事務(wù)同時(shí)鎖定同一個(gè)資源時(shí)也可能產(chǎn)生死鎖
  • 鎖的行為和順序和存儲(chǔ)引擎相關(guān)赛惩，以同樣的順序執(zhí)行語句，有些存儲(chǔ)引擎會(huì)產(chǎn)生死鎖有些不會(huì)趁餐，死鎖的雙重原因：真正的數(shù)據(jù)沖突喷兼，存儲(chǔ)引擎的實(shí)現(xiàn)的方式。
• 檢測(cè)死鎖：數(shù)據(jù)庫(kù)系統(tǒng)實(shí)現(xiàn)了各種死鎖的檢測(cè)和死鎖的超時(shí)機(jī)制后雷，InnoDB存儲(chǔ)引擎檢測(cè)到死鎖的循環(huán)依賴并立即返回一個(gè)錯(cuò)誤
• 死鎖恢復(fù)：死鎖發(fā)生以后季惯，只有部分或完全回滾其中一個(gè)事務(wù)吠各，才能打破死鎖，InnoDB目標(biāo)處理死鎖的方法是星瘾，將持有最少行級(jí)排它鎖的事務(wù)進(jìn)行回滾走孽，所以事務(wù)型應(yīng)用程序在設(shè)計(jì)時(shí)必須考慮如果處理死鎖，多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可琳状。
• 外部鎖的死鎖檢測(cè)：發(fā)生死鎖后磕瓷，InnoDB一般都會(huì)自動(dòng)檢測(cè)到，并使一個(gè)事務(wù)釋放鎖并回退念逞，另一個(gè)事務(wù)獲得鎖困食，繼續(xù)完成事務(wù)，但涉及外部鎖翎承，或涉及表鎖的情況下硕盹，InnoDB并不能完全自動(dòng)檢測(cè)到死鎖，這需要通過設(shè)置鎖等待超時(shí)參數(shù)innodb_lock_wait_timeout來解決
• 死鎖影響性能：死鎖會(huì)影響性能而不是會(huì)產(chǎn)生嚴(yán)重的錯(cuò)誤叨咖，因?yàn)镮nnoDB會(huì)自動(dòng)檢測(cè)死鎖狀況并回滾其中一個(gè)受影響的事務(wù)瘩例，在高并發(fā)系統(tǒng)上，當(dāng)許多線程等待同一個(gè)鎖時(shí)甸各，死鎖檢測(cè)可能導(dǎo)致速度變慢垛贤，有時(shí)當(dāng)發(fā)生死鎖時(shí)，禁用死鎖檢測(cè)(使用innodb_deadlock_detect配置選項(xiàng))可能會(huì)更有效趣倾，這是可以依賴innodb_lock_wait_timeout設(shè)置進(jìn)行事務(wù)回滾聘惦。

MyISAM避免死鎖：

在自動(dòng)加鎖的情況下，MyISAM總是一次獲得SQL語句所需要的全部鎖儒恋，所以MyISAM表不會(huì)出現(xiàn)死鎖善绎。

InnoDB避免死鎖

• 為了在當(dāng)個(gè)InnoDB表上執(zhí)行多個(gè)并發(fā)寫入操作時(shí)避免死鎖，可以在是事務(wù)開始時(shí)通過為預(yù)期要修改的每個(gè)行使用select for update語句來獲取必要的鎖诫尽，即使這些行的更改語句是在之后才執(zhí)行的禀酱。
• 在事務(wù)中，如果要更新記錄牧嫉，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖剂跟，即排它鎖，而不應(yīng)該先申請(qǐng)共享鎖驹止，更新是在在申請(qǐng)排它鎖，因?yàn)檫@時(shí)候用戶在申請(qǐng)排它鎖時(shí)观蜗，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖臊恋，從而造成鎖沖突，甚至死鎖
• 如果事務(wù)需要修改或鎖定多個(gè)表墓捻，則應(yīng)在每個(gè)事務(wù)中以相同的順序使用加鎖語句抖仅，在應(yīng)用如果不同的程序會(huì)并發(fā)存取多個(gè)表坊夫，應(yīng)盡量預(yù)定以相同的順序來訪問表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)
• 通過select lock in share mode獲取行的讀鎖后撤卢，如果當(dāng)前事務(wù)需要對(duì)該記錄進(jìn)行更新操作环凿，則很有可能造成死鎖
• 改變事務(wù)的隔離級(jí)別

如果出現(xiàn)死鎖，可以用show innodb status命令來確定最后一個(gè)死鎖產(chǎn)生的原因放吩，返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細(xì)信息智听，如果引發(fā)死鎖的SQL語句，事務(wù)已經(jīng)獲得的鎖渡紫，正在等待什么鎖到推，以及被回滾的事務(wù)等，據(jù)此可以分析死鎖產(chǎn)生的原因和改進(jìn)措施惕澎。

一些優(yōu)化鎖性能的建議

• 盡量使用較低的隔離級(jí)別
• 精心設(shè)計(jì)索引莉测，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確唧喉，從而減少鎖沖突的機(jī)會(huì)
• 選擇合理的事務(wù)大小捣卤，小事務(wù)發(fā)生所沖突的幾率更小
• 給記錄集顯示加鎖時(shí)，最后一次性請(qǐng)求足夠的鎖級(jí)別八孝，比如要修改數(shù)據(jù)董朝，最后直接申請(qǐng)排它鎖，額而不是先申請(qǐng)共享鎖唆阿，修改時(shí)候在申請(qǐng)排它鎖
• 不同程序訪問一組表時(shí)益涧，應(yīng)盡量約定以相同的順序訪問各表，對(duì)一個(gè)表而言驯鳖，盡可能以固定的順序存取表中的行闲询，這樣可以大大減少死鎖的機(jī)會(huì)
• 盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對(duì)并發(fā)插入的影響浅辙。
• 不要申請(qǐng)超過時(shí)間需要的鎖級(jí)別
• 除非必須扭弧，查詢時(shí)不要顯示加鎖，MySQL的MVCC可以實(shí)現(xiàn)事務(wù)中查詢不用加速搜记舆，優(yōu)化事務(wù)性能鸽捻，MVCC只在commited read(讀提交)，和repeatable read(可重復(fù)度)兩種隔離級(jí)別下工作
• 對(duì)于一些特定的事務(wù)泽腮，可以使用表鎖來提高處理速度或減少死鎖的可能御蒲。

樂觀鎖和悲觀鎖

樂觀鎖

樂觀蘇：假設(shè)不會(huì)發(fā)生并發(fā)沖突，只在提交操作時(shí)檢查是否違反數(shù)據(jù)完整性诊赊，樂觀鎖不能解決臟讀問題厚满。
樂觀鎖，顧名思義碧磅，就是很樂觀碘箍，每次去拿數(shù)據(jù)的時(shí)候都會(huì)認(rèn)為別人不好修改數(shù)據(jù)遵馆，所以不會(huì)上鎖，但是在更新的時(shí)候會(huì)判斷一下在此期間別人有沒有去更新這個(gè)數(shù)據(jù)丰榴，可以使用版本號(hào)的機(jī)制货邓，樂觀鎖適用于讀多的應(yīng)用，這樣可以提供吞吐量四濒，像數(shù)據(jù)庫(kù)如果提供類似于write_condition機(jī)制的其實(shí)都是提供樂觀鎖换况。

悲觀鎖

假定會(huì)發(fā)生并發(fā)沖突，屏蔽一切可能違反數(shù)據(jù)完整性的操作峻黍。

悲觀鎖复隆，就是很悲觀，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人會(huì)修改姆涩，所以每次在拿數(shù)據(jù)的時(shí)候都會(huì)加上鎖挽拂，這樣別人想拿這個(gè)數(shù)據(jù)就會(huì)block直到拿到鎖，傳統(tǒng)的關(guān)系數(shù)據(jù)庫(kù)里面就用到了這種鎖機(jī)制骨饿，比如行鎖亏栈，表鎖，讀鎖宏赘，寫鎖等绒北，都是在操作之前先上鎖。