MySQL筆記（9）– 各種鎖及實現
- 2020 年 4 月 1 日
- 筆記
一、背景
MySQL有兩種類型的鎖：lock(鎖)和latch(閂鎖)：
| 類型 | lock | latch |
| 對象 |
事務 |
執行緒 |
| 保護 | 資料庫內容 | 記憶體數據結構 |
| 持續時間 | 整個事務 | 臨界資源 |
| 模式 | 行鎖、表鎖、意向鎖 | 讀寫鎖、互斥量 |
| 死鎖 | 通過等待圖和超時機制進行死鎖檢測和處理（deadlock detection through waits-for graph, timeout machanism） | 無死鎖檢測和處理機制，僅通過應用程式加鎖的順序保證無死鎖的情況發生 |
| 存在於 | 鎖定管理器的哈希表（Lock Manager’s Hash Table） | 每個數據結構的對象中 |
今天要來聊聊MySQL的lock(鎖)。資料庫鎖設計的初衷是處理並發問題，作為多用戶共享的資源，當出現並發訪問時，資料庫需要合理地控制資源的訪問規則。而鎖就是用來實現這些訪問規則的重要數據結構。
根據加鎖的範圍，MySQL裡面的鎖大致可以分為全局鎖、表級鎖和行鎖三類，還會額外討論MySQL其他的鎖，比如排他鎖、共享鎖、樂觀鎖、悲觀鎖、間隙鎖、死鎖和解決死鎖的機制等。
二、MySQL的鎖類型
1.全局鎖（global lock）
顧名思義，全局鎖就是對整個資料庫實例加鎖。MySQL提供了一個加全局讀鎖的方法，命令是Flush table with read lock(FTWRL)。當你需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他執行緒的以下語句會被阻塞：數據更新語句（數據的增刪改）、數據定義語句（包括建表、修改表結構等）和更新類事務的提交語句。
全局鎖的典型使用場景：做全庫邏輯備份。也就是把整庫每個表都select出來存成文本。
以前有一種做法，是通過FTWRL確保不會有其他執行緒對資料庫做更新，然後對整個庫做備份。注意，在備份過程中整個庫完成處於只讀狀態。
但是讓整庫都只讀，聽上去就很危險：
- 如果你在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺；
- 如果你在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的binlog，會導致主從延遲；
看來加全局鎖不太好。但是細想一下，備份為什麼要加鎖呢？我們來看一下不加鎖會有什麼問題。
假設你現在要維護一個培訓網站的購買系統，關注的是用戶賬戶餘額表和用戶課程表。
現在發起一個邏輯備份。假設備份期間，有一個用戶，他購買了一門課程，業務邏輯里就要扣除他的餘額，然後往已購課程裡面加上一門課。
如果時間順序上是先備份帳號餘額表(u_account)，然後用戶購買，然後備份用戶課程表(u_course)，會怎樣呢？
可以看到，這個備份結果是，用戶A的數據狀態是“帳號餘額沒扣，但是用戶課程表裡面已經多了一門課”，如果後面用這個來備份來恢複數據的話，用戶A就發現，自己賺了。
作為用戶可別覺得這樣很好，你可以試想一下：如果備份表的順序反過來，先備份用戶課程表再備份帳號餘額表，又可能出現什麼結果呢？
也就是說，不加鎖的話，備份系統備份的得到的庫不是一個邏輯時間點，這個視圖是邏輯不一致的。
說到視圖，在前面講事務和實現的時候，其實是有一個方法能夠拿到一致性視圖的，就是在可重複讀隔離級別下開啟一個事務。
官方自帶的邏輯備份工具是mysqldump。當mysqldump使用參數-single-transaction的時候，導數據之前就會啟動一個事務，來確保拿到一致性視圖。而由於MVCC的支援，這個過程中數據是可以正常更新的。
你一定在疑惑，有了這個功能，為什麼還需要FTWRL呢？一致性讀是好，但前提是引擎要支援這個隔離級別。比如，對於MyISAM這種不支援事務的引擎，如果備份過程中有更新，總是只能取到最新的數據，那麼就破壞了備份的一致性。這時，我們就需要使用FTWRL命令。
所以，single-transaction方法只適用於所有的表使用事務引擎的庫。如果有的表使用了不支援事務的引擎，那麼備份就只能通過FTWRL方法。
你也許會問，既然要全庫只讀，為什麼不使用set global readonly=true的方式呢？確實readonly方式也可以讓全庫進入只讀狀態，但還是建議用FTWRL方式，主要有兩個原因：
- 在有些系統中，readonly的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改global變數的方式影響面更大；
- 在異常處理機制上有差異。如果執行FTWRL命令之後由於客戶端發生異常斷開，那麼MySQL會自動釋放這個全局鎖，整個庫回到可以正常更新的狀態。而將整個庫設置為readonly之後，如果客戶端發生異常，則資料庫就會一直保持readonly狀態，這樣會導致整個庫長時間處於不可寫狀態，風險較高。
業務的更新不只是增刪改數據（DML），還有可能是加欄位等修改表結構的操作（DDL）。不論是哪種方法，一個庫被全局鎖上以後，你要對裡面任何一個表做欄位操作，都是會被鎖住的。
但是，即使沒有被全局鎖住，加欄位也不是就能一帆風順的，因為你還會碰到下面的表級鎖。
2.表級鎖（table lock）
MySQL裡面的表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lock[MDL]， 作用是防止DDL和DML並發的衝突）。
表鎖的語法是lock tables…read/write。與FTWRL類型，可以用unlock tables主動釋放鎖，也可以在客戶端斷開時自動釋放。需要注意的是，lock tables語法除了會限制別的執行緒的讀寫外，也限定了本執行緒接下來的操作對象。
舉個例子，如果在某個執行緒A中執行lock tables t1 read,t2 write;這個語句，則其他執行緒寫t1、讀寫t2的語句都會被阻塞。同時，執行緒A在執行unlock tables之前，也只能執行讀t1、讀寫t2的操作。連寫t1都不允許，自然也不能訪問其他表。
在還沒有出現更細粒度的鎖的時候，表鎖是最常用的處理並發的方式。而對於InnoDB這種支援行鎖的引擎，一般不使用lock tables命令來控制並發，畢竟鎖住整個表的影響面還是太大。
另一類表級的鎖是MDL（metadata lock）。MDL不需要顯式使用，在訪問一個表的時候會被自動加上。MDL的作用是，保證讀寫的正確性。你可以想像一下，如果一個查詢正在遍歷一個表中的數據，而執行期間另一個執行緒對這個表結構做變更，刪了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。
因此，在MySQL5.5版本中引入了MDL，當對一個表做增刪查改操作時，加MDL讀鎖；當要對錶做結構變更操作時，加MDL寫鎖。
- 讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪查改；
- 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行；
雖然MDL鎖是系統默認會加的，但卻是你不能忽略的一個機制。比如下面這個例子，給一個小表加個欄位，導致整個庫掛了。
給一個表加欄位、或修改欄位、或加索引，需要掃描全表的數據。在對大表操作的時候，你肯定會特別小心。而實際上，即使是小表，操作不慎也會出問題。我們來看一下下面的操作序列，假設t是一個小表。
我們可以看到sessionA先啟動，這時候會對錶t加一個MDL讀鎖。由於sessionB需要的也是MDL讀鎖，因此可以正常執行；之後sessionC會被blocked，是因為sessionA的MDL讀鎖還沒有釋放，而sessionC需要MDL寫鎖，因此只能被阻塞。
如果只有sessionC自己被阻塞還沒什麼關係，但是之後所有要在表t上新申請MDL讀鎖的請求也會被sessionC阻塞。前面說了，所有對錶的增刪查改操作都需要先申請MDL讀鎖，就都被鎖住，等於這個表現在完全不可讀寫了。
如果某個表上的查詢語句頻繁，而且客戶端有重試機制，也就是說超時後會再起一個新session再請求的話，這個庫的執行緒很快就會爆滿。
你現在理解了，事務中的MDL鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放。
基於上面的分析，那麼如何安全地給小表加欄位呢？
首先我們要解決長事務，事務不提交，就會一直占著MDL鎖。在MySQL的information_schema的innodb_trx表中，你可以查看當前執行中的事務。如果你要做DDL變更的表剛好有長事務在執行，要考慮先暫停DDL，或者kill掉這個長事務。
但考慮一下這個場景。如果你要變更的表是一個熱點表，雖然數據量不大，但是上面的請求很頻繁，而你不得不加個欄位，你該怎麼做呢？
這個時候kill可能未必管用，因為新的請求馬上就來了。比較理想的機制是，在alter table語法裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到MDL寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄，之後再通過重試命令重複這個過程。
MySQL5.6支援online ddl，也就是對錶操作增加欄位等功能不會進行阻塞讀寫，Online DDL的過程是：
- Write locks with MDL（拿MDL寫鎖）
- Degraded to MDL Read Lock（降級成MDL讀鎖）
- Really do DDL（真正做DDL）
- Upgrade to MDL Write Lock（升級成MDL寫鎖）
- Release MDL locks（釋放MDL鎖）
1, 2, 4, 5 have very short execution time if there is no lock conflict. Step 3 takes up most of the DDL time, during which the table can read and write data normally.
1、2、4、5步驟如果沒用鎖衝突和執行時間非常短，第三步佔用了DDL大部分時間，這個期間這個表是可以正常讀寫數據的。上面的例子是在第一步就堵住了，所以導致阻塞。
3.行鎖（row lock）
MySQL的行鎖是在引擎層由各個引擎自己實現的。但並不是所有的引擎都支援行鎖，比如MyISAM引擎就不支援行鎖。不支援行鎖意味著並發控制只能使用表鎖，對於這種引擎的表，同一張表任何時刻只能有一個更新在執行，這就會影響到業務並發度。InnoDB是支援行鎖的【InnoDB使用自動行級鎖。即使在只插入或刪除一行的事務中，也可能出現死鎖。這是因為這些操作並不是真正的“原子”;它們自動對(可能是多個)插入或刪除行的索引記錄設置鎖。】，這也是MyISAM被InnoDB替代的重要原因之一。
顯示執行行鎖的兩種方式：
- FOR UPDATE【使用FOR UPDATE子句持有的任何鎖都不允許其他事務讀取(使用FOR UPDATE子句)、更新或刪除行，直到事務被提交或回滾，釋放鎖為止。這基本上是一個排他/寫鎖。這基本上是一個排他/寫鎖。】
- LOCK IN SHARE MODE【使用lock IN SHARE MODE子句持有的任何鎖將允許其他事務讀取鎖定的行，但在事務提交或回滾並釋放鎖之前，不允許其他事務在該行上寫操作。這基本上是一個共享/讀鎖。】
顧名思義，行鎖就是針對數據表中行記錄的鎖。這很好理解，比如事務A更新了一行，而這時侯事務B也要更新同一行，則必須等事務A的操作完成後才能進行更新。
當然，資料庫中還有一些沒那麼一目了然的概念和設計，這些概念如果理解和使用不當，容易導致程式出現非預期行為，比如兩階段鎖。
從兩階段鎖說起
在下面的操作序列中，事務B的update語句執行時會是什麼現象呢？假設欄位id是表t的關鍵。
這個問題的結論取決於事務A在執行完兩天update語句後，持有哪些鎖，以及在什麼時候釋放。你可以驗證一下：實際上事務B的update語句會被阻塞，直到事務A執行commit之後，事務B才能繼續執行。
知道了這個答案，你一定知道了事務A持有的兩個記錄的行鎖，都是在commit的時候才釋放的。
也就是說，在InnoDB事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。
知道了這個設定，對我們使用事務有什麼幫助呢？那就是，如果你的事務中需要鎖多個行，要把最可能造成鎖衝突、最可能影響並發度的鎖盡量往後放。
假設你負責實現一個電影票在線交易業務，顧客A要到影院B購買電影票。我們簡化一點，這個業務需要涉及到一下操作：
- 從顧客A帳號餘額中扣除電影票價；
- 給影院B的帳號餘額增加這張電影票價；
- 記錄一條交易日誌；
也就是說，要完成這個交易，我們需要update兩天記錄，並insert一條記錄。當然，為了保證交易的原子性，我們要把這三個操作放在一個事務中。那麼，你會怎樣安排這三個語句在事務中的順序呢？
試想如果同時有另外一個顧客C要在影院B買票，那麼這兩個事務衝突的部分就是語句2了，因為它們要更新同一個影院帳號的餘額，需要修改同一行數據。
根據兩階段鎖協議，不論你怎樣安排語句順序，所有的操作需要行鎖都是在事務提交的時候才釋放的。所以，如果你把語句2安排在最後，比如按照3、1、2這樣的順序，那麼影院帳號餘額這一行的鎖時間就最少。這就最大程度地減少了事務之間的鎖等待，提升了並發度。
好了，由於你正確的設計，影院餘額這一行的行鎖在一個事務中不會停留很長時間。但是，這並沒有完全解決你的困擾。
如果這個影院做活動，可以低價預售一年內所有的電影票，而且這個活動只做一天。於是在活動時間開始的時候，你的MySQL就掛了。你登上伺服器一看，CPU消耗接近100%，但整個資料庫每秒就執行不到100個事務。這是什麼原因呢？
這裡就是下面說的死鎖了。
4.死鎖（deadlock）
當並發系統中不同執行緒出現循環資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都處於無限等待的狀態，稱為死鎖。這裡用資料庫中的行鎖來舉個例子。
這時候，事務A在等待事務B釋放id=2的行鎖，而事務B在等待事務A釋放id=1的行鎖。事務A和事務B在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以後，有兩種策略：
- 一種是直接進入等待，直到超時。當使用了Lock Tables語句或除了InnoDB其他引擎設置的表鎖，就要使用超時時間設置來解決死鎖。這個超時時間可以通過參數innodb_lock_wait_timeout來設置；
- 另一種是，發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行，即InnoDB會自動檢測事務死鎖並回滾一個或多個事務以打破死鎖。InnoDB嘗試選擇要回滾的小事務，其中事務的大小由插入、更新或刪除的行數決定。將參數Innodb_deadlock_detect設置為0，表示開啟這個邏輯。
對於死鎖檢測，官方文檔顯示“If the LATEST DETECTED DEADLOCK section of InnoDB Monitor output includes a message stating, ‘TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION,’ this indicates that the number of transactions on the wait-for list has reached a limit of 200. A wait-for list that exceeds 200 transactions is treated as a deadlock and the transaction attempting to check the wait-for list is rolled back. The same error may also occur if the locking thread must look at more than 1,000,000 locks owned by transactions on the wait-for list.”
InnoDB使用等待圖來發現死鎖，而當wait-for列表中的事務量達到LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK設置的量（默認200）時也會認為發生了“死鎖”。此時可在SHOW ENGINE INNODB STATUS的輸出中看到“TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION”這麼一行內容。還有，當wait-for列表中的事務擁有的鎖超過LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK(默認1000000)時也會認為發生了“死鎖”。
在InnoDB中，innodb_lock_wait_timeout的默認值是50s，意味著如果採用第一個策略，當出現死鎖時，第一個被鎖住的執行緒要過50s才會超時退出，然後其他執行緒才有可能繼續執行。對於在線服務來說，這個等待時間往往是無法接受的。
但是，我們又不可能直接把這個時間設置為一個很小的值，比如1s。這樣當出現死鎖時確實很快就可以解開，但如果不是死鎖，而是簡單的鎖等待呢？所以，超時時間設置太短的話，會出現很多誤傷。
所以，正常情況下還是採用第二種策略：主動死鎖檢測，而且Innodb_deadlock_detect的默認值本身就是on。主動死鎖檢測在發生死鎖的時候，是能夠快速發現並進行處理的，但是它也是有額外負擔的。
你可以想像一下這個過程：每當一個事務被鎖的時候，就要看看它所依賴的執行緒有沒有被別人鎖住，如此循環，最後判斷是否出現了循環等待，也就是死鎖。
那如果是我們上面說到的所有事務都是要更新同一行的場景呢？
每個新來的被堵住的執行緒，都要判斷會不會由於自己的加入導致了死鎖，這是一個時間複雜度為O(n)的操作。假設有1000個並發執行緒要同時更新同一行，那麼死鎖檢測操作就是100萬這個量級的。雖然最終檢測的結果是沒有死鎖，但是這期間要消耗大量的CPU資源。因此，你就會看到CPU利用率很高，但是每秒卻執行不了幾個事務。
根據上面的分析，我們來討論一下，怎麼解決由這些熱點行更新導致的性能問題呢？【MySQL8.0.1使用了“SKIP LOCKED”和“NOWAIT”來處理熱點行】問題的癥結在於，死鎖檢測要耗費大量的CPU資源。
一種頭痛醫頭的方法，就是如果你能確保這個業務一定不會出現死鎖，可以臨時把死鎖檢測關掉。但是這種操作本身帶有一定的風險，因為業務設計的時候一般不會把死鎖當做一個嚴重錯誤，畢竟出現死鎖了，就回滾，然後通過業務重試一般就沒問題了，這是業務無損的。而關掉死鎖檢測意味著可能會出現大量的超時，這是業務有損的。
另一個思路是控制並發度。根據上面的分析，你會發現如果並發能夠控制住，比如同一行同時最多只有10個執行緒在更新，那麼死鎖檢測的成本就很低，就不會出現這個問題。一個直接的想法就是，在客戶端做並發控制。但是，你會很快發現這個方法不太可行，因為客戶端很多，比如有一個應用，有600個客戶端，這樣即使每個客戶端控制到只有5個並發執行緒，匯總到資料庫服務端以後，峰值並發數也可能要達到3000。
因此，這個並發控制要做在資料庫服務端。如果有中間件，可以考慮在中間件實現；如果有能修改MySQL源碼的人，可以做在MySQL裡面。基本思路就是，對於相同行的更新，在進入引擎之前排隊。這樣在InnoDB內部就不會有大量的死鎖檢測工作了。
如果上面的方法你無法實現，你可以考慮通過將一行改成邏輯上的多行來減少鎖衝突。還是以影院帳號為例，可以考慮放在多條記錄上，比如10個記錄，影院帳號總額等於這10個記錄的值的總和。這樣每次要給影院帳號加金額的時候，隨機選其中一條記錄來加。這樣每次衝突概率變成原來的1/10，可以減少鎖等待個數，也就減少了死鎖檢測的CPU消耗。
這個方案看上去是無損的，但其實這類方案需要根據業務邏輯做詳細設計。如果帳號餘額可能會減少，比如退票邏輯，那麼這時候就需要考慮當一部分行記錄變成0的時候，程式碼要有特殊處理。
如果死鎖的情況太多，可以使用innodb_print_all_deadlocks來列印所有的死鎖資訊，所有的資訊會保存到error log日誌中。
如果使用了select..for update或者select … lock in share mode這樣的語句來鎖定讀取，那麼可以嘗試使用更低的隔離級別比如提交讀(Read Committed)來減少發生死鎖的概率。
5.排他鎖（exclusive[X] lock）
排他鎖也叫寫鎖，允許持有鎖的事務更新或刪除行。即當進行修改操作時會獲得寫鎖，在一定的時間範圍內，只能存在一個寫鎖。如果在資源上沒有讀/寫鎖，事務可以立即獲得寫鎖。當寫鎖沒有被釋放時，其他的所有鎖請求都只能等待。
值得注意的是，寫鎖的優先順序高於讀鎖。當一個資源上沒有鎖時，或者所有的鎖請求都在等待隊列中，下面是鎖的授予方式：
- 首先將鎖授予寫鎖隊列中等待的請求；
- 如果寫鎖隊列中沒有對資源的鎖請求，那麼將鎖授予讀鎖隊列中的第一個請求。
6.共享鎖（shared[S] lock）
共享鎖也叫讀鎖，允許持有鎖的事務讀取一行。即不能進行寫操作來提供一致性讀取。如果資源上沒有寫鎖，事務可以立即獲得讀鎖，多個事務可以在同時獲得讀鎖，如果讀鎖沒有釋放，寫鎖不能被獲取，寫事務只能放入等待隊列。
7.樂觀鎖（optimistic lock）
關係資料庫中對性能和並發性的需求意味著（關係資料庫）的啟動和調度操作都是快速的。一致性和完整性的需求意味著任何操作都可能失敗：事務可能被回滾，一個DML操作可能違反約束，對鎖的請求可能導致死鎖，一個網路錯誤可能導致超時。樂觀策略是一種假設大多數請求或嘗試都會成功，因此為失敗案例做的準備相對比較少。當這個假設成立時，資料庫幾乎不需要做任何工作。當請求確實失敗時，就必須做更多的工作來清理和撤銷更改。即樂觀鎖允許數據的衝突，但會在數據提交時進行檢測。
許多內置的資料庫機制都使用樂觀策略。
InnoDB對鎖和提交等操作使用樂觀策略，例如，一個做數據更改的事務可以在提交之前寫入數據文件，這使得提交本身非常快，但是如果事務回滾，則需要更多的工作來撤銷更改。
樂觀鎖也叫行版本控制，它不會鎖定任何事務；MySQL內部會管理不同的行版本號，所以當進行讀操作即讀取其中一個版本號的數據時，在同時有一個寫操作對這個數據進行更新，它會對這個數據創建一個新的版本號，再下一次讀取時，會讀取最新版本號的數據，而對老版本號的數據進行標記為死亡元組（dead tuple）；
在上面的關係圖中，Alice的寫操作檢測到了版本更新並發生了衝突，導致操作失敗。【每次執行更新或刪除操作時，version都會遞增，更新和刪除語句的WHERE子句也會使用它。我們需要在執行更新或刪除之前發起SELECT查詢並讀取當前版本，否則，我們不知道將哪個版本值傳遞給WHERE子句或增加哪個版本值。】
-
MVCC體系結構依賴於樂觀鎖定的概念；PostgreSQL和MySQL InnoDB等RDBMS完全基於MVCC；Microsoft SQL Server也具有快照隔離，這也是一種樂觀鎖。
- 不會對讀寫加鎖；
- 使用樂觀鎖訪問數據時，有70％的機會獲得最後提交的數據版本，但它提供了快速訪問許可權，因為它從未在事務之間創建依賴關係。
- 通常，大多數Web和移動應用程式都適合最後提交的數據版本。
8.悲觀鎖（pessimistic lock）
InnoDB使用了悲觀鎖策略濕產生死鎖的機會最小化。悲觀策略是一種為了安全而犧牲性能或並發性的方法。如果大部分請求或嘗試可能失敗，或者失敗的請求的後果非常嚴重，則適合使用悲觀策略。在應用級別你可以使用悲觀策略來避免死鎖，即在開始時獲取事務所需的所有鎖。
悲觀鎖是在進行編輯一條記錄時維護一個獨佔鎖，對於最終用戶來說，在這個鎖沒有被釋放前，它（記錄）不能被其他人進行編輯。
在上面的關係圖中，Alice和Bob都對記錄id為1的數據獲取了一個讀鎖，因為他們都獲取了讀鎖，所以他們不能對記錄進行寫操作，只有等Alice釋放了讀鎖，Bod才能進行寫操作。
- 排他鎖和共享鎖都屬於悲觀鎖；
- 隔離級別像Read Committed(RC提交讀）、Repeatable Read(RR重複讀）和Serializable(串列化)也是使用了悲觀鎖；
- 每當出現問題時，它都會鎖定事務，並將事務放入阻塞隊列。悲觀鎖使你可以訪問活動的和提交的數據，沒有機會訪問臟數據；
- 像銀行或金融系統這樣大型的OLTP（On-Line Transaction Processing聯機事務處理過程）系統因為對讀寫數據的準確性的要求，他們比較喜歡使用悲觀鎖；對於大系統來說，鎖是一種額外的開銷;
9.間隙鎖（gap lock）
顧名思義就是在索引記錄之間的間隙的鎖，或者是在第一個索引記錄之前和最好一個索引記錄之後的間隙的鎖。比如下面的例子：
select c1 from t where c1 between 10 and 20 for update;
當執行這條語句時，MySQL會對t表中c1值從10到20之間的行數加上間隙鎖，阻止其他事務對t.c1的列值在10到20之間進行插入，比如t.c1插入一條新的記錄值為15，不管列中是否已經有15這樣的值，插入是不成功的，因為在10到20之間的間隙都是鎖定的。
比如你對t.c1中大於10的值都進行更新操作，那麼你無法對t.c1的列中插入大於10的值，這也是間隙鎖的體現。
10.記錄鎖（record lock）
一種索引記錄上的鎖。比如下面的語句：
select c1 from t where c1=10 for update;
當執行這條語句時，會阻止其他事務對t.c1為10的這一行數據進行增刪改操作。
三、討論
1.備份一般都會在備庫上執行，你在用–single-transaction 方法做邏輯備份的過程中，如果主庫上的一個小表做了一個 DDL，比如給一個表上加了一列。這時候，從備庫上會看到什麼現象呢？
答案：假設這個DDL是針對錶t1的，下面是備份過程中 幾個關鍵的語句：
Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Q2:START TRANSACTION WITH CONSISTENT SNAPSHOT； /* other tables */ Q3:SAVEPOINT sp; /* 時刻 1 */ Q4:show create table `t1`; /* 時刻 2 */ Q5:SELECT * FROM `t1`; /* 時刻 3 */ Q6:ROLLBACK TO SAVEPOINT sp; /* 時刻 4 */ /* other tables */
在備份開始的時候，為了確保RR(可重複讀）隔離級別，再設置一次RR隔離級別（Q1）；
啟動事務，這裡用WITH CONSISTENT SNAPSHOT確保這個語句執行完就可以得到一個一致性視圖（Q2）；
設置一個保存點，這個很重要（Q3）；
show create是為了拿到表結構（Q4），然後正式導數據（Q5），回滾到SAVEPOINT sp，在這裡的作用是釋放t1的MDL鎖（Q6）。
DDL從主庫傳過來的時候按照效果不同，設置了4個時刻。題目設定為小表，假定到達後，如果開始執行，則很快能夠執行完成。
- 如果在Q4語句執行之前到達，現象：沒有影響，備份拿到的是DDL後的表結構；
- 如果在”時刻2“到達，則表結構被改過，Q5執行的時候，報Table definition has changed,please retry transaction，現象：mysqldump終止；
- 如果在“時刻2”和“時刻3”之間到達，mysqldump占著t1的MDL讀鎖，binlog被阻塞，現象：主從延遲，直到Q6執行完成；
- 從“時刻4”開始，mysqldump釋放了MDL讀鎖，現象：沒有影響，備份拿到的是DDL前的表結構。
2.如果你要刪除一個表裡面的前 10000 行數據，有以下三種方法可以做到：
- 第一種，直接執行 delete from T limit 10000;
- 第二種，在一個連接中循環執行 20 次 delete from T limit 500;
- 第三種，在 20 個連接中同時執行 delete from T limit 500。
答案：第二種方式，即：在一個連接中循環執行 20 次 delete from T limit 500。
第一種方式（即：直接執行 delete from T limit 10000）裡面，單個語句佔用時間長，鎖的時間也比較長；而且大事務還會導致主從延遲。
第三種方式（即：在 20 個連接中同時執行 delete from T limit 500），會人為造成鎖衝突。
參考：
- MySQL概念：https://oxnz.github.io/2014/02/27/mysql-primer-concepts/
- 全局鎖、表級鎖：https://developpaper.com/deep-understanding-of-mysql-global-lock-and-table-lock/
- MySQL8.0.1s使用“SKIP LOCKED”和“NOWAIT”處理熱點行:
- https://mysqlserverteam.com/mysql-8-0-1-using-skip-locked-and-nowait-to-handle-hot-rows/
- https://www.percona.com/blog/2018/06/29/mysql8-hot-rows-with-nowait-skip-locked/
- https://yq.aliyun.com/articles/159602
- 如何最小化和死鎖：https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html
- 死鎖檢測和回滾：https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlock-detection.html
- 避免死鎖的終極策略：https://www.dbrnd.com/2016/04/database-design-the-ultimate-strategies-to-avoid-deadlock/
- 解釋InnoDB顯式鎖定機制：https://blog.toadworld.com/2018/01/11/explaining-innodb-explicit-locking-mechanisms
- 樂觀鎖和悲觀鎖：
- https://www.dbrnd.com/2016/04/database-theory-what-is-optimistic-locking-and-pessimistic-locking/
- https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_optimistic
- https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_pessimistic
- https://enterprisecraftsmanship.com/posts/optimistic-locking-automatic-retry/
- https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking
- 間隙鎖：https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_gap_lock
- 記錄鎖：https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_record_lock
