MySQL鎖總結(jié)
鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制刀疙。鎖保證數(shù)據(jù)并發(fā)訪問的一致性、有效性扫倡；鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素谦秧。鎖是Mysql在服務(wù)器層和存儲(chǔ)引擎層的的并發(fā)控制。
加鎖是消耗資源的撵溃，鎖的各種操作疚鲤，包括獲得鎖、檢測(cè)鎖是否是否已解除缘挑、釋放鎖等集歇。
鎖機(jī)制
共享鎖與排他鎖
- 共享鎖（讀鎖）：其他事務(wù)可以讀，但不能寫语淘。
- 排他鎖（寫鎖） ：其他事務(wù)不能讀取诲宇，也不能寫。
粒度鎖
MySQL 不同的存儲(chǔ)引擎支持不同的鎖機(jī)制惶翻，所有的存儲(chǔ)引擎都以自己的方式顯現(xiàn)了鎖機(jī)制姑蓝，服務(wù)器層完全不了解存儲(chǔ)引擎中的鎖實(shí)現(xiàn)：
- MyISAM 和 MEMORY 存儲(chǔ)引擎采用的是表級(jí)鎖（table-level locking）
- BDB 存儲(chǔ)引擎采用的是頁面鎖（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í)鎖更適合于以查詢?yōu)橹鳎l(fā)用戶少卧惜，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用厘灼，如Web 應(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)用，如一些在線事務(wù)處理（OLTP）系統(tǒng)
頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間任岸；會(huì)出現(xiàn)死鎖再榄；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般享潜。
MyISAM 表鎖
MyISAM表級(jí)鎖模式：
- 表共享讀鎖 （Table Read Lock）：不會(huì)阻塞其他用戶對(duì)同一表的讀請(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)求。 （This ensures that updates to a table are not “starved” even when there is heavy SELECT activity for the table. However, if there are many updates for a table, SELECT statements wait until there are no more updates.）胯盯。
這也正是 MyISAM 表不太適合于有大量更新操作和查詢操作應(yīng)用的原因懈费，因?yàn)椋罅康母虏僮鲿?huì)造成查詢操作很難獲得讀鎖博脑，從而可能永遠(yuǎn)阻塞憎乙。同時(shí)票罐，一些需要長(zhǎng)時(shí)間運(yùn)行的查詢操作，也會(huì)使寫線程“餓死” 寨闹，應(yīng)用中應(yīng)盡量避免出現(xiàn)長(zhǎng)時(shí)間運(yùn)行的查詢操作（在可能的情況下可以通過使用中間表等措施對(duì)SQL語句做一定的“分解” 胶坠，使每一步查詢都能在較短時(shí)間完成，從而減少鎖沖突繁堡。如果復(fù)雜查詢不可避免沈善，應(yīng)盡量安排在數(shù)據(jù)庫空閑時(shí)段執(zhí)行，比如一些定期統(tǒng)計(jì)可以安排在夜間執(zhí)行）椭蹄。
可以設(shè)置改變讀鎖和寫鎖的優(yōu)先級(jí)：
- 通過指定啟動(dòng)參數(shù)low-priority-updates闻牡，使MyISAM引擎默認(rèn)給予讀請(qǐng)求以優(yōu)先的權(quán)利。
- 通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1绳矩，使該連接發(fā)出的更新請(qǐng)求優(yōu)先級(jí)降低罩润。
- 通過指定INSERT、UPDATE翼馆、DELETE語句的LOW_PRIORITY屬性割以，降低該語句的優(yōu)先級(jí)。
- 給系統(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ì)中姜。
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)死鎖（Deadlock Free）的原因层亿。
MyISAM存儲(chǔ)引擎支持并發(fā)插入桦卒，以減少給定表的讀和寫操作之間的爭(zhēng)用：
如果MyISAM表在數(shù)據(jù)文件中間沒有空閑塊，則行始終插入數(shù)據(jù)文件的末尾匿又。 在這種情況下方灾，你可以自由混合并發(fā)使用MyISAM表的INSERT和SELECT語句而不需要加鎖——你可以在其他線程進(jìn)行讀操作的時(shí)候，同時(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)concurrent_insert設(shè)置為1時(shí)矿辽，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個(gè)線程讀表的同時(shí)郭厌，另一個(gè)線程從表尾插入記錄袋倔。這也是MySQL的默認(rèn)設(shè)置。
- 當(dāng)concurrent_insert設(shè)置為2時(shí)折柠，無論MyISAM表中有沒有空洞奕污，都允許在表尾并發(fā)插入記錄。
查詢表級(jí)鎖爭(zhēng)用情況：
可以通過檢查 table_locks_waited 和 table_locks_immediate 狀態(tài)變量來分析系統(tǒng)上的表鎖的爭(zhēng)奪液走，如果 Table_locks_waited 的值比較高，則說明存在著較嚴(yán)重的表級(jí)鎖爭(zhēng)用情況：
mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name | Value |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited | 15324 |
+-----------------------+---------+
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)部使用的意向鎖（Intention Locks）慌洪，這兩種意向鎖都是表鎖：
- 意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行加行共享鎖顶燕，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的 IS 鎖。
- 意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加行排他鎖冈爹，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的 IX 鎖涌攻。
鎖模式的兼容情況：
（如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容， InnoDB 就將請(qǐng)求的鎖授予該事務(wù)频伤； 反之恳谎， 如果兩者不兼容，該事務(wù)就要等待鎖釋放。）
InnoDB加鎖方法：
意向鎖是 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廓译。 其他 session 仍然可以查詢記錄，并也可以對(duì)該記錄加 share mode 的共享鎖赞咙。但是如果當(dāng)前事務(wù)需要對(duì)該記錄進(jìn)行更新操作责循，則很有可能造成死鎖。
排他鎖（X)：SELECT * FROM table_name WHERE ... FOR UPDATE攀操。其他 session 可以查詢?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)的索引訪問條目進(jìn)行上排他鎖（X 鎖），也就是說這個(gè)語句對(duì)應(yīng)的鎖就相當(dāng)于update帶來的效果故俐。
select *** for update 的使用場(chǎng)景：為了讓自己查到的數(shù)據(jù)確保是最新數(shù)據(jù)胁澳，并且查到后的數(shù)據(jù)只允許自己來修改的時(shí)候，需要用到 for update 子句欲低。
select lock in share mode ：in share mode 子句的作用就是將查找到的數(shù)據(jù)加上一個(gè) share 鎖辕宏，這個(gè)就是表示其他的事務(wù)只能對(duì)這些數(shù)據(jù)進(jìn)行簡(jiǎn)單的select 操作，并不能夠進(jìn)行 DML 操作砾莱。select *** lock in share mode 使用場(chǎng)景：為了確保自己查到的數(shù)據(jù)沒有被其他的事務(wù)正在修改瑞筐，也就是說確保查到的數(shù)據(jù)是最新的數(shù)據(jù)，并且不允許其他人來修改數(shù)據(jù)腊瑟。但是自己不一定能夠修改數(shù)據(jù)聚假，因?yàn)橛锌赡芷渌氖聞?wù)也對(duì)這些數(shù)據(jù) 使用了 in share mode 的方式上了 S 鎖。
性能影響：
select for update 語句闰非，相當(dāng)于一個(gè) update 語句膘格。在業(yè)務(wù)繁忙的情況下，如果事務(wù)沒有及時(shí)的commit或者rollback 可能會(huì)造成其他事務(wù)長(zhǎng)時(shí)間的等待财松，從而影響數(shù)據(jù)庫的并發(fā)使用效率闯袒。
select lock in share mode 語句是一個(gè)給查找的數(shù)據(jù)上一個(gè)共享鎖（S 鎖）的功能，它允許其他的事務(wù)也對(duì)該數(shù)據(jù)上S鎖，但是不能夠允許對(duì)該數(shù)據(jù)進(jìn)行修改政敢。如果不及時(shí)的commit 或者rollback 也可能會(huì)造成大量的事務(wù)等待其徙。
for update 和 lock in share mode 的區(qū)別：
前一個(gè)上的是排他鎖（X 鎖），一旦一個(gè)事務(wù)獲取了這個(gè)鎖喷户，其他的事務(wù)是沒法在這些數(shù)據(jù)上執(zhí)行 for update 唾那；后一個(gè)是共享鎖，多個(gè)事務(wù)可以同時(shí)的對(duì)相同數(shù)據(jù)執(zhí)行 lock 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ì)劃真正使用了索引，才能使用行鎖：即便在條件中使用了索引字段底扳，但是否使用索引來檢索數(shù)據(jù)是由 MySQL 通過判斷不同執(zhí)行計(jì)劃的代價(jià)來決定的铸抑，如果 MySQL 認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表衷模，它就不會(huì)使用索引鹊汛，這種情況下 InnoDB 將使用表鎖，而不是行鎖阱冶。因此柒昏，在分析鎖沖突時(shí)，
別忘了檢查 SQL 的執(zhí)行計(jì)劃（可以通過 explain 檢查 SQL 的執(zhí)行計(jì)劃）熙揍，以確認(rèn)是否真正使用了索引。（更多閱讀：MySQL索引總結(jié)） - 由于 MySQL 的行鎖是針對(duì)索引加的鎖氏涩，不是針對(duì)記錄加的鎖届囚，所以雖然多個(gè)session是訪問不同行的記錄， 但是如果是使用相同的索引鍵是尖， 是會(huì)出現(xiàn)鎖沖突的（后使用這些索引的session需要等待先使用索引的session釋放鎖后意系，才能獲取鎖）。 應(yīng)用設(shè)計(jì)的時(shí)候要注意這一點(diǎn)饺汹。
InnoDB的間隙鎖：
當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(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í)際應(yīng)用開發(fā)中氢妈，尤其是并發(fā)插入比較多的應(yīng)用粹污，我們要盡量?jī)?yōu)化業(yè)務(wù)邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)首量，避免使用范圍條件壮吩。
InnoDB使用間隙鎖的目的：
- 防止幻讀，以滿足相關(guān)隔離級(jí)別的要求蕾总；
- 滿足恢復(fù)和復(fù)制的需要：
MySQL 通過 BINLOG 錄入執(zhí)行成功的 INSERT粥航、UPDATE、DELETE 等更新數(shù)據(jù)的 SQL 語句生百，并由此實(shí)現(xiàn) MySQL 數(shù)據(jù)庫的恢復(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è)順序進(jìn)行的。
由此可見市俊，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í)采用的一致性讀策略和需要的鎖是不同的：
對(duì)于許多 SQL伺帘，隔離級(jí)別越高，InnoDB 給記錄集加的鎖就越嚴(yán)格（尤其是使用范圍條件的時(shí)候）忌锯，產(chǎn)生鎖沖突的可能性也就越高伪嫁，從而對(duì)并發(fā)性事務(wù)處理性能的 影響也就越大。
因此偶垮， 我們?cè)趹?yīng)用中张咳， 應(yīng)該盡量使用較低的隔離級(jí)別帝洪， 以減少鎖爭(zhēng)用的機(jī)率。實(shí)際上脚猾，通過優(yōu)化事務(wù)邏輯葱峡，大部分應(yīng)用使用 Read Commited 隔離級(jí)別就足夠了。對(duì)于一些確實(shí)需要更高隔離級(jí)別的事務(wù)婚陪， 可以通過在程序中執(zhí)行 SET SESSION TRANSACTION ISOLATION
LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 動(dòng)態(tài)改變隔離級(jí)別的方式滿足需求族沃。
獲取 InnoDB 行鎖爭(zhēng)用情況：
可以通過檢查 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 |
+-------------------------------+-------+
5 rows in set (0.01 sec)
LOCK TABLES 和 UNLOCK TABLES
Mysql也支持lock tables和unlock tables，這都是在服務(wù)器層（MySQL Server層）實(shí)現(xiàn)的泌参，和存儲(chǔ)引擎無關(guān)脆淹，它們有自己的用途，并不能替代事務(wù)處理沽一。 （除了禁用了autocommint后可以使用盖溺，其他情況不建議使用）：
- 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)?UNLOCK 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;
使用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 后农曲，只能訪問顯式加鎖的這些表社搅，不能訪問未加鎖的表驻债；同時(shí)，如果加的是讀鎖形葬，那么只能執(zhí)行查詢操作合呐，而不能執(zhí)行更新操作。
其實(shí)笙以，在MyISAM自動(dòng)加鎖（表鎖）的情況下也大致如此淌实，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，這也正是 MyISAM 表不會(huì)出現(xiàn)死鎖（Deadlock Free）的原因猖腕。
例如拆祈，有一個(gè)訂單表 orders，其中記錄有各訂單的總金額 total倘感，同時(shí)還有一個(gè) 訂單明細(xì)表 order_detail放坏，其中記錄有各訂單每一產(chǎn)品的金額小計(jì) subtotal，假設(shè)我們需要檢 查這兩個(gè)表的金額合計(jì)是否相符老玛，可能就需要執(zhí)行如下兩條 SQL：
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
這時(shí)淤年，如果不先給兩個(gè)表加鎖，就可能產(chǎn)生錯(cuò)誤的結(jié)果蜡豹，因?yàn)榈谝粭l語句執(zhí)行過程中麸粮，
order_detail 表可能已經(jīng)發(fā)生了改變。因此镜廉，正確的方法應(yīng)該是：
Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;
（在 LOCK TABLES 時(shí)加了“l(fā)ocal”選項(xiàng)弄诲，其作用就是允許當(dāng)你持有表的讀鎖時(shí)，其他用戶可以在滿足 MyISAM 表并發(fā)插入條件的情況下桨吊，在表尾并發(fā)插入記錄（MyISAM 存儲(chǔ)引擎支持“并發(fā)插入”））
死鎖（Deadlock Free）
死鎖產(chǎn)生：
死鎖是指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用威根，并請(qǐng)求鎖定對(duì)方占用的資源，從而導(dǎo)致惡性循環(huán)视乐。
當(dāng)事務(wù)試圖以不同的順序鎖定資源時(shí)洛搀，就可能產(chǎn)生死鎖。多個(gè)事務(wù)同時(shí)鎖定同一個(gè)資源時(shí)也可能會(huì)產(chǎn)生死鎖佑淀。
鎖的行為和順序和存儲(chǔ)引擎相關(guān)留美。以同樣的順序執(zhí)行語句，有些存儲(chǔ)引擎會(huì)產(chǎn)生死鎖有些不會(huì)——死鎖有雙重原因：真正的數(shù)據(jù)沖突伸刃；存儲(chǔ)引擎的實(shí)現(xiàn)方式谎砾。
檢測(cè)死鎖：數(shù)據(jù)庫系統(tǒng)實(shí)現(xiàn)了各種死鎖檢測(cè)和死鎖超時(shí)的機(jī)制。InnoDB存儲(chǔ)引擎能檢測(cè)到死鎖的循環(huán)依賴并立即返回一個(gè)錯(cuò)誤捧颅。
死鎖恢復(fù)：死鎖發(fā)生以后景图，只有部分或完全回滾其中一個(gè)事務(wù)，才能打破死鎖碉哑，InnoDB目前處理死鎖的方法是挚币，將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾亮蒋。所以事務(wù)型應(yīng)用程序在設(shè)計(jì)時(shí)必須考慮如何處理死鎖，多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可妆毕。
外部鎖的死鎖檢測(cè)：發(fā)生死鎖后慎玖，InnoDB 一般都能自動(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ì)更有效繁涂，這時(shí)可以依賴innodb_lock_wait_timeout設(shè)置進(jìn)行事務(wù)回滾拱她。
MyISAM避免死鎖：
- 在自動(dòng)加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖扔罪，所以 MyISAM 表不會(huì)出現(xiàn)死鎖秉沼。
InnoDB避免死鎖：
- 為了在單個(gè)InnoDB表上執(zhí)行多個(gè)并發(fā)寫入操作時(shí)避免死鎖，可以在事務(wù)開始時(shí)通過為預(yù)期要修改的每個(gè)元祖（行）使用SELECT ... FOR UPDATE語句來獲取必要的鎖矿酵，即使這些行的更改語句是在之后才執(zhí)行的唬复。
- 在事務(wù)中，如果要更新記錄全肮，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖敞咧，即排他鎖，而不應(yīng)先申請(qǐng)共享鎖辜腺、更新時(shí)再申請(qǐng)排他鎖休建，因?yàn)檫@時(shí)候當(dāng)用戶再申請(qǐng)排他鎖時(shí)，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖评疗，從而造成鎖沖突测砂，甚至死鎖
- 如果事務(wù)需要修改或鎖定多個(gè)表，則應(yīng)在每個(gè)事務(wù)中以相同的順序使用加鎖語句百匆。 在應(yīng)用中砌些，如果不同的程序會(huì)并發(fā)存取多個(gè)表，應(yīng)盡量約定以相同的順序來訪問表加匈，這樣可以大大降低產(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)求排他鎖归薛，這樣容易產(chǎn)生死鎖
- 不同的程序訪問一組表時(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只在COMMITTED READ（讀提交）和REPEATABLE READ（可重復(fù)讀）兩種隔離級(jí)別下工作
- 對(duì)于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能
樂觀鎖屋厘、悲觀鎖
- 樂觀鎖(Optimistic Lock)：假設(shè)不會(huì)發(fā)生并發(fā)沖突涕烧，只在提交操作時(shí)檢查是否違反數(shù)據(jù)完整性。 樂觀鎖不能解決臟讀的問題汗洒。
樂觀鎖, 顧名思義议纯，就是很樂觀，每次去拿數(shù)據(jù)的時(shí)候都認(rèn)為別人不會(huì)修改溢谤，所以不會(huì)上鎖瞻凤，但是在更新的時(shí)候會(huì)判斷一下在此期間別人有沒有去更新這個(gè)數(shù)據(jù)憨攒，可以使用版本號(hào)等機(jī)制。樂觀鎖適用于多讀的應(yīng)用類型阀参，這樣可以提高吞吐量肝集，像數(shù)據(jù)庫如果提供類似于write_condition機(jī)制的其實(shí)都是提供的樂觀鎖。
- 悲觀鎖(Pessimistic Lock)：假定會(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ù)庫里邊就用到了很多這種鎖機(jī)制，比如行鎖溜族，表鎖等讹俊，讀鎖，寫鎖等煌抒，都是在做操作之前先上鎖劣像。
