mysql锁机制为何设计如此复杂_再谈mysql锁机制及原理—锁的诠释
加鎖是實現(xiàn)數(shù)據(jù)庫并發(fā)控制的一個非常重要的技術(shù)。當(dāng)事務(wù)在對某個數(shù)據(jù)對象進(jìn)行操作前，先向系統(tǒng)發(fā)出請求，對其加鎖。加鎖后事務(wù)就對該數(shù)據(jù)對象有了一定的控制，在該事務(wù)釋放鎖之前，其他的事務(wù)不能對此數(shù)據(jù)對象進(jìn)行更新操作。
什么是鎖？
鎖是計算機(jī)協(xié)調(diào)多個進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。鎖保證數(shù)據(jù)并發(fā)訪問的一致性、有效性；
鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個重要因素。
鎖是Mysql在服務(wù)器層和存儲引擎層的的并發(fā)控制。
為什么要加鎖
數(shù)據(jù)庫是一個多用戶使用的共享資源。當(dāng)多個用戶并發(fā)地存取數(shù)據(jù)時，在數(shù)據(jù)庫中就會產(chǎn)生多個事務(wù)同時存取同一數(shù)據(jù)的情況。若對并發(fā)操作不加控制就可能會讀取和存儲不正確的數(shù)據(jù)，破壞數(shù)據(jù)庫的一致性。
鎖是用于管理對公共資源的并發(fā)控制。也就是說在并發(fā)的情況下，會出現(xiàn)資源競爭，所以需要加鎖。
加鎖解決了 多用戶環(huán)境下保證數(shù)據(jù)庫完整性和一致性。
Lock的對象是事務(wù)，用來鎖定的是數(shù)據(jù)庫中的對象，如表、頁、行。并且一般lock的對象僅在事務(wù)commit或rollback后進(jìn)行釋放(不同事務(wù)隔離級別釋放的時間可能不同)。
鎖分類共享鎖||讀鎖||S 鎖(share lock)：其他事務(wù)可以讀，但不能寫。允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
排他鎖||寫鎖||X 鎖(exclusive) ：其他事務(wù)不能讀取，也不能寫。允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。
類型細(xì)分：意向共享鎖(IS Lock/intent share lock)
意向排他鎖||互斥鎖(IX Lock/intent exclusive lock)
悲觀鎖||保守鎖(pessimistic locking):假定會發(fā)生并發(fā)沖突，屏蔽一切可能違反數(shù)據(jù)完整性的操作。
悲觀鎖是數(shù)據(jù)庫層面加鎖，都會阻塞去等待鎖。
樂觀鎖(optimistic locking)：假設(shè)不會發(fā)生并發(fā)沖突，只在提交操作時檢查是否違反數(shù)據(jù)完整性。
樂觀鎖是一種思想，具體實現(xiàn)是，表中有一個版本字段，第一次讀的時候，獲取到這個字段。處理完業(yè)務(wù)邏輯開始更新的時候，需要再次查看該字段的值是否和第一次的一樣。如果一樣更新，反之拒絕。之所以叫樂觀，因為這個模式?jīng)]有從數(shù)據(jù)庫加鎖，等到更新的時候再判斷是否可以更新。
缺點：并發(fā)很高的時候，多了很多無用的重試。樂觀鎖，不能解決臟讀的問題。
鎖策略:鎖的粒度/粒度維度(lock granularity)
鎖的開銷是較為昂貴的，鎖策略其實就是保證了線程安全的同時獲取最大的性能之間的平衡策略。
行級鎖(row-level locking)
行鎖：即只允許事務(wù)讀一行數(shù)據(jù)。行鎖的粒度實在每一條行數(shù)據(jù)，當(dāng)然也帶來了最大開銷，但是行鎖可以最大限度的支持并發(fā)處理。
開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
最大程度的支持并發(fā)，同時也帶來了最大的鎖開銷。
行級鎖更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時又有并發(fā)查詢的應(yīng)用，如一些在線事務(wù)處理(OLTP)系統(tǒng)
在 InnoDB 中，除單個 SQL 組成的事務(wù)外，鎖是逐步獲得的，這就決定了在 InnoDB 中發(fā)生死鎖是可能的。
行級鎖只在存儲引擎層實現(xiàn)，而Mysql服務(wù)器層沒有實現(xiàn)。
表級鎖(table-level locking)
表鎖：允許事務(wù)在行級上的鎖和表級上的鎖同時存在。鎖定整個表，開銷最小，但是也阻塞了整個表。
開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
這些存儲引擎通過總是一次性同時獲取所有需要的鎖以及總是按相同的順序獲取表鎖來避免死鎖。
表級鎖更適合于以查詢?yōu)橹?#xff0c;并發(fā)用戶少，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web 應(yīng)用。若一個用戶正在執(zhí)行寫操作，會獲取排他的“寫鎖”，這可能會鎖定整個表，阻塞其他用戶的讀、寫操作；
若一個用戶正在執(zhí)行讀操作，會先獲取共享鎖“讀鎖”，這個鎖運行其他讀鎖并發(fā)的對這個表進(jìn)行讀取，互不干擾。只要沒有寫鎖的進(jìn)入，讀鎖可以是并發(fā)讀取統(tǒng)一資源的。
Mysql的表級別鎖分為兩類：元數(shù)據(jù)鎖(Metadata Lock，MDL)、表鎖。
元數(shù)據(jù)鎖(Metadata Lock，MDL)
元數(shù)據(jù)鎖(MDL) 不需要顯式使用，在訪問一個表的時候會被自動加上。這個特性需要MySQL5.5版本以上才會支持，當(dāng)對一個表做增刪改查的時候，該表會被加MDL讀鎖
當(dāng)對表做結(jié)構(gòu)變更的時候，加MDL寫鎖
MDL鎖規(guī)則：讀鎖之間不互斥，所以可以多線程多同一張表進(jìn)行增刪改查。
讀寫鎖、寫鎖之間是互斥的，為了保證表結(jié)構(gòu)變更的安全性，所以如果要多線程對同一個表加字段等表結(jié)構(gòu)操作，就會變成串行化，需要進(jìn)行鎖等待。
MDL的寫鎖優(yōu)先級比MDL讀鎖的優(yōu)先級，但是可以設(shè)置max_write_lock_count系統(tǒng)變量來改變這種情況，當(dāng)寫鎖請求超過這個變量設(shè)置的數(shù)后，MDL讀鎖的優(yōu)先級會比MDL寫鎖的優(yōu)先級高。(默認(rèn)情況下，這個數(shù)字會很大，所以不用擔(dān)心寫鎖的優(yōu)先級下降)
MDL的鎖釋放必須要等到事務(wù)結(jié)束才會釋放
頁面鎖(page-level locking)
頁級鎖定是 MySQL 中比較獨特的一種鎖定級別，在其他數(shù)據(jù)庫管理軟件中也并不是太常見。
頁面鎖開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般。
頁級鎖定的特點是鎖定顆粒度介于行級鎖定與表級鎖之間，所以獲取鎖定所需要的資源開銷，以及所能提供的并發(fā)處理能力也同樣是介于上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發(fā)生死鎖。
在數(shù)據(jù)庫實現(xiàn)資源鎖定的過程中，隨著鎖定資源顆粒度的減小，鎖定相同數(shù)據(jù)量的數(shù)據(jù)所需要消耗的內(nèi)存數(shù)量是越來越多的，實現(xiàn)算法也會越來越復(fù)雜。
不過，隨著鎖定資源顆粒度的減小，應(yīng)用程序的訪問請求遇到鎖等待的可能性也會隨之降低，系統(tǒng)整體并發(fā)度也隨之提升。
使用頁級鎖定的主要是 BerkeleyDB 存儲引擎。
全局鎖
MySQL 提供全局鎖來對整個數(shù)據(jù)庫實例加鎖。
FLUSH TABLES WITH READ LOCK
這條語句一般都是用來備份的，當(dāng)執(zhí)行這條語句后，數(shù)據(jù)庫所有打開的表都會被關(guān)閉，并且使用全局讀鎖鎖定數(shù)據(jù)庫的所有表，同時，其他線程的更新語句(增刪改)，數(shù)據(jù)定義語句(建表，修改表結(jié)構(gòu))和更新類的事務(wù)提交都會被阻塞。
在mysql 8.0 以后，對于備份，mysql可以直接使用備份鎖。
LOCK INSTANCE FOR BACKUP UNLOCK INSTANCE
這個鎖的作用范圍更廣，這個鎖會阻止文件的創(chuàng)建，重命名，刪除，包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作以及賬戶的管理都會被阻塞。當(dāng)然這些操作對于內(nèi)存臨時表來說是可以執(zhí)行的，為什么內(nèi)存表不受這些限制呢？因為內(nèi)存表不需要備份，所以也就沒必要滿足這些條件。
MySQL不同的存儲引擎支持不同的鎖機(jī)制
所有的存儲引擎都以自己的方式顯現(xiàn)了鎖機(jī)制，服務(wù)器層完全不了解存儲引擎中的鎖實現(xiàn)：MyISAM、MEMORY、CSV存儲引擎采用的是表級鎖(table-level locking)
BDB(Berkeley DB) 存儲引擎采用的是頁面鎖(page-level locking)，但也支持表級鎖
InnoDB 存儲引擎既支持行級鎖(row-level locking)，也支持表級鎖，但默認(rèn)情況下是采用行級鎖。InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！
行級鎖都是基于索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。行級鎖的缺點是：由于需要請求大量的鎖資源，所以速度慢，內(nèi)存消耗大。
默認(rèn)情況下，表鎖和行鎖都是自動獲得的， 不需要額外的命令。
但是在有的情況下， 用戶需要明確地進(jìn)行鎖表或者進(jìn)行事務(wù)的控制， 以便確保整個事務(wù)的完整性，這樣就需要使用事務(wù)控制和鎖定語句來完成。
InnoDB與MyISAM的最大不同有兩點：一是支持事務(wù)(TRANSACTION)；二是采用了行級鎖。
Innodb存儲引擎由于實現(xiàn)了行級鎖定，雖然在鎖定機(jī)制的實現(xiàn)方面所帶來的性能損耗可能比表級鎖定會要更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MyISAM的表級鎖定的。當(dāng)系統(tǒng)并發(fā)量較高的時候，Innodb的整體性能和MyISAM相比就會有比較明顯的優(yōu)勢了。
但是，Innodb的行級鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r候，可能會讓Innodb的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會更差。
InnoDB行級鎖和表級鎖
InnoDB鎖模式：
InnoDB 實現(xiàn)了以下兩種類型的行鎖：共享鎖(S-shared)：允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。
排他鎖(X-exclusive)：允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。
為了支持在不同粒度上進(jìn)行加鎖操作(允許行鎖和表鎖共存，實現(xiàn)多粒度鎖機(jī)制)，InnoDB 還有兩種內(nèi)部使用的意向鎖(Intention Locks)，這兩種意向鎖都是表鎖：意向共享鎖(IS- intent share lock)事務(wù)想要獲得一張表中某幾行的共享鎖
務(wù)打算給數(shù)據(jù)行加行共享鎖，事務(wù)在給一個數(shù)據(jù)行加共享鎖前必須先取得該表的 IS 鎖。
意向排他鎖(IX -intent exclusive lock)事務(wù)想要獲得一張表中某幾行的排他鎖
事務(wù)打算給數(shù)據(jù)行加行排他鎖，事務(wù)在給一個數(shù)據(jù)行加排他鎖前必須先取得該表的 IX 鎖。
由于InnoDB存儲引擎支持的是行級別的鎖，因此意向鎖其實不會阻塞除全表掃以外的任何請求。故表級意向鎖與行級鎖的兼容性如下所示
如果一個事務(wù)請求的鎖模式與當(dāng)前的鎖兼容， InnoDB 就將請求的鎖授予該事務(wù)； 反之， 如果兩者不兼容，該事務(wù)就要等待鎖釋放
若將上鎖的對象看成一棵樹，那么對最下層的對象上鎖，也就是對最細(xì)粒度的對象進(jìn)行上鎖，那么首先需要對粗粒度的對象上鎖。例上圖，如果需要對頁上的記錄r進(jìn)行上X鎖，那么分別需要對數(shù)據(jù)庫A、表、頁上意向鎖IX，最后對記錄r上X鎖。若其中任何一個部分導(dǎo)致等待，那么該操作需要等待粗粒度鎖的完成。舉例來說，在對記錄r加X鎖之前，已經(jīng)有事務(wù)對表1進(jìn)行了S表鎖，那么表1上已存在S鎖，之后事務(wù)需要對記錄r在表1上加上IX，由于不兼容，所以該事務(wù)需要等待表鎖操作的完成。
意向鎖到底有什么作用？
innodb的意向鎖主要用戶多粒度的鎖并存的情況。比如事務(wù)A要在一個表上加S鎖，如果表中的一行已被事務(wù)B加了X鎖，那么該鎖的申請也應(yīng)被阻塞。如果表中的數(shù)據(jù)很多，逐行檢查鎖標(biāo)志的開銷將很大，系統(tǒng)的性能將會受到影響。為了解決這個問題，可以在表級上引入新的鎖類型來表示其所屬行的加鎖情況，這就引出了“意向鎖”的概念。
舉個例子，如果表中記錄1億，事務(wù)A把其中有幾條記錄上了行鎖了，這時事務(wù)B需要給這個表加表級鎖，如果沒有意向鎖的話，那就要去表中查找這一億條記錄是否上鎖了。如果存在意向鎖，那么假如事務(wù)Ａ在更新一條記錄之前，先加意向鎖，再加Ｘ鎖，事務(wù)B先檢查該表上是否存在意向鎖，存在的意向鎖是否與自己準(zhǔn)備加的鎖沖突，如果有沖突，則等待直到事務(wù)Ａ釋放，而無須逐條記錄去檢測。事務(wù)Ｂ更新表時，其實無須知道到底哪一行被鎖了，它只要知道反正有一行被鎖了就行了。
主要作用是處理行鎖和表鎖之間的矛盾，能夠顯示“某個事務(wù)正在某一行上持有了鎖，或者準(zhǔn)備去持有鎖”
InnoDB 行鎖實現(xiàn)方式：
行鎖是加在索引上的
Innodb中的索引數(shù)據(jù)結(jié)構(gòu)是 B+ 樹，數(shù)據(jù)是有序排列的，從根節(jié)點到葉子節(jié)點一層層找到對應(yīng)的數(shù)據(jù)。
普通索引，也叫做輔助索引，葉子節(jié)點存放的是主鍵值。主鍵上的索引叫做聚集索引，表里的每一條記錄都存放在主鍵的葉子節(jié)點上。當(dāng)通過輔助索引select 查詢數(shù)據(jù)的時候，會先在輔助索引中找到對應(yīng)的主鍵值，然后用主鍵值在聚集索引中找到該條記錄。
舉個例子，用name=Alice來查詢的時候，會先找到對應(yīng)的主鍵值是18 ，然后用18在下面的聚集索引中找到name=Alice的記錄內(nèi)容是 77 和 Alice。
表中每一行的數(shù)據(jù)，是組織存放在聚集索引中的，所以叫做索引組織表。InnoDB 行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，這一點 MySQL 與 Oracle 不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB 這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖！
不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會使用行鎖來對數(shù)據(jù)加鎖。
只有執(zhí)行計劃真正使用了索引，才能使用行鎖：即便在條件中使用了索引字段，但是否使用索引來檢索數(shù)據(jù)是由 MySQL 通過判斷不同執(zhí)行計劃的代價來決定的，如果 MySQL 認(rèn)為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下 InnoDB 將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查 SQL 的執(zhí)行計劃(可以通過 explain 檢查 SQL 的執(zhí)行計劃)，以確認(rèn)是否真正使用了索引。(更多閱讀：MySQL索引總結(jié))
由于 MySQL 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然多個session是訪問不同行的記錄， 但是如果是使用相同的索引鍵， 是會出現(xiàn)鎖沖突的(后使用這些索引的session需要等待先使用索引的session釋放鎖后，才能獲取鎖)。 應(yīng)用設(shè)計的時候要注意這一點。
行鎖的算法(算法維度)Record Lock(單行記錄)
Gap Lock(間隙鎖，鎖定一個范圍，但不包含鎖定記錄)
Next-Key Lock(Record Lock + Gap Lock，鎖定一個范圍，并且鎖定記錄本身， MySql 防止幻讀，就是使用此鎖實現(xiàn))
記錄鎖、間隙鎖、臨鍵鎖都是排它鎖
記錄鎖(Record Lock)
事務(wù)加鎖后鎖住的只是表的某一條記錄。
記錄鎖出現(xiàn)條件：精準(zhǔn)條件命中，并且命中的條件字段是唯一索引；
例如：update user_info set name=’張三’ where id=1 ,這里的id是唯一索引。
Record Lock總是會去鎖住索引記錄，如果InnoDB存儲引擎表在建立的時候沒有設(shè)置任何一個索引，那么這時InnoDB存儲引擎會使用隱式的主鍵來進(jìn)行鎖定。
記錄鎖的作用：加了記錄鎖之后可以避免數(shù)據(jù)在查詢的時候被修改的重復(fù)讀問題，也避免了在修改的事務(wù)未提交前被其他事務(wù)讀取的臟讀問題。
臨鍵鎖(Next-Key Lock)
臨鍵鎖是INNODB的行鎖默認(rèn)算法，它是記錄鎖和間隙鎖的組合，臨鍵鎖會把查詢出來的記錄鎖住，同時也會把該范圍查詢內(nèi)的所有間隙空間也會鎖住，再之它會把相鄰的下一個區(qū)間也會鎖住。
臨鍵鎖出現(xiàn)條件：范圍查詢并命中，查詢命中了索引。
比如下面表的數(shù)據(jù)執(zhí)行 select * from user_info where id>1 and id<=13 for update ;
會鎖住ID為 1,5,10的記錄；同時會鎖住，1至5,5至10,10至15的區(qū)間。
臨鍵鎖的作用：結(jié)合記錄鎖和間隙鎖的特性，臨鍵鎖避免了在范圍查詢時出現(xiàn)臟讀、重復(fù)讀、幻讀問題。加了臨鍵鎖之后，在范圍區(qū)間內(nèi)數(shù)據(jù)不允許被修改和插入。
Next-Key Lock是結(jié)合了Gap Lock和Record Lock的一種鎖定算法，在Next-Key Lock算法下，InnoDB對于行的查詢都是采用這種鎖定算法。
除了Next-Key Locking，還有Previous-Key Locking技術(shù)。
間隙鎖(gap lcok )：
當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請求共享或排他鎖時，InnoDB會給符合條件的已有數(shù)據(jù)記錄的索引項加鎖；對于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙(GAP)”，InnoDB也會對這個“間隙”加鎖，這種鎖機(jī)制就是所謂的間隙鎖。
很顯然，在使用范圍條件檢索并鎖定記錄時，InnoDB這種加鎖機(jī)制會阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入，這往往會造成嚴(yán)重的鎖等待。因此，在實際應(yīng)用開發(fā)中，尤其是并發(fā)插入比較多的應(yīng)用，我們要盡量優(yōu)化業(yè)務(wù)邏輯，盡量使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件。
間隙鎖的目的：防止幻讀，以滿足相關(guān)隔離級別的要求；
滿足恢復(fù)和復(fù)制的需要：
產(chǎn)生間隙鎖的條件(RR事務(wù)隔離級別下)：使用普通索引鎖定；
使用多列唯一索引；
使用唯一索引鎖定多行記錄。
以上情況，都會產(chǎn)生間隙鎖
MySQL 通過 BINLOG 錄入執(zhí)行成功的 INSERT、UPDATE、DELETE 等更新數(shù)據(jù)的 SQL 語句，并由此實現(xiàn) MySQL 數(shù)據(jù)庫的恢復(fù)和主從復(fù)制。MySQL 的恢復(fù)機(jī)制(復(fù)制其實就是在 Slave Mysql 不斷做基于 BINLOG 的恢復(fù))有以下特點：一是 MySQL 的恢復(fù)是 SQL 語句級的，也就是重新執(zhí)行 BINLOG 中的 SQL 語句。
二是 MySQL 的 Binlog 是按照事務(wù)提交的先后順序記錄的， 恢復(fù)也是按這個順序進(jìn)行的。
由此可見，MySQL 的恢復(fù)機(jī)制要求：在一個事務(wù)未提交前，其他并發(fā)事務(wù)不能插入滿足其鎖定條件的任何記錄，也就是不允許出現(xiàn)幻讀。
這張圖里出現(xiàn)了三種鎖記錄鎖：單行記錄上的鎖
間隙鎖：鎖定記錄之間的范圍，但不包含記錄本身。
Next Key Lock: 記錄鎖+ 間隙鎖，鎖定一個范圍，包含記錄本身。
不是所有索引都會加上Next-key Lock的，在查詢的列是唯一索引(包含主鍵索引)的情況下，Next-key Lock會降級為Record Lock。
CREATE TABLE z (a INT,b INT,PRIMARY KEY(a),KEY(b));// a是主鍵索引，b是普通索引
INSERT INTO z select1,1;
INSERT INTO z select3,1;
INSERT INTO z select5,3;
INSERT INTO z select7,6;
INSERT INTO z select10,8;
這時候在會話A中執(zhí)行 SELECT*FROM z WHERE b=3FOR UPDATE ，索引鎖定如下：
這時候會話B執(zhí)行的語句落在鎖定范圍內(nèi)的都會進(jìn)行waiting
SELECT * FROM z WHERE a =5 LOCK IN SHARE MODE;
INSERT INTO z SELECT 4,2;
INSERT INTO z SELECT 6,5;
用戶可以通過以下兩種方式來顯示的關(guān)閉Gap Lock：將事務(wù)的隔離級別設(shè)為 READ COMMITED。
將參數(shù)innodblocksunsafeforbinlog設(shè)置為1。
從上面的例子可以看出來，Gap Lock的作用是為了阻止多個事務(wù)將記錄插入到同一個范圍內(nèi)，設(shè)計它的目的是用來解決Phontom Problem(幻讀問題)。在MySQL默認(rèn)的隔離級別(Repeatable Read)下，InnoDB就是使用它來解決幻讀問題。
InnoDB加鎖方法：意向鎖是 InnoDB 自動加的， 不需用戶干預(yù)。
對于 UPDATE、 DELETE 和 INSERT 語句， InnoDB 會自動給涉及數(shù)據(jù)集加排他鎖(X)；
對于普通 SELECT 語句，InnoDB 不會加任何鎖；
事務(wù)可以通過以下語句顯式給記錄集加共享鎖或排他鎖：共享鎖(S)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查詢記錄，并也可以對該記錄加 share mode 的共享鎖。但是如果當(dāng)前事務(wù)需要對該記錄進(jìn)行更新操作，則很有可能造成死鎖。
排他鎖(X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 可以查詢該記錄，但是不能對該記錄加共享鎖或排他鎖，而是等待獲得鎖
隱式鎖定：
InnoDB在事務(wù)執(zhí)行過程中，使用兩階段鎖協(xié)議：隨時都可以執(zhí)行鎖定，InnoDB會根據(jù)隔離級別在需要的時候自動加鎖；
鎖只有在執(zhí)行commit或者rollback的時候才會釋放，并且所有的鎖都是在同一時刻被釋放。
顯式鎖定 ：
select ... lock in share mode //共享鎖
select ... for update //排他鎖
select for update
在執(zhí)行這個 select 查詢語句的時候，會將對應(yīng)的索引訪問條目進(jìn)行上排他鎖(X 鎖)，也就是說這個語句對應(yīng)的鎖就相當(dāng)于update帶來的效果。
select *** for update 的使用場景：為了讓自己查到的數(shù)據(jù)確保是最新數(shù)據(jù)，并且查到后的數(shù)據(jù)只允許自己來修改的時候，需要用到 for update 子句。
select * from user where id=10 for update
通過鎖住聚集索引中的節(jié)點來鎖住這條記錄(鎖住id=10的索引，即鎖住了這條記錄)。
select * from user where name=‘b’ for update
這里的name上加了唯一索引，唯一索引本質(zhì)上是輔助索引，加了唯一約束。所以會先在輔助索引上找到name為d的索引記錄，在輔助索引中加鎖，然后查找聚集索引，鎖住對應(yīng)索引記錄。
為什么聚簇索引上的記錄也要加鎖？
試想一下，如果有并發(fā)的另外一個SQL，是直接通過主鍵索引id=30來更新，會先在聚集索引中請求加鎖。如果只在輔助索引中加鎖的話，兩個并發(fā)SQL之間是互相感知不到的。
select lock in share mode
in share mode 子句的作用就是將查找到的數(shù)據(jù)加上一個 share 鎖，這個就是表示其他的事務(wù)只能對這些數(shù)據(jù)進(jìn)行簡單的select 操作，并不能夠進(jìn)行 DML 操作。
select *** lock in share mode 使用場景：為了確保自己查到的數(shù)據(jù)沒有被其他的事務(wù)正在修改，也就是說確保查到的數(shù)據(jù)是最新的數(shù)據(jù)，并且不允許其他人來修改數(shù)據(jù)。但是自己不一定能夠修改數(shù)據(jù)，因為有可能其他的事務(wù)也對這些數(shù)據(jù) 使用了 in share mode 的方式上了 S 鎖。
for update 和 lock in share mode 的區(qū)別：
前一個上的是排他鎖(X 鎖)，一旦一個事務(wù)獲取了這個鎖，其他的事務(wù)是沒法在這些數(shù)據(jù)上執(zhí)行 for update ；后一個是共享鎖，多個事務(wù)可以同時的對相同數(shù)據(jù)執(zhí)行 lock in share mode。
顯式鎖定對性能影響(performance impact)：
select for update 語句，相當(dāng)于一個 update 語句。在業(yè)務(wù)繁忙的情況下，如果事務(wù)沒有及時的commit或者rollback 可能會造成其他事務(wù)長時間的等待，從而影響數(shù)據(jù)庫的并發(fā)使用效率。
select lock in share mode 語句是一個給查找的數(shù)據(jù)上一個共享鎖(S 鎖)的功能，它允許其他的事務(wù)也對該數(shù)據(jù)上S鎖，但是不能夠允許對該數(shù)據(jù)進(jìn)行修改。如果不及時的commit 或者rollback 也可能會造成大量的事務(wù)等待。
默認(rèn)的讀操作，上鎖嗎
默認(rèn)是 MVCC 機(jī)制(“一致性非鎖定讀-consistent nonlocking read”)保證 RR 級別的隔離正確性，是不上鎖的。
可以選擇手動上鎖：select xxxx for update (排他鎖); select xxxx lock in share mode(共享鎖)，稱之為“一致性鎖定讀”。
使用鎖之后，就能在 RR 級別下，避免幻讀。當(dāng)然，默認(rèn)的 MVCC 讀，也能避免幻讀。
既然 RR 能夠防止幻讀，那么，SERIALIZABLE 有啥用呢？防止丟失更新。
這個時候，我們必須使用 SERIALIZABLE 級別進(jìn)行串行讀取。
最后，行鎖的實現(xiàn)原理就是鎖住聚集索引，如果你查詢的時候，沒有正確地?fù)糁兴饕?#xff0c;MySql 優(yōu)化器將會拋棄行鎖，使用表鎖。
InnoDB 在不同隔離級別下的一致性讀及鎖的差異：
鎖和多版本數(shù)據(jù)(MVCC)是 InnoDB 實現(xiàn)一致性讀和 ISO/ANSI SQL92 隔離級別的手段。
因此，在不同的隔離級別下，InnoDB 處理 SQL 時采用的一致性讀策略和需要的鎖是不同的：
對于許多 SQL，隔離級別越高，InnoDB 給記錄集加的鎖就越嚴(yán)格(尤其是使用范圍條件的時候)，產(chǎn)生鎖沖突的可能性也就越高，從而對并發(fā)性事務(wù)處理性能的 影響也就越大。
因此， 我們在應(yīng)用中， 應(yīng)該盡量使用較低的隔離級別， 以減少鎖爭用的機(jī)率。實際上，通過優(yōu)化事務(wù)邏輯，大部分應(yīng)用使用 Read Commited 隔離級別就足夠了。對于一些確實需要更高隔離級別的事務(wù)， 可以通過在程序中執(zhí)行 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 或 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE 動態(tài)改變隔離級別的方式滿足需求。
InnoDB 行鎖優(yōu)化建議
合理利用 InnoDB 的行級鎖定，做到揚長避短盡可能讓所有的數(shù)據(jù)檢索都通過索引來完成，從而避免 InnoDB 因為無法通過索引鍵加鎖而升級為表級鎖定。
合理設(shè)計索引，讓 InnoDB 在索引鍵上面加鎖的時候盡可能準(zhǔn)確，盡可能的縮小鎖定范圍，避免造成不必要的鎖定而影響其他 Query 的執(zhí)行。
盡可能減少基于范圍的數(shù)據(jù)檢索過濾條件，避免因為間隙鎖帶來的負(fù)面影響而鎖定了不該鎖定的記錄。
盡量控制事務(wù)的大小，減少鎖定的資源量和鎖定時間長度。
在業(yè)務(wù)環(huán)境允許的情況下，盡量使用較低級別的事務(wù)隔離，以減少 MySQL 因為實現(xiàn)事務(wù)隔離級別所帶來的附加成本。
infoDB什么時候加表鎖
對于 InnoDB 表，在絕大部分情況下都應(yīng)該使用行級鎖，因為事務(wù)和行鎖往往是我們之所以選擇 InnoDB 表的理由。
事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大，如果使用默認(rèn)的行鎖，不僅這個事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務(wù)的執(zhí)行速度。
事務(wù)涉及多個表，比較復(fù)雜，很可能引起死鎖，造成大量事務(wù)回滾。這種情況也可以考慮一次性鎖定事務(wù)涉及的表，從而避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。
在 InnoDB 下，使用表鎖要注意以下兩點：使用 LOCK TABLES 雖然可以給 InnoDB 加表級鎖，但必須說明的是，表鎖不是由 InnoDB 存儲引擎層管理的，而是由其上一層──MySQL Server 負(fù)責(zé)的。僅當(dāng) autocommit=0(不自動提交，默認(rèn)是自動提交的)、InnoDB_table_locks=1(默認(rèn)設(shè)置)時，InnoDB 層才能知道 MySQL 加的表鎖，MySQL Server 也才能感知 InnoDB 加的行鎖。這種情況下，InnoDB 才能自動識別涉及表級鎖的死鎖，否則，InnoDB 將無法自動檢測并處理這種死鎖。
在用 LOCK TABLES 對 InnoDB 表加鎖時要注意，要將 AUTOCOMMIT 設(shè)為 0，否則 MySQL 不會給表加鎖。事務(wù)結(jié)束前，不要用 UNLOCK TABLES 釋放表鎖，因為 UNLOCK TABLES 會隱含地提交事務(wù)。COMMIT 或 ROLLBACK 并不能釋放用 LOCK TABLES 加的表級鎖，必須用 UNLOCK TABLES 釋放表鎖。
正確的方式見如下語句，例如，如果需要寫表 t1 并從表 t 讀，可以按如下做：
SET AUTOCOMMIT=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and t2 here];
COMMIT;
UNLOCK TABLES;
MyISAM 表鎖
MyISAM表級鎖模式：表共享讀鎖 (Table Read Lock)：不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；
表獨占寫鎖 (Table Write Lock)：會阻塞其他用戶對同一表的讀和寫操作；
MyISAM 表的讀操作與寫操作之間，以及寫操作之間是串行的。當(dāng)一個線程獲得對一個表的寫鎖后， 只有持有鎖的線程可以對表進(jìn)行更新操作。 其他線程的讀、 寫操作都會等待，直到鎖被釋放為止。
默認(rèn)情況下，寫鎖比讀鎖具有更高的優(yōu)先級：當(dāng)一個鎖釋放時，這個鎖會優(yōu)先給寫鎖隊列中等候的獲取鎖請求，然后再給讀鎖隊列中等候的獲取鎖請求。 (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)用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠(yuǎn)阻塞。同時，一些需要長時間運行的查詢操作，也會使寫線程“餓死” ，應(yīng)用中應(yīng)盡量避免出現(xiàn)長時間運行的查詢操作(在可能的情況下可以通過使用中間表等措施對SQL語句做一定的“分解” ，使每一步查詢都能在較短時間完成，從而減少鎖沖突。如果復(fù)雜查詢不可避免，應(yīng)盡量安排在數(shù)據(jù)庫空閑時段執(zhí)行，比如一些定期統(tǒng)計可以安排在夜間執(zhí)行)。
可以設(shè)置改變讀鎖和寫鎖的優(yōu)先級：通過指定啟動參數(shù)low-priority-updates，使MyISAM引擎默認(rèn)給予讀請求以優(yōu)先的權(quán)利。
通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請求優(yōu)先級降低。
通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優(yōu)先級。
給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個合適的值，當(dāng)一個表的讀鎖達(dá)到這個值后，MySQL就暫時將寫請求的優(yōu)先級降低，給讀進(jìn)程一定獲得鎖的機(jī)會。
MyISAM加表鎖方法：在執(zhí)行查詢語句(SELECT)前，會自動給涉及的表加讀鎖
在執(zhí)行更新操作(UPDATE、DELETE、INSERT 等)前，會自動給涉及的表加寫鎖
這個過程并不需要用戶干預(yù)，因此，用戶一般不需要直接用 LOCK TABLE 命令給 MyISAM 表顯式加鎖。
在自動加鎖的情況下，MyISAM 總是一次獲得 SQL 語句所需要的全部鎖，這也正是 MyISAM 表不會出現(xiàn)死鎖(Deadlock Free)的原因。
MyISAM存儲引擎支持并發(fā)插入，以減少給定表的讀和寫操作之間的爭用：
如果MyISAM表在數(shù)據(jù)文件中間沒有空閑塊，則行始終插入數(shù)據(jù)文件的末尾。 在這種情況下，你可以自由混合并發(fā)使用MyISAM表的INSERT和SELECT語句而不需要加鎖——你可以在其他線程進(jìn)行讀操作的時候，同時將行插入到MyISAM表中。 文件中間的空閑塊可能是從表格中間刪除或更新的行而產(chǎn)生的。 如果文件中間有空閑快，則并發(fā)插入會被禁用，但是當(dāng)所有空閑塊都填充有新數(shù)據(jù)時，它又會自動重新啟用。 要控制此行為，可以使用MySQL的concurrent_insert系統(tǒng)變量。
如果你使用LOCK TABLES顯式獲取表鎖，則可以請求READ LOCAL鎖而不是READ鎖，以便在鎖定表時，其他會話可以使用并發(fā)插入。當(dāng)concurrent_insert設(shè)置為0時，不允許并發(fā)插入。
當(dāng)concurrent_insert設(shè)置為1時，如果MyISAM表中沒有空洞(即表的中間沒有被刪除的行)，MyISAM允許在一個線程讀表的同時，另一個線程從表尾插入記錄。這也是MySQL的默認(rèn)設(shè)置。
當(dāng)concurrent_insert設(shè)置為2時，無論MyISAM表中有沒有空洞，都允許在表尾并發(fā)插入記錄。
顯示鎖sql語句共享讀鎖：lock table tableName read
獨占寫鎖：lock table tableName write
同時加多鎖：lock table t1 write，t2 read
批量解鎖：unlock tables
MyISAM 表鎖優(yōu)化建議
縮短鎖定時間唯一的辦法就是讓我們的 Query 執(zhí)行時間盡可能的短
盡量減少大的復(fù)雜 Query，將復(fù)雜 Query 分拆成幾個小的 Query 分布進(jìn)行。
盡可能的建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速。
盡量讓 MyISAM 存儲引擎的表只存放必要的信息，控制字段類型。
利用合適的機(jī)會優(yōu)化 MyISAM 表數(shù)據(jù)文件。
分離能并行的操作
MyISAM 存儲引擎有一個控制是否打開 Concurrent Insert 功能的參數(shù)選項：
concurrent_insert=2，無論 MyISAM 表中有沒有空洞，都允許在表尾并發(fā)插入記錄。
concurrent_insert=1，如果 MyISAM 表中沒有空洞(即表的中間沒有被刪除的行)，MyISAM 允許在一個進(jìn)程讀表的同時，另一個進(jìn)程從表尾插入記錄。這也是 MySQL 的默認(rèn)設(shè)置。
concurrent_insert=0，不允許并發(fā)插入
可以利用 MyISAM 存儲引擎的并發(fā)插入特性，來解決應(yīng)用中對同一表查詢和插入的鎖爭用。
例如，將 concurrent_insert 系統(tǒng)變量設(shè)為 2，總是允許并發(fā)插入；同時，通過定期在系統(tǒng)空閑時段執(zhí)行 OPTIMIZE TABLE 語句來整理空間碎片，收回因刪除記錄而產(chǎn)生的中間空洞。
合理利用讀寫優(yōu)先級通過執(zhí)行命令 SET LOW_PRIORITY_UPDATES=1，使該連接讀比寫的優(yōu)先級高，如果我們的系統(tǒng)是一個以讀為主，可以設(shè)置此參數(shù)，如果以寫為主，則不用設(shè)置。
通過指定 INSERT、UPDATE、DELETE 語句的 LOW_PRIORITY 屬性，降低該語句的優(yōu)先級。
MySQL 也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù) max_write_lock_count 設(shè)置一個合適的值，當(dāng)一個表的讀鎖達(dá)到這個值后，MySQL 就暫時將寫請求的優(yōu)先級降低，給讀進(jìn)程一定獲得鎖的機(jī)會。
需要長時間運行的查詢操作，也會使寫進(jìn)程“餓死”，盡量避免出現(xiàn)長時間運行的查詢操作，不要總想用一條 SELECT 語句來解決問題，因為這種看似巧妙的 SQL 語句，往往比較復(fù)雜，執(zhí)行時間較長。
多表級聯(lián)。事務(wù)涉及多個表，比較復(fù)雜的關(guān)聯(lián)查詢，很可能引起死鎖，造成大量事務(wù)回滾，這種情況若能一次性鎖定事務(wù)涉及的表，從而可以避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。
死鎖(Deadlock Free)死鎖產(chǎn)生：死鎖是指兩個或多個事務(wù)在同一資源上相互占用，并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)。
當(dāng)事務(wù)試圖以不同的順序鎖定資源時，就可能產(chǎn)生死鎖。多個事務(wù)同時鎖定同一個資源時也可能會產(chǎn)生死鎖。
鎖的行為和順序和存儲引擎相關(guān)。以同樣的順序執(zhí)行語句，有些存儲引擎會產(chǎn)生死鎖有些不會——死鎖有雙重原因：真正的數(shù)據(jù)沖突；存儲引擎的實現(xiàn)方式。
檢測死鎖：數(shù)據(jù)庫系統(tǒng)實現(xiàn)了各種死鎖檢測和死鎖超時的機(jī)制。InnoDB存儲引擎能檢測到死鎖的循環(huán)依賴并立即返回一個錯誤。
死鎖恢復(fù)：死鎖發(fā)生以后，只有部分或完全回滾其中一個事務(wù)，才能打破死鎖，InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務(wù)進(jìn)行回滾。所以事務(wù)型應(yīng)用程序在設(shè)計時必須考慮如何處理死鎖，多數(shù)情況下只需要重新執(zhí)行因死鎖回滾的事務(wù)即可。
外部鎖的死鎖檢測：發(fā)生死鎖后，InnoDB 一般都能自動檢測到，并使一個事務(wù)釋放鎖并回退，另一個事務(wù)獲得鎖，繼續(xù)完成事務(wù)。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB 并不能完全自動檢測到死鎖， 這需要通過設(shè)置鎖等待超時參數(shù) innodb_lock_wait_timeout 來解決
死鎖影響性能：死鎖會影響性能而不是會產(chǎn)生嚴(yán)重錯誤，因為InnoDB會自動檢測死鎖狀況并回滾其中一個受影響的事務(wù)。在高并發(fā)系統(tǒng)上，當(dāng)許多線程等待同一個鎖時，死鎖檢測可能導(dǎo)致速度變慢。 有時當(dāng)發(fā)生死鎖時，禁用死鎖檢測(使用innodb_deadlock_detect配置選項)可能會更有效，這時可以依賴innodb_lock_wait_timeout設(shè)置進(jìn)行事務(wù)回滾。
MyISAM避免死鎖：
在自動加鎖的情況下，MyISAM 表不會出現(xiàn)死鎖(MyISAM 總是一次獲得 SQL 語句所需要的全部鎖)。
InnoDB避免死鎖：為了在單個InnoDB表上執(zhí)行多個并發(fā)寫入操作時避免死鎖，可以在事務(wù)開始時通過為預(yù)期要修改的每個元祖(行)使用SELECT ... FOR UPDATE語句來獲取必要的鎖，即使這些行的更改語句是在之后才執(zhí)行的。
在事務(wù)中，如果要更新記錄，應(yīng)該直接申請足夠級別的鎖，即排他鎖，而不應(yīng)先申請共享鎖、更新時再申請排他鎖，因為這時候當(dāng)用戶再申請排他鎖時，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖
如果事務(wù)需要修改或鎖定多個表，則應(yīng)在每個事務(wù)中以相同的順序使用加鎖語句。 在應(yīng)用中，如果不同的程序會并發(fā)存取多個表，應(yīng)盡量約定以相同的順序來訪問表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會
通過SELECT ... LOCK IN SHARE MODE獲取行的讀鎖后，如果當(dāng)前事務(wù)再需要對該記錄進(jìn)行更新操作，則很有可能造成死鎖。
改變事務(wù)隔離級別，如降低隔離級別(如果業(yè)務(wù)允許，將隔離級別調(diào)低也是較好的選擇，比如將隔離級別從RR調(diào)整為RC，可以避免掉很多因為gap鎖造成的死鎖)
為表添加合理的索引。可以看到如果不走索引將會為表的每一行記錄添加上鎖，死鎖的概率大大增大。
如果出現(xiàn)死鎖，可以用 SHOW INNODB STATUS 命令來確定最后一個死鎖產(chǎn)生的原因。返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細(xì)信息，如引發(fā)死鎖的 SQL 語句，事務(wù)已經(jīng)獲得的鎖，正在等待什么鎖，以及被回滾的事務(wù)等。據(jù)此可以分析死鎖產(chǎn)生的原因和改進(jìn)措施。
加鎖處理分析
下面兩條簡單的SQL，他們加什么鎖？
select * from t1 where id = 10
delete from t1 where id = 10
如果要分析加鎖情況，必須還要知道以下的一些前提，前提不同，加鎖處理的方式也不同id列是不是主鍵？
當(dāng)前系統(tǒng)的隔離級別是什么？
id列如果不是主鍵，那么id列上有索引嗎？
id列上如果有二級索引，那么這個索引是唯一索引嗎？
兩個SQL的執(zhí)行計劃是什么？索引掃描？全表掃描？
根據(jù)上述情況，有以下幾種組合id列是主鍵，RC隔離級別
id列是二級唯一索引，RC隔離級別
id列是二級非唯一索引，RC隔離級別
id列上沒有索引，RC隔離級別
id列是主鍵，RR隔離級別
id列是二級唯一索引，RR隔離級別
id列是二級非唯一索引，RR隔離級別
id列上沒有索引，RR隔離級別
Serializable隔離級別
排列組合還沒有列舉完全，但是看起來，已經(jīng)很多了。真的有必要這么復(fù)雜嗎？事實上，要分析加鎖，就是需要這么復(fù)雜。但是從另一個角度來說，只要你選定了一種組合，SQL需要加哪些鎖，其實也就確定了。接下來挑幾個比較經(jīng)典的組合
1. id主鍵+RC
這個組合，是最簡單，最容易分析的組合。id是主鍵，Read Committed隔離級別，給定SQL：delete from t1 where id = 10; 只需要將主鍵上，id = 10的記錄加上X鎖即可。如下圖1：
結(jié)論：id是主鍵時，此SQL只需要在id=10這條記錄上加X鎖即可。
2. id唯一索引+RC
這個組合，id不是主鍵，而是一個Unique的二級索引鍵值。那么在RC隔離級別下，delete from t1 where id = 10; 需要加什么鎖呢？見下圖2：
id是unique索引，而主鍵是name列。此時，加鎖的情況由于組合一有所不同。由于id是unique索引，因此delete語句會選擇走id列的索引進(jìn)行where條件的過濾，在找到id=10的記錄后，首先會將unique索引上的id=10索引記錄加上X鎖，同時，會根據(jù)讀取到的name列，回主鍵索引(聚簇索引)，然后將聚簇索引上的name = ‘d’ 對應(yīng)的主鍵索引項加X鎖。
結(jié)論：若id列是unique列，其上有unique索引。那么SQL需要加兩個X鎖，一個對應(yīng)于id unique索引上的id = 10的記錄，另一把鎖對應(yīng)于聚簇索引上的[name='d',id=10]的記錄、
3. id非唯一索引+RC
相對于組合一、二，組合三又發(fā)生了變化，隔離級別仍舊是RC不變，但是id列上的約束又降低了，id列不再唯一，只有一個普通的索引。假設(shè)delete from t1 where id = 10; 語句，仍舊選擇id列上的索引進(jìn)行過濾where條件，那么此時會持有哪些鎖？同樣見下圖3：根據(jù)此圖，可以看到，首先，id列索引上，滿足id = 10查詢條件的記錄，均已加鎖。同時，這些記錄對應(yīng)的主鍵索引上的記錄也都加上了鎖。與組合二唯一的區(qū)別在于，組合二最多只有一個滿足等值查詢的記錄，而組合三會將所有滿足查詢條件的記錄都加鎖。
結(jié)論：若id列上有非唯一索引，那么對應(yīng)的所有滿足SQL查詢條件的記錄，都會被加鎖。同時，這些記錄在主鍵索引上的記錄，也會被加鎖。
4. id非唯一索引+RR
還記得前面提到的MySQL的四種隔離級別的區(qū)別嗎？RC隔離級別允許幻讀，而RR隔離級別，不允許存在幻讀。但是在組合五、組合六中，加鎖行為又是與RC下的加鎖行為完全一致。那么RR隔離級別下，
組合七，Repeatable Read隔離級別，id上有一個非唯一索引，執(zhí)行delete from t1 where id = 10; 假設(shè)選擇id列上的索引進(jìn)行條件過濾，最后的加鎖行為，是怎么樣的呢？同樣看下圖1：
結(jié)論：Repeatable Read隔離級別下，id列上有一個非唯一索引，對應(yīng)SQL：delete from t1 where id = 10; 首先，通過id索引定位到第一條滿足查詢條件的記錄，加記錄上的X鎖，加GAP上的GAP鎖，然后加主鍵聚簇索引上的記錄X鎖，然后返回；然后讀取下一條，重復(fù)進(jìn)行。直至進(jìn)行到第一條不滿足條件的記錄[11,f]，此時，不需要加記錄X鎖，但是仍舊需要加GAP鎖，最后返回結(jié)束。
什么時候會取得gap lock或nextkey lock 這和隔離級別有關(guān),只在REPEATABLE READ或以上的隔離級別下的特定操作才會取得gap lock或nextkey lock。
5.id無索引+RC
相對于前面三個組合，這是一個比較特殊的情況。id列上沒有索引，where id = 10;這個過濾條件，沒法通過索引進(jìn)行過濾，那么只能走全表掃描做過濾。對應(yīng)于這個組合，SQL會加什么鎖？或者是換句話說，全表掃描時，會加什么鎖？這個答案也有很多：有人說會在表上加X鎖；有人說會將聚簇索引上，選擇出來的id = 10;的記錄加上X鎖。那么實際情況呢？請看下圖2：
由于id列上沒有索引，因此只能走聚簇索引，進(jìn)行全部掃描。從圖中可以看到，滿足刪除條件的記錄有兩條，但是，聚簇索引上所有的記錄，都被加上了X鎖。無論記錄是否滿足條件，全部被加上X鎖。既不是加表鎖，也不是在滿足條件的記錄上加行鎖。
有人可能會問？為什么不是只在滿足條件的記錄上加鎖呢？這是由于MySQL的實現(xiàn)決定的。如果一個條件無法通過索引快速過濾，那么存儲引擎層面就會將所有記錄加鎖后返回，然后由MySQL Server層進(jìn)行過濾。因此也就把所有的記錄，都鎖上了。
結(jié)論：若id列上沒有索引，SQL會走聚簇索引的全掃描進(jìn)行過濾，由于過濾是由MySQL Server層面進(jìn)行的。因此每條記錄，無論是否滿足條件，都會被加上X鎖。但是，為了效率考量，MySQL做了優(yōu)化，對于不滿足條件的記錄，會在判斷后放鎖，最終持有的，是滿足條件的記錄上的鎖，但是不滿足條件的記錄上的加鎖/放鎖動作不會省略。同時，優(yōu)化也違背了2PL的約束。
6：id無索引+RR
組合八，Repeatable Read隔離級別下的最后一種情況，id列上沒有索引。此時SQL：delete from t1 where id = 10; 沒有其他的路徑可以選擇，只能進(jìn)行全表掃描。最終的加鎖情況，圖3所示：
結(jié)論：在Repeatable Read隔離級別下，如果進(jìn)行全表掃描的當(dāng)前讀，那么會鎖上表中的所有記錄，同時會鎖上聚簇索引內(nèi)的所有GAP，杜絕所有的并發(fā) 更新/刪除/插入 操作。當(dāng)然，也可以通過觸發(fā)semi-consistent read，來緩解加鎖開銷與并發(fā)影響，但是semi-consistent read本身也會帶來其他問題，不建議使用。
7 id主鍵+RR
上面的四個組合，都是在Read Committed隔離級別下的加鎖行為，接下來的四個組合，是在Repeatable Read隔離級別下的加鎖行為。
組合五，id列是主鍵列，Repeatable Read隔離級別，針對delete from t1 where id = 10; 這條SQL，加鎖與組合一：[id主鍵，Read Committed]一致。
8. 組合六：id唯一索引+RR
與組合五類似，組合六的加鎖，與組合二：[id唯一索引，Read Committed]一致。兩個X鎖，id唯一索引滿足條件的記錄上一個，對應(yīng)的聚簇索引上的記錄一個。
9. 組合九：Serializable
針對前面提到的簡單的SQL，最后一個情況：Serializable隔離級別。對于SQL2：delete from t1 where id = 10; 來說，Serializable隔離級別與Repeatable Read隔離級別完全一致，因此不做介紹。
Serializable隔離級別，影響的是SQL1：select * from t1 where id = 10; 這條SQL，在RC，RR隔離級別下，都是快照讀，不加鎖。但是在Serializable隔離級別，SQL1會加讀鎖，也就是說快照讀不復(fù)存在，MVCC并發(fā)控制降級為Lock-Based CC。
結(jié)論：在MySQL/InnoDB中，所謂的讀不加鎖，并不適用于所有的情況，而是隔離級別相關(guān)的。Serializable隔離級別，讀不加鎖就不再成立，所有的讀操作，都是當(dāng)前讀。
五、死鎖案例
1. 不同表相同記錄行鎖沖突
這種情況很好理解，事務(wù)A和事務(wù)B操作兩張表，但出現(xiàn)循環(huán)等待鎖情況。
2. 相同表記錄行鎖沖突
這種情況比較常見，之前遇到兩個job在執(zhí)行數(shù)據(jù)批量更新時，jobA處理的的id列表為[1,2,3,4]，而job處理的id列表為[8,9,10,4,2]，這樣就造成了死鎖。
3. 不同索引鎖沖突
這種情況比較隱晦，事務(wù)A在執(zhí)行時，除了在二級索引加鎖外，還會在聚簇索引上加鎖，在聚簇索引上加鎖的順序是[1,4,2,3,5]，而事務(wù)B執(zhí)行時，只在聚簇索引上加鎖，加鎖順序是[1,2,3,4,5]，這樣就造成了死鎖的可能性。
4. gap鎖沖突
innodb在RR級別下，如下的情況也會產(chǎn)生死鎖，比較隱晦。不清楚的同學(xué)可以自行根據(jù)上節(jié)的gap鎖原理分析下。
結(jié)論：在MySQL/InnoDB中，所謂的讀不加鎖，并不適用于所有的情況，而是隔離級別相關(guān)的。Serializable隔離級別，讀不加鎖就不再成立，所有的讀操作，都是當(dāng)前讀。
一些優(yōu)化鎖性能的建議盡量使用較低的隔離級別；
精心設(shè)計索引， 并盡量使用索引訪問數(shù)據(jù)， 使加鎖更精確， 從而減少鎖沖突的機(jī)會
選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也更小
給記錄集顯示加鎖時，最好一次性請求足夠級別的鎖。比如要修改數(shù)據(jù)的話，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產(chǎn)生死鎖
不同的程序訪問一組表時，應(yīng)盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機(jī)會
盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對并發(fā)插入的影響
不要申請超過實際需要的鎖級別
除非必須，查詢時不要顯示加鎖。 MySQL的MVCC可以實現(xiàn)事務(wù)中的查詢不用加鎖，優(yōu)化事務(wù)性能；MVCC只在COMMITTED READ(讀提交)和REPEATABLE READ(可重復(fù)讀)兩種隔離級別下工作
對于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能
相關(guān)鏈接：
詳解mysql的各種鎖(表鎖、行鎖、共享鎖、意向共享鎖、記錄鎖、間隙鎖、臨鍵鎖) https://zhuanlan.zhihu.com/p/52312376
總結(jié)
以上是生活随笔為你收集整理的mysql锁机制为何设计如此复杂_再谈mysql锁机制及原理—锁的诠释的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql多个子查询_mysql（5）多
- 下一篇： mysql防注入原理_MyBatis如何